How to automate how to process mass transaction without ABAP code (SAP ABAP coding 하지 않고 엑셀 매크로 VBA로 SAP 생산오더 만들기)

By -

'For English Readers'

SAP 프로젝트를 하다보면 개발자의 도움없이 특정 Tranaction을 대량으로 자동으로 실행시킬 경우가 있다. 물론 ABAP coding 을 배워서 하든지 ABAP개발자가 개발 하면 가능하다. 대량 Data를 엑셀로 정리하여 개발된 프로그램으로 실행하면 된다. 만약 상황이 여의치 않고, 본인이  엑셀 매크(VBA)를 조금 해본 경험이 있다면 아래 VBA source code 를 복사하여 엑셀로 개발하면 가능하다. 아래 SAP 생산오더 생성 및 변경을 위한 엑셀 매크로 VBA를 참조하시기 바랍니다.   

물론 생산오더 포함하여 SAP recording 이 가능한 모든 Transaction 에 대해 적용 가능하다. 다른 Transaction 을 실행하고 싶다면 SAP script recording를 실행하면 자동으로 VB source가 recording 되므로 크게 걱정할 필요는 없습니다. Recording 된 VBA source 파일(*.vbs)을 열어 "session.findById" 시작하는 문장을 엑셀로 복사하여 수정하면 된다.

그러면 Scirpt Recording 시작해보자. 

1. 아래 화면을 참조하여 Script Recording 기능을 활성화 해주어야 한다. 

        1.1. 먼저 t-code RZ11을 실행합니다.

     

        1.2. 매개변수 이름에 sapgui/user_scripting을 입력한 후 Display 버튼을 누른다.

        1.3. "Change Value"을 누른다.
        1.4. 새 값 상자에서 TRUE를 선택합니다. 그런 다음 저장 버튼을 클릭합니다.

        하단 오른쪽 모서리에 있는 이발소 모양 아이콘은 GUI 스크립팅이 활성화되었음을 보여준다.


2. SAP GUI에서 스크립팅을 활성화하려면

    2.1 SAP 사용자 정의를 클릭한 다음 옵션을 클릭합니다.
    2.2 SAP GUI 옵션 대화 상자에서 스크립팅을 클릭하거나 스크립팅 탭을 클릭한 다음 스크립팅 사용을 선택합니다.
    


    2.3 확인을 클릭합니다.

3. 아래 화면을 참조하여 Script Recording을 한다. 

    

    3.1 Script Recording을 저장할 file path 및 file 이름을 지정한다.

    

    3.2 원하는 t-code를 실행한다.
    3.3 원하는 t-code를 종료하고 Script Recording을 종료하려면 아래 메뉴를 다시 선택한다.
        

      3.4 아래 3개 버튼 중에서 중간에 있는 노란색 stop 버튼을 눌러 Script Recording 종료한다.
    

    3.5 저장된 파일을 열어 Visual Basic Script  확인한다.
     

 4. Recording 된 VBA source 파일(*.vbs)을 열어 "session.findById" 시작하는 문장을 엑셀로 복사하여 수정하면 된다.



>> For English Readers 


When you join a SAP project or are working on a SAP project, there are cases in which you might need to executed tranactions with large data volume without the help of ABAP developers. Of course, it is possible for you after learning ABAP coding or to ask ABAP developer to develop what you need. Firstly you need to prepare or collect large amounts of data in Excel and execute using the new ABAP programs. If this situation is not favorable for you, then if you have some experience with Excel macros (VBA), you can copy the VBA source code below and paste it in Excel VBA module. Please feel free to use the source of Excel macro VBA below for creating and changing SAP production orders below.

Of course, you can try other SAP transactions where SAP scrip recording is possible as same as create/change production order. If you want to execute other transactions, you do not have to worry too much because the VB source is automatically recorded when you execute SAP script recording. Just open the recorded VBA source file (*.vbs) and copy the sentence starting with "session.findById" to Excel VB and edit it. That's it.

Then let's start script recording.

1. Refer to the screen below to activate the Script Recording function.

    1.1. Firstly, execute t-code RZ11 
    
    1.2. Type sapgui/user_scripting. In the Param. Name box. Then press Display button.
    
    1.3. Press Change Value.

    1.4. Choose TRUE In the New value box. Then click Save button.

The icon of a barber pole in the bottom right corner showes that GUI scripting is enabled.

2. You need to enable scripting in SAP GUI

    2.1 Click Customize customize sap, and then click Options.
    2.2 Click Scripting or click the Scripting tab In the SAP GUI options, and select Enable Scripting.
    

    2.3 Click OK.


3. Then start Script Recording with reference to the menu path on screen below:
    
    

     3.1 Specify file path and file name to save Script Recording.
    
    

     3.2 Execute the t-code to record.

     3.3 Finish Script Recording with reference to menu below.
        

       3.4 Press the yellow stop button in the middle of three buttons below to stop Script Recording.
    

     3.5 Open the saved file to check the Visual Basic Script.
     

 4. Open the recorded VBA source file (*.vbs) and copy the sentence starting with "session.findById" and paste Excel VBA and edit it.
    

