SAP 일괄 업로드 양식으로 쉽고 빠르게 만들어주는 엑셀 매크로

SAP에서 대량의 전표를 작성해야 할 때 사용하는 것이 일괄 업로드 기능입니다. 일괄 업로드 기능은 매우 편리하지만 데이터를 일괄 업로드 양식에 맞춰 작성하는 것이 쉬운 일은 아닙니다. 이 글에서는 어떤 엑셀 양식이라도 SAP 일괄 업로드 양식으로 쉽고 빠르게 만들어주는 엑셀 매크로에 대해 알아보겠습니다.

SAP 일괄 업로드 양식으로 쉽고 빠르게 만들어주는 엑셀 매크로

SAP를 사용해 보신 분들이라면 아시겠지만 전표를 입력하는 방법에는 2가지가 있습니다. 첫 번째는 전표를 하나씩 수작업으로 입력하는 방법이고, 두 번째는 데이터를 업로드 양식에 맞춰 작성한 후, SAP에서 해당 파일을 열어 일괄로 업로드하는 방법입니다.첫 번째 방법은 입력해야 할 전표가 몇 개 안 될 경우에 사용하는 것이 좋고, 두 번째 방법은 대량의 전표를 작성해야 할 때 업무 효율을 대폭 향상시킬 수 있습니다. 이 글에서는 두 번째 방법을 자주 사용하시는 분들에게 많은 도움이 될 수 있도록 어떤 엑셀 양식이라도 SAP 일괄 업로드 양식으로 쉽고 빠르게 만들어주는 엑셀 매크로를 만들어 보도록 하겠습니다. 맨 마지막에는 예제로 사용했던 엑셀 파일을 제공해 드리도록 하겠습니다.

1. SAP 일괄 업로드 작성 매크로가 필요한 이유

예를 들어 아래 A와 같이 50줄의 데이터가 있고, 이 데이터를 B- SAP 일괄 업로드 양식에 맞춰 작성해야 한다고 가정해 보겠습니다. 우선 A 시트의 첫 번째 줄의 각 column의 값을 B 양식의 첫 번째와 두 번째 줄에 복사 붙여넣기 해야 합니다. SAP 업로드 양식은 차대변을 구분하기 위해 2줄로 되어 있고, 첫 번째와 두 번째 줄의 계정과 전기키, 그리고 일부 컬럼의 내용이 다르게 작성되어야 합니다. 

즉, 데이터 1줄에 업로드 양식 2줄이 만들어지는 것이죠. 방금 설명드린대로 50줄의 데이터를 SAP 일괄 업로드 양식에 맞춰 작성한다면 100줄을 작성해야 한다는 의미가 됩니다. 만약 일괄 업로드할 데이터가 500개라면 1000줄을 작성해야 합니다. 많은 회계 담당자 분들이 자기만의 업로드 양식 작성 노하우를 가지고 있다 하더라도, 일괄 업로드 양식에 맞춰 파일을 작성하는 것은 시간이 많이 드는 번거로운 업무일 뿐입니다. 

저도 처음에는 일일이 일괄 업로드 양식을 작성하다가, 매달 동일한 업무를 해야 하는데 이렇게 해서는 안 되겠다는 생각에 엑셀 매크로를 활용해 아무리 데이터가 많아도 1초 안에 SAP 일괄 업로드 양식을 만들어주는 프로그램을 작성하였습니다.

[A  – SAP에 일괄 업로드할 데이터시트]

[B  – SAP 일괄 업로드양식시트]

위의 업로드 양식을 자세히 보면 데이터의 구조를 3가지로 구분할 수 있다는 것을 알 수 있습니다.

• 고정: ‘증빙일’이나 ‘전기일’은 해당 날짜를 고정적으로 입력해 줘야 하기 때문에 고정 데이터 형식입니다.
• 변동: ‘전표 통화금액’은 데이터 시트의 값을 가져와야 하기 때문에 변동 데이터 형식입니다.
• 고정 + 변동: 위의 그림에는 없지만 (첨부해 드리는 엑셀 예제 파일에서 확인 가능) ‘텍스트’는 ‘[매입][2024-02]’ + ‘Name’ + ‘Line Text’를 합쳐서 하나의 필드에 입력해 줘야 하기 때문에 고정+변동 데이터 형식입니다.

엑셀 매크로에서 이 3가지 데이터 구조를 구분해서 처리해 줘야 하기 때문에 매우 중요한 개념이므로, 잘 이해해 두시기 바랍니다.

2. 데이터 시트와 일괄 업로드 시트 매핑시키기

위에서 A는 일괄 업로드할 데이터라고 말씀드렸는데요. 고정된 데이터 형식으로 되어 있는 엑셀 시트만 대상으로 한다면, 이 매크로의 사용 범위가 매우 작아지기 때문에 어떤 데이터의 형식이나 구조가 나오더라도 적용할 수 있도록 중간에 매핑 시트를 만들어 사용할 계획입니다.

‘매핑 시트’는 ‘데이터 시트 A열의 데이터는 일괄 업로드 양식 시트의 C열에 입력한다’ 등으로 데이터 시트의 각 필드를 일괄 업로드 시트의 어떤 열에 매핑할지 정의하는 시트라고 이해하시면 되며, 아래 그림이 바로 데이터 시트와 일괄 업로드 시트를 매핑시키는 ‘매핑 시트’입니다.
A열은 일괄 업로드 시트의 열을 가리키며, B부터 E열은 고정 문자열과 데이터 시트의 데이터의 열을 가리킵니다.

몇 가지 항목에 대해서 설명드리면,

