How To Extract Data From Sap To Excel Using Vba

Extracting Data from SAP to Excel using VBA

Extracting data from SAP to Excel can be achieved using VBA (Visual Basic for Applications), which is a programming language used to automate tasks in Microsoft Office applications. With VBA, you can interact with SAP’s GUI (Graphical User Interface) to retrieve the desired data and transfer it to an Excel worksheet. Here’s a step-by-step guide with examples on how to accomplish this:

Step 1: Enable SAP GUI Scripting

In order to use VBA to automate SAP, you must enable SAP GUI scripting on your SAP server. Follow these steps:

• Open the SAP Logon Pad and select the system you want to connect to.
• Click on the “Options” button.
• In the left-hand menu, expand “Accessibility and Scripting” and select “Scripting”.
• Check the box next to “Enable scripting” and click “OK”.
• Restart SAP Logon Pad for the changes to take effect.

Step 2: Create a VBA Macro in Excel

Open Excel and create a new workbook. Press Alt+F11 to open the VBA editor. In the editor, create a new module and write the following code:

    Sub ExtractDataFromSAP()
        ' Declare the necessary variables
        Dim SapGuiApp As Object
        Dim Connection As Object
        Dim Session As Object
        Dim WSH As Object
        Dim Workbook As Object
        Dim Worksheet As Object
        Dim i As Integer
        
        ' Start SAP GUI
        Set SapGuiApp = CreateObject("Sapgui.ScriptingCtrl.1")
        Set Connection = SapGuiApp.OpenConnection("SAPSystemID", True)
        Set Session = Connection.Children(0)
        
        ' Log in to SAP
        Session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "YourUsername"
        Session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "YourPassword"
        Session.findById("wnd[0]").SendVKey 0
        
        ' Navigate to the desired transaction code
        Session.findById("wnd[0]/tbar[0]/okcd").Text = "/nTransactionCode"
        Session.findById("wnd[0]").SendVKey 0
        
        ' Execute some actions in SAP, e.g., running a report
        
        ' Create a new workbook and worksheet in Excel
        Set WSH = CreateObject("WScript.Shell")
        WSH.AppActivate "SAP - YourTransactionCode"
        WSH.SendKeys "^n"
        Set Workbook = Workbooks.Add
        Set Worksheet = Workbook.Worksheets(1)
        
        ' Retrieve data from SAP and transfer it to Excel
        For i = 1 To 100 ' Assuming you have 100 rows to extract
            Worksheet.Cells(i, 1).Value = Session.findById("wnd[0]/usr/lbl[1," & i & "]").Text
            Worksheet.Cells(i, 2).Value = Session.findById("wnd[0]/usr/lbl[2," & i & "]").Text
            ' Repeat the above line for each column you want to extract, adjusting the cell references accordingly
        Next i
        
        ' Save and close the workbook
        Workbook.SaveAs "C:\Path\To\Your\Excel\File.xlsx"
        Workbook.Close
        
        ' Logout and close SAP
        Session.findById("wnd[0]").Close
        Session.findById("wnd[1]").SendVKey 0
        Connection.Close
        
        ' Clean up the objects
        Set SapGuiApp = Nothing
        Set Connection = Nothing
        Set Session = Nothing
        Set WSH = Nothing
        Set Workbook = Nothing
        Set Worksheet = Nothing
    End Sub
    

Replace the placeholders, such as “SAPSystemID”, “YourUsername”, “YourPassword”, “TransactionCode”, and file path in the code with the appropriate values for your SAP system and needs.

Step 3: Run the VBA Macro

Press Ctrl+S to save the Excel workbook that contains the VBA macro. Close the VBA editor, and then run the macro by either pressing Alt+F8 to open the macro dialog or going to “Developer” > “Macros” and selecting the macro from the list. Click “Run” to execute the macro.

The VBA macro will automate the login process, navigate to the desired transaction code, retrieve the data from SAP, and transfer it to an Excel worksheet. Finally, it will save the Excel file and close SAP.

You can customize the VBA code to meet your specific requirements, such as extracting data from different SAP transactions, performing calculations or transformations on the data, or formatting the Excel worksheet.

Note: This example assumes you have basic knowledge of VBA programming and the SAP GUI. You may need to adjust the code based on your specific SAP system configuration and transaction code layout. It’s always recommended to test the code in a non-production environment before using it in a production environment.