이번 편에서는 전편에서 분석한 결과를 바탕으로 엑셀 매크로 코드를 작성하도록 하겠습니다. 여러번 반복해서 보시면 어렵지 않게 이해하고 현업에 활용하실 수 있으니 꼭 활용해 보세요. 퇴근 시간을 최소 30분 이상 줄이실 수 있으실 겁니다.
SAP 일괄 업로드 양식 자동 생성, 엑셀 매크로 활용 - 2/2
분석결과를 바탕으로 매크로 소스 작성
우선 매크로 소스의 구성 및 로직은 다음과 같습니다.
- 1) 시트와 변수 선언 및 초기화
- 2) 업로드양식에 기존 데이터가 있을 경우 삭제
- 3) 업로드자료 시트를 처음부터 끝까지 순환
- 4) 구룹핑번호는 1부터 순차적으로 증가시켜 업로드 양식에 삽입
- 5) “>>” 시트 2행부터 62행까지 순환하면서 정의된 문자열을 업로드 양식에 삽입
- 6) 5)에서 순환할 때 고정 문자열과 업로드자료에 있는 데이터를 가져오는 변동 문자열을 판단하여 sss변수에 문자열 생성
- 7) “>>”시트에 지정된 업로드 양식 열(column)에 sss 문자열 삽입
1) 시트와 변수 선언 및 초기화
매크로 프로그래밍을 할 떄 시트명은 가능한 Set 을 활용하여 변수로 선언하여 사용하는 것이 편리합니다.
그리고 매크로가 실행될 때 엑셀의 화면 업데이트와 자동계산을 켜 놓으면 속도가 현저하게 저하되기 때문에 가능한 해당 기능을 off 시켰다가 종료시점에서 on 시키는 것이 좋습니다.
Dim ws_eas As Worksheet Dim ws_cho As Worksheet Dim ws_rep As Worksheet '업로드자료, ">>", 업로드양식 시트를 변수로 설정 Set ws_eas = ThisWorkbook.Sheets("업로드자료") Set ws_cho = ThisWorkbook.Sheets("업로드양식") Set ws_rep = ThisWorkbook.Sheets(">>") '업로드자료 시트의 행을 나타내는 변수 Dim roww As Integer Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '변수 초기화 roww = 2 row1 = 2 rno = 1
2) 업로드양식에 기존 데이터가 있을 경우 삭제
이 코드가 필요한 이유는 예를 들어 업로드양식에 이미 100행까지 데이터가 쓰여져 있고, 지금 자동으로 업로드양식을 만들려고 하는 업로드자료가 20행밖에 없다고 가정하겠습니다. 이 경우 업로드자료 1행이 업로드양식 2행으로 작성되기 때문에 총 40행이 작성되게 됩니다.
즉, 40행까지는 업로드자료에서 데이터를 가져와 새로 작성했기 때문에 문제가 없는데 40행~100행은 이전 데이터이기 때문에 이걸 그대로 쓰면 잘못된 데이터가 SAP에 업로드 되어 문제가 됩니다. 그래서 새로운 데이터를 업로드양식에 작성할 때는 반드시 기존 데이터를 삭제하여 초기화 한 후 새로 작성하는 것이 좋습니다.
'업로드양식 기존 데이터 삭제 lastRow = 0 lastRow = ws_cho.Cells(Rows.Count, 1).End(xlUp).Row If lastRow <= 1 Then lastRow = 2 End If ws_cho.Range("A2:BH" & lastRow).ClearContents
3) 업로드 데이터 시트를 처음부터 끝까지 순환
이 코드가 매크로의 중요 로직의 시작부분입니다. 업로드자료 시트의 1행부터 데이터가 있는 마지막 행까지 Do while 문을 사용하여 순환시킵니다.
Do While ws_eas.Cells(roww, "A") <> ""
4) 구룹핑번호는 1부터 순차적으로 증가시켜 업로드 양식에 삽입
업로드양식에서 유일하게 ">>"시트를 참조하지 않는 컬럼입니다. 매크로에서 자동으로 1번부터 채번하여 카운트를 증가시키며, 1행과 2행은 1, 3행과 4행은 2, 5행과 6행은 3 등의 형식으로 2줄씩 채번이 증가하게 됩니다.
이를 위한 변수가 rno입니다.
5) “>>” 시트 2행부터 62행까지 순환하면서 정의된 문자열을 업로드 양식에 삽입
앞에서도 설명하였지만, 업로드양식에 작성되는 텍스트와 형식은 “>>”시트에 정의되어 있기 때문에 그룹핑번호를 제외한 2행부터 62행까지 For 문을 활용해 순환하면서 하나씩 처리해야 합니다.
For j = 2 To 62
6) 5)에서 순환할 때 고정 문자열과 업로드자료 시트에 있는 데이터를 가져오는 변동 문자열을 판단하여 sss변수에 문자열 생성
“>>” 시트의 2행부터 62행까지 고정 텍스트와 업로드자료 시트에서 가져와야 하는 데이터를 구분하여 하나의 문자열로 만들고 만들어진 문자열(sss)을 업로드양식의 지정 셀에 삽입을 합니다.
즉, 이 코드에서 “>>”시트의 B2부터 B62까지 각각 정의된 텍스트를 그대로 업로드양식에 써도 되는 것인지 아니면 업로드자료 시트에서 가져와야 하는지, 또는 고정텍스트와 업로드자료 시트에서 가져온 것이 혼합되어 있는지 판단하여 sss 문자열로 만들어야 합니다. 이렇게 하려면 문자가 고정인지 아니면 업로드자료 시트에서 가져와야 하는지를 구분해야 하는데 그 구분자를 이 매크로에서는 “?” 를 사용하였습니다.
B열에 작성되어 있는 문자열의 시작이 “?”로 되어 있으면 업로드자료 시트에서 가져오는 것으로 인식하고, “?”시작하지 않으면 쓰여진 그대로 업로드양식에 삽입하는 것이죠.
그런데 문제는 텍스트(SGTXT)와 같이 고정과 변동을 여러 개 조합해야 하는 경우입니다. 이 문제는 “>>”시트의 여러 column를 활용하여 아래와 같이 표현하여 해결하였습니다.
cc1 = 0 cc2 = 2 '몇 개의 열에 데이터가 정의되어 있는지 확인 Do While ws_rep.Cells(j, cc2 + cc1).Value <> "" cc1 = cc1 + 1 Loop sss = "" '사용된 열의 개수만큼 For 순환 For i = 1 To cc1 col = ws_rep.Cells(j, cc2 + i - 1).Value '문자열의 맨앞에 ?가 있을 경우 업로드자료 시트에서 데이터를 가져오고 '없으면 문자열 그대로 사용 If Left(col, 1) = "?" Then col = Mid(col, 2, Len(col) - 1) sss = sss & ws_eas.Cells(roww, col).Value Else sss = sss & col End If Next i
7) “>>”시트에서 지정된 업로드양식 열(column)에 sss 문자열 삽입
이제 6)에서 sss로 만들어진 텍스트를 업로드양식의 해당 셀에 써 넣기만 하면 됩니다. 여기서는 Select Case 문을 활용하여 최대한 단순하게 구현하였습니다.
Select Case (j) Case 2 '헤더지시자 ws_cho.Cells(row1, j).Value = sss Case 4, 5 '증빙일, 전기일
ws_cho.Cells(row1, j).Value = sss
ws_cho.Cells(row1 + 1, j).Value = sss
Case 6, 7, 8, 9, 12, 14 '전기기간, 전표유형, 회사코드, 통화, 전표헤더 텍스트, 전기키 1
ws_cho.Cells(row1, j).Value = sss
Case 15 '전기키 2
ws_cho.Cells(row1 + 1, j - 1).Value = sss
Case 16 '계정 1
ws_cho.Cells(row1, j - 1).Value = sss
Case 17 '계정 2
ws_cho.Cells(row1 + 1, j - 2).Value = sss
Case 21, 25, 27, 29, 34, 39, 40, 41, 44 '전표통화금액, 사업장, 사업영역, WBS_NO, 지정, 참조키1, 참조키2, 참조키3, 텍스트(SGTXT)
ws_cho.Cells(row1, j - 2).Value = sss
ws_cho.Cells(row1 + 1, j - 2).Value = sss
End Select
위에서 기능별로 구분하여 설명드렸던 전체 소스는 아래와 같습니다.
Sub 업로드양식_작성() Dim ws_eas As Worksheet Dim ws_cho As Worksheet Dim ws_rep As Worksheet '업로드자료, ">>", 업로드양식 시트를 변수로 설정 Set ws_eas = ThisWorkbook.Sheets("업로드자료") Set ws_cho = ThisWorkbook.Sheets("업로드양식") Set ws_rep = ThisWorkbook.Sheets(">>") '업로드자료 시트의 행을 나타내는 변수 Dim roww As Integer Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '변수 초기화 roww = 2 row1 = 2 rno = 1 '업로드양식 기존 데이터 삭제 lastRow = 0 lastRow = ws_cho.Cells(Rows.Count, 1).End(xlUp).Row If lastRow <= 1 Then lastRow = 2 End If ws_cho.Range("A2:BH" & lastRow).ClearContents Do While ws_eas.Cells(roww, "A") <> "" ws_cho.Cells(row1, "A").Value = rno '구룹핑번호 ws_cho.Cells(row1 + 1, "A").Value = rno For j = 2 To 62 cc1 = 0 cc2 = 2 '몇 개의 열에 데이터가 정의되어 있는지 확인 Do While ws_rep.Cells(j, cc2 + cc1).Value <> "" cc1 = cc1 + 1 Loop sss = "" '사용된 열의 개수만큼 For 순환 For i = 1 To cc1 col = ws_rep.Cells(j, cc2 + i - 1).Value '문자열의 맨앞에 ?가 있을 경우 업로드자료 시트에서 데이터를 가져오고 '없으면 문자열 그대로 사용 If Left(col, 1) = "?" Then col = Mid(col, 2, Len(col) - 1) sss = sss & ws_eas.Cells(roww, col).Value Else sss = sss & col End If Next i Select Case (j) Case 2 '헤더지시자 ws_cho.Cells(row1, j).Value = sss Case 4, 5 '증빙일, 전기일 ws_cho.Cells(row1, j).Value = sss ws_cho.Cells(row1 + 1, j).Value = sss Case 6, 7, 8, 9, 12, 14 '전기기간, 전표유형, 회사코드, 통화, 전표헤더 텍스트, 전기키 1 ws_cho.Cells(row1, j).Value = sss Case 15 '전기키 2 ws_cho.Cells(row1 + 1, j - 1).Value = sss Case 16 '계정 1 ws_cho.Cells(row1, j - 1).Value = sss Case 17 '계정 2 ws_cho.Cells(row1 + 1, j - 2).Value = sss Case 21, 25, 27, 29, 34, 39, 40, 41, 44 '전표통화금액, 사업장, 사업영역, WBS_NO, 지정, 참조키1, 참조키2, 참조키3, 텍스트(SGTXT) ws_cho.Cells(row1, j - 2).Value = sss ws_cho.Cells(row1 + 1, j - 2).Value = sss End Select Next j roww = roww + 1 row1 = row1 + 2 rno = rno + 1 Loop Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic MsgBox "끝" End Sub
지금까지 SAP 업로드양식을 자동으로 생성하기 위한 매크로 코드에 대해서 상세히 설명드렸습니다. 여러 상황에서 유연하게 사용할 수 있도록 코드를 작성하였고, 코드가 복잡하지 않기 때문에 필요하신 분들은 활용해 보셨으면 합니다.
이 글에서 사용한 엑셀 예제 파일은 여기를 클릭하면 다운로드 가능합니다.