• ‘그룹핑 번호’는 일괄 업로드 시트의 2줄씩 묶어서 1부터 순차적으로 증가하는 번호로 매크로에서 자동으로 순번을 하며,
• 헤더 지시자는 ‘X’로 고정 입력하고
• 증빙일과 전기일은 오늘 날짜 기준으로 전월의 마지막 날을 자동으로 계산하여 입력하는 것으로, 오늘이 4월 1일이라면, 3월 31일을 결산일로 인식해서 입력한다는 의미입니다.
• 전표 통화 금액은 ?Q로 되어 있는데, 변동 데이터를 나타내는 것으로 데이터 시트의 Q열의 데이터를 가져와 입력한다는 의미입니다.
• ‘WBS NO’는 마찬가지로 데이터 시트의 J열의 데이터를 가져오라는 의미이며,
• ‘텍스트(STEXT)’는 ① “[매입][” & TEXT(EOMONTH(TODAY(),-1),”yyyy-mm”) & “] “을 처리하고 ②?T 즉, 데이터 시트의 T열의 데이터를 가져오고, ③ ‘/’를 입력하고, ④?K 즉, 데이터 시트의 K열의 데이터를 가져오라는 의미입니다. 결과값은 ①+②+③+④가 되는 것이지요.
• 전기키1, 전기키2는 데이터 시트 한 줄이 업로드 양식 시트에서는 2줄이라고 말씀드렸고, 전기키1은 첫 번째 줄, 전기키2가 두 번째 줄이 되며, 각각 차변과 대변을 나타냅니다.
• 계정1, 계정2는 각각 차변의 계정, 대변의 계정을 나타냅니다.

일괄 업로드 양식으로 작성해야 할 데이터 시트가 여러 개이고 매월 작업을 해야 한다면, 각각의 데이터 시트에 대응하는 매핑 시트를 만들어 놓고 사용하면 되기 때문에 그만큼 업무 시간을 단축시킬 수 있게 됩니다.

3. 매크로 소스 설명

위와 같이 왜 자동 일괄 업로드 매크로가 필요하고, 매핑 시트(이 글에서는 ’>>’이 매핑 시트명입니다)를 어떻게 작성하는지 설명하였습니다. 이제 설명한 내용을 바탕으로 매크로를 작성해 보겠습니다. 

매크로 소스의 구성 및 로직은 다음과 같습니다.

1) 변수 선언 및 초기화

2) 업로드양식시트에 기존 데이터가 있을 경우 모두 삭제 (초기화)

3) 데이터시트를 1행부터 마지막행까지 순환

  4) 구룹핑번호는 1부터 순차 증가시켜 업로드양식시트에 삽입

  5) ‘>>’ 시트 2행부터 62행까지 순환하면서 정의된 데이터를 업로드양식시트에 삽입

     6) 5)에서 순환할 때 고정 데이터와 데이터 시트에서 가져오는 변동 데이터를 판단하여 sss로 문자열 생성 

     7) ‘>>’ 의 각 열에 지정된 업로드 양식의 열에 sss 문자열 삽입

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
   
'변수 초기화
    roww = 2
    row1 = 2
    rno = 1
    ‘매크로 실행 속도 향상을 위해 엑셀 화면 업데이터 중지, 이벤트사용 중지, 자동계산 중지
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

1) 변수 선언 및 초기화

매크로 프로그래밍 시 시트 이름은 Set을 활용하여 변수로 선언하여 사용하는 것이 편리합니다. 또한 매크로를 프로그래밍할 때 주의해야 하는 것이 있는데, 매크로가 실행될 때 엑셀의 화면 업데이트와 자동 계산을 켜 놓으면 매크로 실행 속도가 현저하게 저하되기 때문에 가능한 해당 기능을 off 시켰다가 종료 시점에서 on 시키는 것이 좋습니다.

2) 업로드양식시트에 기존 데이터가 있을 경우 초기화

업로드 양식 시트에 데이터가 있을 경우 초기화하는 코드입니다. 이 코드가 필요한 이유는 예를 들어 업로드 양식 시트에 이미 100행까지 데이터가 쓰여져 있고, 지금 일괄 업로드 시트를 만들려고 하는 데이터 시트에 20행밖에 없다면, 업로드 양식 시트의 40행까지는 데이터 시트에서 데이터를 가져와 새로 작성하기 때문에 문제가 없는데, 41행~100행은 이전 데이터가 그대로 있기 때문에 문제가 됩니다. 그래서 새로운 데이터를 업로드 양식 시트에 작성할 때는 반드시 기존 데이터를 모두 초기화한 후 새로 작성하는 것이 좋습니다.

'업로드양식 기존 데이터 삭제
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행부터부터 마지막행까지 순환

데이터 시트의 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)을 업로드 양식 시트의 지정 셀에 입력합니다.

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) ‘>>’ 의 각 열에 지정된 업로드 양식의 열에 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

4. 전체 소스 코드

위에서 설명드렸던 전체 소스는 아래와 같습니다.

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
    
    '업로드양식 기존 데이터 삭제
    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

    '업로드양식 데이타 작성
    roww = 2
    row1 = 2
    rno = 1
    
    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 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 일괄 업로드 양식으로 쉽고 빠르게 만들어주는 엑셀 매크로에 대해 알아보았습니다. 대량의 데이터를 매달 SAP에 일괄 업로드가 필요하신 분들이라면 이 글을 참고하여 매크로를 적용해 보시기 바랍니다. 단언컨대 최소 1시간 이상의 업무 시간을 단축하실 수 있으실 겁니다.

이 글에서 사용한 엑셀 예제 파일 다운로드 하기