======================== End of article ========================
'Move Top'

'For English Readers'


* Please find VBA source code below to create / change process order COR1 & COR2 


Private Sub SAP_Process_Order(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim SapGui As Object
    Dim Applic As Object
    Dim connection As Object
    Dim session As Object
    Dim WSHShell As Object
    Dim ObjR3 As Object
    Dim Grid As Object
    Dim sap_message As String
    Dim start_date As String
    Dim end_date As String
'    Dim objBAPIControl As Object 'Function Control (Collective object)
'    Dim sapConnection As Object 'Connection object
'    Set objBAPIControl = CreateObject("SAP.Functions")
'    Set sapConnection = objBAPIControl.connection
'    Set ObjR3 = CreateObject("SAP.Functions")
    If Me.txtMaterialCode.Value <> "" And Me.txtStartDate.Value <> "" And Me.txtEndDate.Value <> "" And Me.txtQty.Value <> "" Then
        If Me.txtOrder.Value <> "" Then
            Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus
            Set WSHShell = CreateObject("WScript.Shell")
            Do Until WSHShell.AppActivate("SAP Logon ")
                Application.Wait Now + TimeValue("0:00:01")
            Loop
            Set WSHShell = Nothing
            Set SapGui = GetObject("SAPGUI")
            Set Applic = SapGui.GetScriptingEngine
            If shHome.Cells(3, 6).Value = "xxP" Then 'Choose SAP server Quality or Production server ?
                Set connection = Applic.OpenConnection("0.3         ECC ? PROD (xxP)", True)
            Else
                Set connection = Applic.OpenConnection("0.2         ECC ? QUAL (xxQ)", True)
            End If
            Set session = connection.Children(0)
            session.findById("wnd[0]").maximize
            session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
            session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = shHome.Cells(2, 6).Value
            session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = shHome.Cells(4, 6).Value
            session.findById("wnd[0]").sendVKey 0
            'Start of COR2, Change Process Order
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/nCOR2"
            session.findById("wnd[0]").sendVKey 0
            session.findById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text = Me.txtOrder.Value
            session.findById("wnd[0]").sendVKey 0
            'end of COR2
        Else
            'Start of COR1, Create Process Order
            If CDate(Me.txtStartDate.Value) >= Format(Now(), "yyyy-mm-dd") And CDate(Me.txtEndDate.Value) >= Format(Now(), "yyyy-mm-dd") Then
                Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus
                Set WSHShell = CreateObject("WScript.Shell")
                Do Until WSHShell.AppActivate("SAP Logon ")
                    Application.Wait Now + TimeValue("0:00:01")
                Loop
                Set WSHShell = Nothing
                Set SapGui = GetObject("SAPGUI")
                Set Applic = SapGui.GetScriptingEngine
                If shHome.Cells(3, 6).Value = "xxP" Then
                    On Error Resume Next
                    Set connection = Applic.OpenConnection("0.3         ECC ? PROD (xxP)", True)
                Else
                    On Error Resume Next
                    Set connection = Applic.OpenConnection("0.2         ECC ? QUAL (xxQ)", True)
                End If
                Set session = connection.Children(0)
                session.findById("wnd[0]").maximize
                session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
                session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = shHome.Cells(2, 6).Value
                session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = shHome.Cells(4, 6).Value
                session.findById("wnd[0]").sendVKey 0
                'change date format yyyy-mm-dd to dd.mm.yyyy
                start_date = Right(Me.txtStartDate.Value, 2) & "." & Mid(Me.txtStartDate.Value, 6, 2) & "." & Left(Me.txtStartDate.Value, 4)
                end_date = Right(Me.txtEndDate.Value, 2) & "." & Mid(Me.txtEndDate.Value, 6, 2) & "." & Left(Me.txtEndDate.Value, 4)
                session.findById("wnd[0]/tbar[0]/okcd").Text = "/NCOR1" 'Type T-Code to create Process Order in command window
                session.findById("wnd[0]").sendVKey 0 'Press Enter T-Code to create Process Order
                On Error GoTo ErrorHandler
                session.findById("wnd[0]/usr/ctxtCAUFVD-MATNR").Text = Me.txtMaterialCode.Value 'Enter Material Code
                session.findById("wnd[0]/usr/ctxtCAUFVD-WERKS").Text = "xxxx" 'Enter Plant Code
                session.findById("wnd[0]/usr/ctxtCAUFVD-WERKS").SetFocus
                session.findById("wnd[0]/usr/ctxtCAUFVD-WERKS").caretPosition = 4
                session.findById("wnd[0]").sendVKey 0
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOZE/ssubSUBSCR_5115:SAPLCOKO:5120/txtCAUFVD-GAMNG").Text = Me.txtQty.Value 'Enter QTY
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOZE/ssubSUBSCR_5115:SAPLCOKO:5120/ctxtCAUFVD-GLTRP").Text = end_date 'Enter end date of Process Order
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOZE/ssubSUBSCR_5115:SAPLCOKO:5120/ctxtCAUFVD-GSTRP").Text = start_date 'Enter start date of Process Order
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOZE/ssubSUBSCR_5115:SAPLCOKO:5120/ctxtCAUFVD-GSTRP").SetFocus
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOZE/ssubSUBSCR_5115:SAPLCOKO:5120/ctxtCAUFVD-GSTRP").caretPosition = 10
                session.findById("wnd[0]/tbar[1]/btn[30]").press 'Press Release button on application toolbar
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOWE").Select
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOWE/ssubSUBSCR_5115:SAPLCOKO:5190/ctxtAFPOD-CHARG").SetFocus
                session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOWE/ssubSUBSCR_5115:SAPLCOKO:5190/ctxtAFPOD-CHARG").caretPosition = 4
                If Me.txtBatch.Value <> "" Then
                    session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOWE/ssubSUBSCR_5115:SAPLCOKO:5190/ctxtAFPOD-CHARG").Text = Me.txtBatch.Value 'Enter batch No.
                    session.findById("wnd[0]/tbar[0]/btn[11]").press
                    session.findById("wnd[1]/usr/btnSPOP-VAROPTION1").press
                Else
                    Me.txtBatch.Value = session.findById("wnd[0]/usr/tabsTABSTRIP_5115/tabpKOWE/ssubSUBSCR_5115:SAPLCOKO:5190/ctxtAFPOD-CHARG").Text 'Enter Batch No
                End If
                session.findById("wnd[0]/tbar[0]/btn[11]").press 'Press Save button
                sap_message = session.findById("wnd[0]/sbar").Text 'Fectch system message to extract Process order No
                Me.txtOrder.Value = Mid(sap_message, 9, 7) 'Extract Process order No
                Set session = Nothing
                connection.CloseSession ("ses[0]") 'Logoff
                Set connection = Nothing
            Else
                MsgBox "Check start and end date"
            End If
        End If
    Else
        MsgBox "Check SAP Material code, Date, QTY"
    End If
Exit Sub
ErrorHandler:
    Set session = Nothing
    On Error Resume Next
    'connection.CloseSession ("ses[0]")
    Set connection = Nothing
    MsgBox "Log off all SAP screen"
    Exit Sub
End Sub
'For English Readers'

Location: 대한민국 서울특별시
- SAP R/3 Implementation for 25 years in SD, LE, MM, Retail / Fashion, WM, QM and PP - SAP project manager and OCM lead - Certified in SAP R/3 SD, FI/CO, Workbench (ABAP Development), CRM – Online store & Internet sales - Business Process Re-engineering (BPR) - 7 SAP SRM and 3 CRM (Supplier & Customer Relationship Management) implementation - Industry Experience: Pharmaceutical, Diagnostics, Retail, Fashion, Electronic, Electric, Petro-Chemical, Mill, Shipbuilding, Automotive - SAP Industry Solution: IS-Retail, IS-AFS, IS-MILL

Comments

Post a Comment

Popular posts from this blog

Material Label as a industry standard on traceability along the whole supply chain.

By -
Image
The automotive industry places increasing demands on traceability along the whole supply chain. To ensure this traceability, material flow and information flow from suppliers to customers have to be aligned. This can be achieved by a unique material label on the smallest package unit containing a clearly defined set of traceability information. Up to now, there is no common industry standard for such a label. The MAT-Label is a complement to existing labels such as VDA 4902, Odette and GTL. These existing logistics labels are taken into account and referenced. If you happen to scan a material label to decode, and change content to generate new 2D matrix barcode for testing, please try this How to decode barcode of Material Label   Please find simple decode function of text in 2D barcode below: Please find data identifers in mat-label below: For support & inquiry, please contact bryan.ohjs@gmail.com.
Read more »

How to execute Transaction with mass data without ABAP coding using SAP Script Recording, 코딩없이 대량 데이타를 SAP 업로드 할 수 있나요?

By -
Image
In the realm of SAP, there exists a menu that holds significant value for both SAP users and consultants. Allow me to acquaint you with this menu, a gateway to executing transactions involving substantial data volumes, all without the need for intricate ABAP coding. Intrigued? Imagine a scenario where you effortlessly transfer vast data sets from Excel to SAP transactions, all facilitated by a bit of VBA coding. This menu, known as "Script Recording and Playback" in SAP, is the key to this endeavor. To embark on this journey, simply copy the provided source code and paste it into the Visual Basic Editor. You can access this editor by launching Excel and pressing ALT+F11. The possibilities that unfold are truly captivating. Absolutely, I'm here to help you through the process. Let's break it down step by step: 1. Review the SAP VBS File: First, let's thoroughly review the SAP VBS file I've provided below which will help you understand its content and identify
Read more »