VBA를 활용하여 [인사, 근태] 근무계획 자동화 하기

728x90

반응형

얼마전 어느 한 업체의 서버 점검을 위하여 방문한 이 후 해당 업체 주업무는 인사 업무지만 전산을 보조적 업무로 담당하시는 분에게 의뢰가 들어왔다.

 

자체 구축한 사내 ERP에 근무계획을 현장에서 간소화하게 타이핑 하여 주는데 이것을 코드로 일일이 보고 치고 있어 환장할 노릇...이라며 한탄하시 길래 하루정도의 시간을 벌고 엑셀 VBA를 활용하여 쉽게 사용 하실 수 있도록 방향을 잡고 (대충) 만들었다.

 

먼저 엑셀의 구조를 받아 똑같이 만든 후 파일이 자동화 VBA가 무단 사용을 방지 하기위해 아래 코드를 작성

 

먼저 엑셀 시트를 활용 하기에 먼저 사용자의 컴퓨터에 있는 특정 경로에 저장된 "license Key.txt" 파일에서 이진수 형식(암호화된)으로 저장된 라이선스 키를 읽어들인 후, 작동하도록 구성하였다.

 

작성하는 코드에는

파일이 존재하지 않거나 잘못된 이진수 문자열이 있을 경우, 오류 메시지를 출력하고 종료

잘못된 이진수 형식이 들어오면 "잘못된 이진수 문자열" 메시지를 출력하고 함수 실행을 중단

하는 작업이 필요하다.

 

folderPath = Environ("USERPROFILE") & "\Desktop\SpecialEngineer VBA\"
filePath = folderPath & "license Key.txt"

활용 코드는 위와 같고 해석해보면 바탕화면 SpecialEngineer VBA 폴더안에 license Key.txt 파일을 넣어 작동 되도록 코드를 작성하였고 구문을 쪼개어보면

Environ("USERPROFILE") 현재 사용자의 프로필 경로(예: "C:\Users\사용자") 바라보게 하고

folderPath 사용자의 바탕화면에 있는 SpecialEngineer VBA 폴더 경로를 지정

filePath 해당 폴더 안에 있는 license Key.txt 파일의 전체 경로를 포함하게 한다.

이제 파일이 폴더안에 존재하는지의 여부를 판단해야하는데

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(filePath) Then
    Set file = fso.OpenTextFile(filePath, 1)
    binaryPassword = file.ReadLine
    file.Close
Else
    MsgBox "라이선스 키 파일이 존재하지 않습니다. 경로를 확인하세요.", vbCritical
    End
End If

의 구문으로 판단 존재하지 않을경우 메세지가 표시되도록 한다.

마찬가지로 쪼개어 보면

 

fso.FileExists(filePath): 지정된 경로에 파일이 존재하는지 확인

파일이 존재하면, OpenTextFile로 파일을 열고 첫 번째 줄을 읽어 binaryPassword 변수에 저장

파일이 없다면, 경고 메시지( 라이선스 키 파일이 존재하지 않습니다. 경로를 확인하세요. )를 띄우고 프로그램을 종료합니다.

 

비교적 간단하게 쪼개어 볼 수 있다.

 

이제 "license Key.txt" 의 내용이 이진수로 변환 암호화 되었기에 인식 할 수 있도록 설정해주어야 하는데

binaryPassword = Replace(binaryPassword, " ", "")

의 구문을 이용 이진수 문자열에서 공백을 제거한다.

Replace 함수는 이진수 문자열에 포함된 공백을 제거

 

이제 이진수의 문자열을 문자로 변환 하여 인식시키는 과정이 필요하다.

decodedPassword = ""
decValue = 0
For i = 1 To Len(binaryPassword)
    If Mid(binaryPassword, i, 1) = "1" Then
        decValue = decValue * 2 + 1
    ElseIf Mid(binaryPassword, i, 1) = "0" Then
        decValue = decValue * 2
    Else
        MsgBox "잘못된 이진수 문자열: " & Mid(binaryPassword, i, 1), vbCritical ' 오류 메시지 출력
        Exit Function
    End If

    If i Mod 8 = 0 Then
        decodedPassword = decodedPassword & Chr(decValue)
        decValue = 0
    End If
Next i

 

이진수를 하나씩 처리하여 10진수로 변환하고, 8비트마다 문자로 변환

decValue 변수는 10진수 값을 저장하고, Chr(decValue)로 이를 문자로 변환

변환 후 **decodedPassword**에 각 문자를 추가

 

GetPasswordFromFile = decodedPassword

이제 최종적으로 디코딩된 문자열( decodedPassword )을 반환하도록 하자.

 

이제 사원코드와 사원명의 데이터를 기준으로 날짜와 요일에 따라 데이터가 입력되어 있는(백데이터 ?) 데이터의 예시를 유선상으로 아래와 같이 받았다.

현장에서 주로 주간 근무는 주, 야간 근무는 야, 휴일주간은 휴주, 휴일야간은 휴야로 표기하여 인사 담당자에게 데이터를 공유하여 주기에 사진과 같이 데이터를 작성 후 코드를 입력을 시작하여 참조하게 만들었다.

 

correctPassword = GetPasswordFromFile() ' 파일에서 암호를 읽어옴
If correctPassword <> "                         " Then
    MsgBox "암호가 틀렸습니다. 프로그램을 종료합니다.", vbCritical
    Exit Sub
End If

 

라이선스 키를 확인하여 불러오고 불러온 데이터가 암호화 다를시에는 VBA를 종료 하게 하였다.

 

MsgBox "해당 프로그램 제작자는 Special Engineer 입니다.", vbInformation

