오늘은 회계 업무에서 자주 발생하는 SAP 전표 일괄 업로드를 위해 원본 데이터를 업로드 양식에 맞게 매크로를 활용하여 10초 이내에 작성할 수 있는 방법을 알아보도록 하겠습니다. 이 방법을 응용하면 업무시간을 대폭 줄일 수 있으실 겁니다.
매크로를 활용해 SAP 업로드 양식 10초 안에 만드는 방법
회사에서 회계쪽 업무를 관리하게 되어 업무를 들여다 보니, 업로드 양식에 데이터를 입력하여 SAP에 일괄 업로드 하는 일들이 많았습니다. 그런데 담당자들이 어떻게 하는지 보니 일일히 수작업으로 데이터를 복사 붙여넣기 하고 있었습니다. 그리고 이번달에 400줄이 넘는 대체전표를 그렇게 작업하려고 하더군요. 데이터가 몇 줄 안된다면 수작업이 빠를 수 있지만 몇백 줄이면 얘기가 달라집니다.
그래서 10초도 안되는 시간에 업로드 양식에 맞게 시트를 작성할 수 있는 엑셀 매크로 프로그램을 만들어 줬습니다. 담당자의 첫마디가 “우와~”일 정도로 업무시간을 단축시킬 수 있었습니다.
그래서 혹시 우리 회사뿐 아니라, 다른 회사에서도 동일한 업무를 해야 하는 분들을 위해 이번에 만든 엑셀 매크로 소스를 공유하니 필요하신 분들은 활용하시면 좋겠습니다.
엑셀 매크로라고 하니 어려움을 느끼시는 분들이 많은데 흥미가 있으신 분들은 제가 작성한 엑셀 매크로 관련 글을 참고하시면 쉽게 배우고 활용하실 수 있습니다.
1. SAP 업로드 양식 작성을 위한 조건
엑셀에서 매크로 사용을 위해 어떻게 셋팅을 해야 하는지에 대해서는 위의 링크를 참고하시면 됩니다.
우선, 아래와 같이 왼쪽에 있는 데이터를 오른쪽에 있는 양식으로 만들어야 한다면 어떻게 해야 할까요?
1) 1줄로 된 원본 데이터를 업로드 양식에 맞게 2줄로 만들어야 합니다.
2) 원본데이터의 컬럼과 업로드 양식의 컬럼을 대응시켜야 합니다. 예를 들어 원본데이터 V컬럼은 업로드 양식의 S컬럼과 대응되도록 해야 합니다.
3) 원본 데이터에 없는 데이터는 업로드 양식에 입력될 수 있도록 매크로 프로그래밍에 추가해 줘야 합니다. 예를 들어 업로드 양식에 있는 G컬럼은 원본데이터에 없기 때문에 매크로에 하드코딩 해줘야 합니다. (하드코딩이란 프로그램 소스에 직접 추가하는 것을 말합니다.)
4) 원본데이터가 끝날때까지 프로그램이 계속 순환해야 합니다. 예를 들어 원본데이터에 400줄이 있다면 400번 순환해야 합니다.
2. 매크로 프로그램 작성
위의 조건대로 작성한 매크로 프로그램은 아래와 같습니다. 각 줄에 주석을 달아 놓았으나, 핵심적인 내용에 대해서 소스를 설명드리겠습니다.
ws_from 은 원본데이터가 있는 시트이고,
ws_to는 업로드 양식 시트입니다.
roww 는 ws_from의 행을 가리키기 위한 변수이고,
row1은 ws_to의 행을 가리키기 위한 변수입니다.
lastRow = 0 lastRow = ws_to.Cells(Rows.Count, 1).End(xlUp).Row If lastRow <= 1 Then lastRow = 2 End If ws_to.Range("A2:BH" & lastRow).ClearContents
이 부분은 원본데이터를 업로드 양식에 채워 넣기 전에 업로드양식을 초기화 하는 코드입니다. 이렇게 함으로써 이전 데이터로 인해 잘못된 값이 들어가는 것을 방지할 수 있습니다.
이 부분은 ws_from의 A열에 데이터가 없을 때까지 순환을 시키는 코드입니다. 즉, 원본데이터의 마지막행까지 순환을 하도록 Do While를 사용하였습니다.
Do While ws_from.Cells(roww, "A").Value <> ""
아래는 전체 소스입니다.
Sub from_to() Dim ws_from As Worksheet Dim ws_to As Worksheet Set ws_from = ThisWorkbook.Sheets("from") Set ws_to = ThisWorkbook.Sheets("to") 'from 시트로 이동 ws_from.Select 'ws_from 시트의 row를 가리키는 변수 roww = 3 rv = msgshow("to 시트에 있는 기존 데이터를 삭제합니다.") 'to 시트에 있는 기존 데이터 삭제 lastRow = 0 lastRow = ws_to.Cells(Rows.Count, 1).End(xlUp).Row If lastRow <= 1 Then lastRow = 2 End If ws_to.Range("A2:BH" & lastRow).ClearContents roww = 2 'to 시트의 row를 가리키는 변수 row1 = 2 'to 시트의 grouping 번호를 위한 변수 rno = 1 Do While ws_from.Cells(roww, "A").Value <> "" ws_to.Cells(row1, "A").Value = rno '구룹핑번호 ws_to.Cells(row1 + 1, "A").Value = rno ws_to.Cells(row1, "B").Value = "X" '헤더지시자 ws_to.Cells(row1, "D").Value = cm & lastday '증빙일 ws_to.Cells(row1 + 1, "D").Value = cm & lastday ws_to.Cells(row1, "E").Value = cm & lastday '전기일 ws_to.Cells(row1 + 1, "E").Value = cm & lastday ws_to.Cells(row1, "F").Value = "6" '전기기간 ws_to.Cells(row1, "G").Value = "SB" '전표유형 ws_to.Cells(row1, "H").Value = "1000" '회사코드 ws_to.Cells(row1, "I").Value = "KRW" '통화 ws_to.Cells(row1, "L").Value = "귀속대체전표" ws_to.Cells(row1, "N").Value = "40" '전기키 ws_to.Cells(row1 + 1, "N").Value = "50" ws_to.Cells(row1, "O").Value = ws_from.Cells(roww, "J").Value '계정 ws_to.Cells(row1 + 1, "O").Value = ws_from.Cells(roww, "J").Value ws_to.Cells(row1, "S").Value = ws_from.Cells(roww, "V").Value '전표통화금액 ws_to.Cells(row1 + 1, "S").Value = ws_from.Cells(roww, "V").Value ws_to.Cells(row1, "W").Value = "1018" '사업장 ws_to.Cells(row1 + 1, "W").Value = "1018" ws_to.Cells(row1, "Y").Value = "5000" '사업장 ws_to.Cells(row1 + 1, "Y").Value = "5000" '사업영역 ws_to.Cells(row1, "AA").Value = "PS223321-IC-500087" '프로젝트 코드 ws_to.Cells(row1 + 1, "AA").Value = "" ws_to.Cells(row1, "AB").Value = "" '코스트센터 ws_to.Cells(row1 + 1, "AB").Value = ws_from.Cells(roww, "G").Value ws_to.Cells(row1, "AK").Value = ws_from.Cells(roww, "L").Value '참조키 1 ws_to.Cells(row1 + 1, "AK").Value = ws_from.Cells(roww, "L").Value ws_to.Cells(row1, "AL").Value = "" '참조키 2 ws_to.Cells(row1 + 1, "AL").Value = "" ws_to.Cells(row1, "AP").Value = ws_from.Cells(roww, "R").Value '텍스트(SGTXT) ws_to.Cells(row1 + 1, "AP").Value = ws_from.Cells(roww, "R").Value row1 = row1 + 2 rno = rno + 1 roww = roww + 1 rv = msgshow("from 시트의 데이터를 to 시트 양식에 맞게 입력하고 있습니다. ( " & rno & " 번째 데이터 입력중)") Loop rv = msgshow("") Application.ScreenUpdating = True Application.EnableEvents = True MsgBox "끝" End Sub Function msgshow(s As String) Application.StatusBar = s End Function
위의 코드를 여러분의 상황에 맞게 수정해서 사용하시면 업무시간을 많이 절약하실 수 있을 겁니다.
오늘은 위와 같이 SAP 전표 일괄 업로드를 위해 원본데이터를 SAP 업로드양식에 맞게 엑셀 매크로를 활용하여 10내 작성하는 방법을 설명드렸습니다. 복잡하지 않으니 현업에서 활용해 보세요.
*필요하신 분들이 있을 것 같아 관련 엑셀파일을 제공해 드리니 아래 링크를 클릭하여 다운로드후 사용하시면 됩니다.
