오늘 얘기하고 싶은 주제는 Excel VBA를 이용하여 SAP를 자동화하는 방법입니다. 우선 이게 뭔 소린인지부터 설명을 해야겠네요. SAP는 기업에서 사용하는 전사적 자원관리(Enterprise Resource Planning, ERP) 소프트웨어 중에서 세계시장 점유율이 가장 높은 제품입니다. 알기 쉽게 좀 과장해서 말하면 판매부서에서 제품 주문을 받아 시스템에 입력하면, 자동으로 생산부서에 통보가 날라 가고 그 주문량에 맞춰서 원료사용계획과 생산인력 할당이 이루어지고, 선적 계획과 수금 계획까지 잡히고, 이후 실행상태를 실시간으로 확인할 수 있는 그런 시스템이죠. 대부분의 기업에서 필요한 자료구조와 업무절차를 처리할 수 있도록 만들어 놓은 기업용 솔루션입니다. 대부분의 세계적인 다국적기업들이 SAP를 쓰고 있습니다.
근데 뭔가 되게 근사한 거 같긴 한데 막상 이거 설치해 놓고 쓰라고 하면 사용자들 대부분이 욕부터 합니다. 유저 인터페이스가 아주아주 거지같기 때문이죠. 제품번호는 다 번호로 되어 있고, 공장이름도 번호로 되어 있고, 거래처도 번호로 되어 있고, 심지어 명령어도 mb51 등의 아주아주 기억하기 힘든 코드로 되어 있는데 이런 외우기 힘든 암호들을 이용해서 업무를 처리하라고 강요합니다. 서로 다른 기업에서 공통적으로 사용할 수 있도록 하다보니 사업장/제품/거래처 등에 직접적인 이름을 사용하는 것이 아니라 숫자를 붙여 구분하도록 만들어진거죠. 친절한 메뉴와 직관적인 인터페이스에 익숙한 대부분의 윈도우즈 프로그램 사용자들은 적응하는데 상당한 시간이 걸립니다. 말하자면 아래아 한글에 익숙한 사람한테 이제부터 리눅스 vi 에디터를 사용해서 문서를 편집하라고 강요하는 식입니다.
하여튼 가뜩이나 번호도 외우기 힘든데 거기다가 기업업무의 특성상 비슷한 일을 반복해야 하는 일이 아주 잦다는 겁니다. 즉, 주문 입력하는 넘은 제품번호와 고객번호만 다른 수백건의 주문들을 허구헌날 입력해야 한다는 거고, 생산하는 넘은 비슷비슷한 번호의 수십-수백개의 제품과 원료를 양을 허구헌날 체크해서 망할 부장한테 엑셀로 챠트를 만들어 가야 한다는 겁니다. 이쯤되면 쓰기 익숙한 엑셀에다 주문정보를 입력한 다음 한 큐에 SAP에 입력하는 방법이나, 재고 확인해야 하는 원료를 엑셀에다 저장해 놨다가 버튼 하나 띡 누르면 SAP로부터 값을 쭈루룩 뽑아오는 프로그램이 없을까 하고 고민을 하게 되죠.이럴 때 필요한 것이 SAP GUI 스크립팅이라는 겁니다.
먼저 개념을 좀 잡아 보죠. SAP는 크게 내부 데이타베이스 계층, 어플리케이션 계층 그리고 유저인터페이스 계층으로 구분할 수 있습니다. 데이타베이스와 어플리케이션 계층은 사용자에게는 노출되지 않고 서버에서 백그라운드로 돌아가는 프로세스이므로 유저가 직접적으로 제어하기 어렵습니다. 사용자들은 웹 브라우저를 이용해서 인터넷에 접근하듯이 SAP GUI라는 클라이언트 프로그램을 이용하여 백그라운드의 데이타베이스 및 어플리케이션 서비스를 구동하게 됩니다. 저희가 하고자 하는 것은 이 SAP GUI라는 클라이언트 프로그램을 자동화하는 것입니다.
1. SAP 스크립트 레코더를 이용하여 SAP의 단위업무를 기록(.vbs 화일 생성)
2. 기록된 스크립트를 엑셀 VBA 에디터로 옮겨 modify
3. 엑셀에서 매크로를 실행하여 SAP를 자동으로 구동
그럼 이제부터 어떻게 하면 엑셀과 SAP를 이용해서 업무를 자동화할 수 있는지 차근차근 알아보도록 하겠습니다.
1. SAP 스크립트 기록
스크립트 레코더는 SAP GUI의 메뉴 아래 툴바 맨 오른쪽의 스크린 모양 아이콘을 눌러보면 나옵니다.
엑셀의 매크로 리코더와 마찬가지로 기록 버튼을 누르고 나면 사용자가 SAP GUI 상에서 수행하는 여러 작업들이 VB형식의 스크립트로 텍스트 화일에 저장되게 됩니다. TR-CODE는 가능하면 명령어 입력란(아래 그림 참조)에서 타이핑하시는 편이 코딩하기 편하고 수정하기도 쉽습니다. tr-code msc03 앞에 있는 "/n"은 현재 작업내용을 모두 취소하고 그 뒤에 따라오는 tr-code를 수행하라는 뜻입니다. tr-code입력 후 단위 작업을 마치고 기록중지 버튼을 누른 후 경로상의 vbs화일을 열어 보면 방금 행한 작업들이 vb명령어의 형태로 적혀 있는 것을 보실 수 있습니다. 재생 버튼을 누르면 기록된 스크립트가 윈도우즈 스크립트 호스트에 의해 수행되어 SAP를 구동하게 됩니다.
2. 기록된 스크립트를 편집
기록된 스크립트를 메모장을 이용해 열어보면 크게 두 가지 부분으로 구성된 것을 볼 수 있습니다. 앞 부분은 SAP GUI의 COM객체를 생성하여 연결하는 부분입니다. 이 부분은 모든 자동화 스크립트에 공통적인 것이라서 항상 같은 내용이 기록됩니다. 그리고 뒷부분은 실제 유저가 수행한 작업들이 기록됩니다.
'vbs의 SAP연결 코드는 아래와 같이 변경해 주시면 됩니다.
Dim rotEntry, guiApp, connection, session
Set rotEntry = GetObject("SAPGUI")
Set guiApp = rotEntry.GetScriptingEngine
Set connection = guiApp.Children(0)
Set session = connection.Children(0)
'작업내용 부분은 vbs 화일에서 복사해 오면 됩니다.
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmsc3n"
session.findById("wnd[0]").sendVKey 0
...
3. 엑셀에서 매크로를 구동
이제 코드가 다 작성되었으면 실행을 하면 됩니다. 작성된 코드는 버튼을 클릭할 때 동작하도록 하는 것이 가장 일반적이겠지요. 매크로는 SAP GUI가 실행된 상태에서 구동해야 합니다. 실행되고 있지 않으면 객체를 찾을 수 없어 매크로에서 런타임 에러가 납니다. 제대로 연결되면 SAP에서 아래와 같은 보안 메세지가 뜹니다. OK를 누르시면 SAP가 매크로에 기록된 작업을 수행하면서 화면이 차례로 바뀌는 것을 볼 수 있습니다.
자 이제 개념은 대략 잡히셨을거라 생각합니다. SAP 작업내용을 기록하고, 엑셀로 옮겨서 수정한 후 실행시킨다.. 이거죠. 근데 이상의 과정을 진행하는데 접하실 수 있는 몇 가지 문제를 짚고 넘어 가겠습니다.
곤란한 상황 1.
SAP의 스크립트 레코더가 유저한테 노출되지 않은 경우가 더러 있습니다. SAP GUI의 메뉴 아래 툴바 맨 오른쪽에 보면 스크린 모양의 아이콘이 있는데 이걸 선택하여 스크립트 레코더가 켜져 있는지 확인하시기 바랍니다.
가끔은 서버 측에서 스크립트 사용을 막아놓은 경우가 있습니다. 그럴 경우엔 시스템 관리자에게 열어달라고 요청해야 합니다.
응용예 1.
이제 응용부분을 좀 생각해 보죠. 어떤 작업을 기록하고 한번만 반복하는 건 사실 별 의미가 없습니다. 수십번 반복해서 해야될 일을 엑셀과 SAP에게 맡겨 놓고 나는 띵까띵까 놀 수 있으면 좋겠죠. 그럼 반복작업을 어떻게 구현할 수 있을지 살펴보겠습니다. 아래의 예제는 material 넘버를 쭉 나열해 놓고 버튼을 누르면 각 물질의 description을 SAP에서 불러오는 예제입니다.
Private Sub DescButton_Click()
Dim rotEntry, guiApp, connection, session, grid, t
Dim i As Integer, r As Range
On Error GoTo Err_Handler
Application.WindowState = xlMinimized
' SAP 연결 부분
Set rotEntry = GetObject("SAPGUI")
Set guiApp = rotEntry.GetScriptingEngine
Set connection = guiApp.Children(0)
Set session = connection.Children(0)
'사용자 작업부분
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03" 'mm03은 물질정보를 보는 tr-code
session.findById("wnd[0]").sendVKey 0
Set r = [A3]
r.Offset(, 1).Resize(1000).ClearContents '데이타 영역 초기화
Do While r <> ""
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = r.Value '물질번호 입력
session.findById("wnd[0]").sendVKey 0 '실행
'SAP의 description 컨트롤 항목에서 값을 읽어 옴.
Set t = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP01/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX")
r.Offset(, 1) = t.Text
session.findById("wnd[0]").sendVKey 3
Set r = r.Offset(1)
Loop
session.findById("wnd[0]").sendVKey 3
Application.WindowState = xlMaximized
MsgBox "Done!"
Exit Sub
Err_Handler:
MsgBox Err.Description, , "Error"
End Sub
회사마다 SAP가 조금씩 customize되어 있을 수 있기 때문에 제가 짠 매크로가 그대로 동작하지는 않을 수도 있습니다. 하지만 앞에서 말씀드린 개념을 잘 이해하신 분이라면 분석하시는데 큰 어려움이 없으실 겁니다. 매크로는 일단 SAP를 연결하고, mm03 transaction을 돌립니다. 그리고 [A3]부터 시작하여 공란이 나올 때까지 순차적으로 아래로 내려 가면서 각 셀의 값을 SAP에 입력하고, 결과 화면에서 description 부분을 읽어서 그 옆 칸에 적어넣는 거죠. 그런데 내가 원하는 값이 있는 SAP 상의 화면요소의 이름을 어떻게 알아내는지 궁금하시죠? 간단합니다. 매크로 레코딩할 때 결과화면에서 그 화면요소에 커서를 찍으면 기록된 매크로에 해당 화면요소의 이름이 같이 기록되어 나옵니다. 요렇게요.
session.findById("wnd...(중략).../txtMAKT-MAKTX").setFocus
session.findById("wnd...(중략).../txtMAKT-MAKTX").caretPosition = 39
그 화면요소의 text 프로퍼티를 읽어오면 되지요. 위의 예제 화일을 첨부하였으니 필요에 따라 수정해서 사용하시기 바랍니다.
SAP_example1.xlsm
응용예 2.
SAP의 결과 화면들은 가끔씩 테이블 형태로 나타납니다. 테이블 형태가 아닌 텍스트 화면들도 레이아웃을 잘 선택하면 테이블 형태로 바뀌는 경우도 꽤 있습니다. 이 테이블 형태의 자료는 컬럼이름과 행번호를 이용해 접근할 수 있습니다. 스크립트 리코더에서 결과 화면을 출력한 후 각 컬럼에 커서를 놓으면 해당 컬럼의 이름이 스크립트에 기록됩니다. 이것을 이용해 다음과 같은 방법으로 값을 읽어올 수가 있는 거죠.
'extract data from table
Set grid = session.findById("wnd[0]/usr/cntlGRID1/shellcont/shell")
Set r = [A2]
For i = 0 To grid.RowCount - 1
r.Offset(, 1) = grid.getcellvalue(i, "WERKS") 'plant
r.Offset(, 2) = grid.getcellvalue(i, "MATNR") 'material
r.Offset(, 3) = grid.getcellvalue(i, "CHARG") 'batch
r.Offset(, 4) = grid.getcellvalue(i, "ATNAM") 'char. name
r.Offset(, 5) = grid.getcellvalue(i, "ATBEZ") 'char. description
r.Offset(, 6) = grid.getcellvalue(i, "GV_OUTPUT_VALUE") 'value
Set r = r.Offset(1)
Next
테이블 화면요소를 grid라는 object로 할당해 놓고, 이후에 grid.getcellvalue(row_no, col_name) 형식으로 읽을 수 있습니다.
맺음말
저는 도대체 어디서 이런 정보들을 얻었을까요? 답은 아주 쉽습니다. SAP 툴바에서 스크립트 리코더를 실행시키는 메뉴 아래쪽을 보면 SAP GUI Help 등 프로그래밍 관련 정보가 나옵니다. 거길 살펴 보시면 SAP session을 연결하는 방법, 테이블 등 여러 가지 화면요소와 관련된 클래스/프로퍼티 등등 아주 많은 관련 정보들이 나옵니다. 그걸 참조하셔서 응용하시면 되는 거지요. 사실 위에 말씀드린 예제들만 응용하셔도 단순무식한 반복작업들을 상당히 줄일 수 있습니다. 그럼 본 포스팅이 귀사의 업무에 도움이 되셨길 바라며 장황한 글을 줄입니다.
p.s. 가끔 댓글로 도움 요청하시는 분들이 계시는데 제가 워낙 게을러서 댓글 자주 안 봅니다. 성질도 나빠서 댓글 봐도 잘 안 도와줍니다. ㅋㅋ 가끔 기분 내킬 때 답글 달 때도 있지만 너무 기대하지 마세요. 사실 이 정도 알려드렸으면 본인 스스로 찾아서 해결하셔야죠.
댓글 없음:
댓글 쓰기