프로그램 시작을 알리는 문구 표시

 

fileMonth = InputBox("백데이터 파일의 월을 입력하세요. (예: 9월)", "월 입력")
If fileMonth = "" Then
    MsgBox "월을 입력하지 않았습니다. 프로그램을 종료합니다.", vbCritical
    Exit Sub
End If

사정상 현장에서 데이터를 공급(?) 받을 때 뒤에 0월을 붙인다는 특정조건을 추가.

매번 코드를 수정 할 수 없으니 파일 이름(예: 백데이터 파일 [9월])의 뒤에 [ 0월 ] 을 추가

혹시 모르니 예시를 띄워. 입력이 쉽도록 만들었다.

 

파일 경로를 설정하고 백데이터 파일 열기

downloadFolder = Environ("USERPROFILE") & "\Downloads\"
backendFileName = "사원별_근무계획(백데이터)" & fileMonth & ".xlsx"
backendFilePath = downloadFolder & backendFileName
If Dir(backendFilePath) = "" Then
    MsgBox "백데이터 파일을 찾을 수 없습니다: " & backendFilePath, vbExclamation
    Exit Sub
End If

downloadFolder = 보통 메일을 이용하여 데이터가 이동하는 것을 확인 했기에 다운로드 폴더를 디폴트로 지정

backendFileName = 불러올 파일이름을 지정

backendFilePath = downloadFolder & backendFileName = 폴더와 이름을 확인

파일을 찾을수 없다 면  MsgBox "백데이터 파일을 찾을 수 없습니다: " & backendFilePath, vbExclamation 문구를 이용하여 오류 메세지 반환

 

서로 다른 엑셀 파일을 활요하기에 백데이터를 여는 구문을 추가

Set wbBackend = Workbooks.Open(backendFilePath)
Set wsBackend = wbBackend.Sheets(1) ' 백데이터 시트

 

자동화 파일을 만들고 현재 파일을 참조하도록 설정

Set wbAutomation = ThisWorkbook
Set wsAutomation = wbAutomation.Sheets(1) ' 자동화 파일 시트

 

백데이터(?)와 자동화(?) 파일의 행과 열을 확인 해 각 파일에서 마지막 행을 찾아 처리할 범위를 지정

backendLastRow = wsBackend.Cells(wsBackend.Rows.Count, 3).End(xlUp).Row
automationLastRow = wsAutomation.Cells(wsAutomation.Rows.Count, 5).End(xlUp).Row

 

백데이터와 자동화 파일의

자동화 파일의 각 사원 정보와 백데이터의 사원 정보를 비교하여 일치하는 데이터를 찾아

일치하는 사원에 대해, 교대근무 값(주, 야, 휴주 등)을 숫자로 변환하여 자동화 파일에 복사

비교 및 데이터 복사하도록 설정

 

For i = 4 To automationLastRow
    empCode = wsAutomation.Cells(i, 1).Value ' 사원코드 # 백데이터의 사원코드 열
    empName = wsAutomation.Cells(i, 2).Value ' 사원명 # 백데이터의 사원명 열
    
    For j = 4 To backendLastRow
        backendEmpCode = wsBackend.Cells(j, 1).Value ' 사원코드 # 자동화 시트의 사원코드 열
        backendEmpName = wsBackend.Cells(j, 2).Value ' 사원명  # 자동화 시트의 사원명 열
        
        If backendEmpCode = empCode And backendEmpName = empName Then
            For k = 0 To maxColumns - 1 ' G~AK = 31열 # 요일과 날짜가 포함된 범위
                shiftValue = wsBackend.Cells(j, backendStartCol + k).Value
                
                ' 구분 값을 숫자로 변환
                Select Case shiftValue
                    Case "주"
                        numericValue = 100
                    Case "야"
                        numericValue = 200
                    Case "휴주"
                        numericValue = 300
                    Case "휴야"
                        numericValue = 400
                    Case "9-6"
                        numericValue = 500
                    Case Else
                        numericValue = 0 ' 기본값 처리
                End Select
                
                ' 자동화 파일에 값 입력
                wsAutomation.Cells(i, automationStartCol + k).Value = numericValue
            Next k
        End If
    Next j
Next i

 

변환 된 값이 입력된 후 백데이터 파일을 닫아 주어야하니

wbBackend.Close SaveChanges:=False

백데이터 파일을 저장하지 않고 닫기

 

작업이 완료 되었음을 안내

MsgBox "프로그램 작업이 완료되었습니다." & vbCrLf & "프로그램 종료 후 데이터를 검토하세요.", vbInformation

 

실행 후 파일이 자동으로 열리는 문제가 있으니 해당 구문으로 자동 종료 방

response = MsgBox("프로그램을 종료하시겠습니까?", vbYesNo + vbQuestion)
If response = vbYes Then
    Application.DisplayAlerts = False
    ThisWorkbook.Saved = True ' 변경 사항을 저장하지 않은 것으로 간주하여 종료 시 경고를 방지
    MsgBox "Excel 창은 종료되지 않았습니다. 프로그램 종료 후에도 작업을 계속 할 수 있습니다.", vbInformation
End If

 

주의 사항으로는

license Key.txt 의 암호를 이용하여 작동하기에 해당 파일이 꼭 지정된 폴더내에 있어야 한다.

파일 경로가 바탕화면과 다운로드 폴더를 지정하고 바라보며, 다른 경로에 저장된 파일을 사용 할 경우 위 코드를 참조 수정해야한다.

 

비교적 검증을 통하지 않았기에 해당 코드가 100% 활용 가능합니다.

(라고하면 맞겠지..?)