Connect to SAP via Excel VBA

This was my first main automation in Excel VBA. I was working a lot on SAP data and modifying it in Excel on the daily basis. Almost every day tons of same routine for get the data. One day I thought to myself about automation. I knew that I can record my activity in Excel, but did not know at all how to connect to SAP using Excel VBA?

Connect to SAP via Excel VBA

Open SAP application

Firstly, if we don’t want to open SAP manually, we need to provide its directory.

'Change for your file directory
Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", 4
Set WshShell = CreateObject("WScript.Shell")

Then just wait for the activation of application, because sometimes it can lasts longer than usually.

Do Until WshShell.AppActivate("SAP Logon ")
    Application.Wait Now + TimeValue("0:00:01")
Loop

Set SAP app as object

After that we should set some objects to handle SAP.

Set SapGui = GetObject("SAPGUI")
Set Appl = SapGui.GetScriptingEngine
Set Connection = Appl.Openconnection("paste name of module", True)
Set session = Connection.Children(0)

In Connection object put the name of the module you want to connect (from the SAP Logon Module screen below). It is important to put proper name, in other case You won’t connect.

Connect to SAP via Excel VBA

Log in with the data

In my case there was no need to login and write password, but if it is neccessary in your case I am also prepared for that.

SAP Login Password
SAP Login interface
'if You need to pass username and password
session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "100"
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "user"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "password"
session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

In first row there is unit number, in second put your user login, in third your password and in the last row choose language.

Check if it’s already opened

I also implemented to my SAP code check if it is already opened.

If session.Children.Count > 1 Then

    answer = MsgBox("You've got opened SAP already," & _
"please leave and try again", vbOKOnly, "Opened SAP")

    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").Select
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press

    Exit Sub

End If

In the end of connection, code is approving all the things from above and You are ready to do anything SAP.

session.findById("wnd[0]").maximize
session.findById("wnd[0]").sendVKey 0

After that You can put your code, which is operating in SAP system. You can do this for example by script recording in Options.

Full code:

Sub SapConn()

Dim Appl As Object
Dim Connection As Object
Dim session As Object
Dim WshShell As Object
Dim SapGui As Object

'Of course change for your file directory
Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", 4
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 Appl = SapGui.GetScriptingEngine
Set Connection = Appl.Openconnection("paste name of module", _
    True)
Set session = Connection.Children(0)

'if You need to pass username and password
session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "900"
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "user"
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "password"
session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

If session.Children.Count > 1 Then

    answer = MsgBox("You've got opened SAP already," & _
"please leave and try again", vbOKOnly, "Opened SAP")

    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").Select
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press

    Exit Sub

End If

session.findById("wnd[0]").maximize
session.findById("wnd[0]").sendVKey 0 'ENTER

'and there goes your code in SAP

End Sub

Summary

It took me couple of hours to find information in web how to connect to SAP via Excel VBA and finally set this up as code. After that I was only doing copy paste that to my next macros.

I just wanted to save your time, because I know how long and painful was that for me.

This code already helped one guy from StackOverflow – I hope it will help You too.

I also invite you to the next article in this series!
SAP session.findbyid – 10 code lines you can identify

Tomasz Płociński

Author: Tomasz Płociński

Excel VBA enthusiast who is also open for other languages. Mainly working in VBA, some SQL, hungry for more.

64 thoughts on “Connect to SAP via Excel VBA”

  1. Thanks for sharing this code.
    It was so helpful and worked like a charm.
    I really love the developer community , sharing code is such a great part of the developer community DNA. Why re invent the wheel 🙂

  2. If session.Children.Count > 1 Then

    answer = MsgBox(“You’ve got opened SAP already,” & _
    “please leave and try again”, vbOKOnly, “Opened SAP”)

    session.findById(“wnd[1]/usr/radMULTI_LOGON_OPT3”).Select
    session.findById(“wnd[1]/usr/radMULTI_LOGON_OPT3”).SetFocus
    session.findById(“wnd[1]/tbar[0]/btn[0]”).press

    Exit Sub

    End If

    This part of code do nothing in my case. Is this to terminate multiple seesion login? Please explain the purpose in more detail. By the way, a big thanks, your code is very crispy to use and understand.

    1. Ok.. I got the reason.

      session.findById(“wnd[0]”).maximize
      session.findById(“wnd[0]”).sendVKey 0

      This enter command is to be before if loop i.e. connection testing loop. It is now working like a charm. Thanks a ton. You saved my lot of time.

      1. Yes, this part is detecting if there is more than 1 session already opened.
        Glad to hear that, appreciate that 🙂

  3. Hello, thank you that’s exactly what I was looking for. thanks to you I think saved a lot of time because I was currently obliged to have SAP open and connected. do you know if there will be a test version of SAP that I could install on my personal machine so that I can continue my project at home? thank you very much again.

    1. I haven’t yet looking for that kind of thing, but it would be great to have it for video tutorial purpose.
      If I find that, You will see this here on website for sure. If You will be faster than me I’ll be thankful for sharing it here. Thank You too 🙂

  4. Hi Tomasz!
    Thanks for all the info, very helpful. I’m gettin an error “605: he ‘Sapgui Component’ could not be instantiated”. Are you familiar with this? I’m using SAP 740.
    Thanks again

      1. Hi Tomasz, thank you for your response! It was due to a version issue, I had 740 and just upgraded to 750 and it works incredibly. Thanks!

  5. hello looking on the internet i found this.
    https://blogs.sap.com/2018/09/13/as-abap-7.52-sp01-developer-edition-concise-installation-guide/
    I think this is what I was looking for I will do the tutorial and see where it leads me :). the first who arrives to implement the trial service will give the information to others 👍.
    Small SAP script question did you succeed with Excel to print in PDF format? or use Microsoft print to pdf and take control of the window to enter the file name automatically. I’m tearing my hair out. Thank you in advance.

    1. I don’t want to spoil your enthusiasm, but I don’t think this tutorial is for home users, but rather how to install an SAP server. =(
      According to your pdf question, try this.
      Please ask questions connected to the topic of article next time (not to make a “spam”) 🙂
      ThisWorkbook.Worksheets("Sheet1").Range("A1:N24").ExportAsFixedFormat Type:=xlTypePDF, _
      filename:=filename, _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, _
      OpenAfterPublish:=True

      1. I’m sorry, I won’t do it again. thank you for the piece of code, but i’m already using it and it doesn’t do what i want to do with the print sent by SAP. Thanks again. I will try when the test server and keep you posted if the tests are successful. Good night.

  6. Hi Tomasz, Thank your input.
    This code below does not work, it don’t populate the account and password automatically. Is it for safety reasons?
    Thanks

    'if You need to pass username and password
    session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "900"
    session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = "user"
    session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = "password"
    session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

  7. Hi Tomasz, thank you for your help..
    For the part bellow, it’s not working for me, I can run sSAP program several time and I don’t get the msg bellow if the session was already opened
    ————————————

    If session.Children.Count > 1 Then
    answer = MsgBox("You've got opened SAP already," & _
    "please leave and try again", vbOKOnly, "Opened SAP")
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").Select
    session.findById("wnd[1]/usr/radMULTI_LOGON_OPT3").SetFocus
    session.findById("wnd[1]/tbar[0]/btn[0]").press
    Exit Sub
    End If

    1. Hello, I appreciate your feedback.
      I checked the code once again and I can see that on the newest version of SAP this part of code can’t see more than 1 session, so will not work.
      Good point 🙂

  8. Great blog post really helpful.

    I am having another issue in this regard that I am hoping that you have a solution.

    I have created a macro to run MARC table and I need to choose specific fields in this table. For this, I have used approach as following example:
    session.findById("wnd[0]/mbar/menu[3]/menu[2]").select
    session.findById("wnd[1]/usr/chk[2,8]").selected = true

    But, now the problem is that the number of Rows on wnd(1) keeps changing everytime out IT team updates SAP.

    Is there a way to select these rows of Field using their technical name? Se example below for names I have extracted for these selection window.
    X MATNR Material
    X WERKS Plant
    PSTAT Maintenance status
    X LVORM DF at plant level
    BWTTY Valuation Category
    XCHAR Batch management
    MMSTA Plant-sp.matl status
    MMSTD Valid from
    X MAABC ABC Indicator
    KZKRI Critical Part
    X EKGRP Purchasing Group
    AUSME Unit of issue
    DISPR MRP profile
    X DISMM MRP Type
    X DISPO MRP Controller
    KZDIE MRP controller=buyer
    PLIFZ Planned Deliv. Time
    WEBAZ GR Processing Time
    PERKZ Period Indicator
    AUSSS Assembly scrap (%)
    X DISLS Lot size
    X BESKZ Procurement type

    1. I have not been dealing with looping through SAP Tables yet.
      But after your comment I tried to dig into this topic and… it’s a whole material for full article 🙂
      Long story short – if session.findById("wnd[1]/usr/chk[2,8]") is your checkbox, so You need to deal with its parent session.findById("wnd[1]/usr/chk[2,8]").Parent or session.findById("wnd[1]/usr") – it has all the information about rows and columns to let You loop through.
      If it’s not enough, You need to be patient and wait for the article, where I’ll describe what I found out in case of SAP Tables and their fields like text or checkbox. 🙂

  9. Dear Tomasz Płociński,
    I was very puzzled that I tried to run your VBA code, it was always stuck at “Set session = Connection.Children(0)”. Error message said “Run-time error ‘614’: The enumerator of the collection cannot find en element with the specified index.” Kindly give me an advice.

    1. I did not have any similar issue yet, but according to this:
      – check if scripting is activated in SAP
      – try to change from 0 to 1 in .Children(0), both in session and Connection
      – if nothing helped, re-installation of SAP Logon app can help.

      1. Dear Tomasz Płociński,

        Thank you for your reply. I did try your method but it was failure. Unfortunately, this SAP software was unable to reinstall due to the controller of my company, so I unable to do so.

        1. Can You try there also instead of 0 to put: “0” or 0+0 or CLng(“0”) or CLng(0) ?

      2. Hey Tomasz,

        I was so excited to see this code.

        But I am having the same issue with Set session = Connection.Children(0)

        even when it is changed from 0 to 1
        And also I am not allowed to re-install SAP.

        Run-time error ’91’

        1. Hello Teddy
          Did You check if scripting is activated in SAP?
          Check also if the name of connection is correct Set Connection = Appl.Openconnection("paste name of module", True)

          1. Try to change the name of Connection object, for example to conn1
            Don’t forget also to declare this before: Dim conn1 as Object

  10. Hello,
    first time the code works perfectly, but if I have SAP already opened and run code again I have error in excel :

    “Run-time error – 2147….
    Automation error
    The object invoked has disconnected from its client”

    How can I avoid it?

    Thank you.

  11. Hi,
    am a new user for macros can you tell me what is module name to paste here?am using PE1 and ERP

    Thanks
    Dhamo

    1. Hello Dhamo!
      Paste there the connection (module) name, which You choose in SAP Logon window.

  12. Set Connection = Appl.Openconnection(“paste name of module”, _
    True)
    I am getting error message here, ” SAP Logon connection entry not found”
    I not using SAP login credential, I will execute the script once I am in home page of SAP Login Page.
    Kindly help me to run the script to execute the SAP TCOde
    Below is my recorded script:
    If Not IsObject(application) Then
    Set SapGuiAuto = GetObject(“SAPGUI”)
    Set application = SapGuiAuto.GetScriptingEngine
    End If
    If Not IsObject(connection) Then
    Set connection = application.Children(0)
    End If
    If Not IsObject(session) Then
    Set session = connection.Children(0)
    End If
    If IsObject(WScript) Then
    WScript.ConnectObject session, “on”
    WScript.ConnectObject application, “on”
    End If
    session.findById(“wnd[0]”).maximize
    session.findById(“wnd[0]/tbar[0]/okcd”).text = “/nZ040”
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/tbar[1]/btn[17]”).press
    session.findById(“wnd[1]/usr/txtENAME-LOW”).text = “bosead”
    session.findById(“wnd[1]/usr/txtENAME-LOW”).setFocus
    session.findById(“wnd[1]/usr/txtENAME-LOW”).caretPosition = 6
    session.findById(“wnd[1]/tbar[0]/btn[8]”).press
    session.findById(“wnd[0]/tbar[1]/btn[8]”).press
    session.findById(“wnd[0]/tbar[1]/btn[5]”).press
    session.findById(“wnd[1]/tbar[0]/btn[0]”).press

    1. Hello
      You are getting error ”SAP Logon connection entry not found”, because the name of chosen module/connection in Connection is wrong.
      Check and correct the name and the code will work 🙂

  13. hello Tomasz

    what about if the username and password is not right (can’t connect to SAP)
    where the vba script to check about that. can you give me that vba for check wrong username and password

    thanks for your

    1. I’m sorry for late answer.
      In case of wrong username/password You can check the status bar if there appears “Name or password is incorrect (repeat logon)”
      I will do post about that, thanks for idea 🙂
      Stay tune for that 🙂

  14. Hello!

    I want to write a macro that assumes that the user is already logged in to SAP. I’m able to detect the connection with “Appl.Children(0)” but when I try to detect the session inside the connection, I get an error, and the array obtained with “Connection.Children.Count” is 0. I’ve tried to open from scratch a new LogOn window with Appl.OpenConnection()” and that does work, the session is stored in “Connection.Children(0)”. What can be happening?

  15. I’m really happy for this post.

    Now i can use VBA Scripting with SAP.

    You are the only one with clearly information.

    Thanks man.

      1. Hi Tomasz, Kindly ask you to help me with bellow issues. On the end of script I would like to activate SAP windows with tc zmm4tr021 and go with new session.
        Thank you in advans.
        Mykola

        Sub OpenSessionFromLogon()
        Dim SapGui
        Dim Applic
        Dim connection
        Dim session
        Dim WSHShell
        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:02”)
        Loop
        Set WSHShell = Nothing
        Set SapGui = GetObject(“SAPGUI”)
        Set Applic = SapGui.GetScriptingEngine
        Set connection = Applic.OpenConnection(“‘ ******* – Automatic Logon”, True)
        Set session = connection.Children(0)

        Windows(“Data.xlsm”).Activate
        Sheets(“Start”).Select
        Columns(“C:C”).Select
        Selection.Copy
        Application.Wait Now + TimeValue(“0:00:01”)

        session.findById(“wnd[0]/tbar[0]/okcd”).Text = “zmm4tr021”
        session.findById(“wnd[0]”).sendVKey 0
        Application.Wait Now + TimeValue(“0:00:01”)
        session.findById(“wnd[0]/usr/ctxtP_VARI”).Text = “FOR_EEDC_INB”
        session.findById(“wnd[0]/usr/ctxtP_VARI”).SetFocus
        session.findById(“wnd[0]/usr/ctxtP_VARI”).caretPosition = 12
        session.findById(“wnd[0]/usr/btn%_S_SHPTAG_%_APP_%-VALU_PUSH”).press
        session.findById(“wnd[1]/tbar[0]/btn[24]”).press
        session.findById(“wnd[1]/tbar[0]/btn[8]”).press
        session.findById(“wnd[0]/tbar[1]/btn[8]”).press
        session.findById(“wnd[0]/mbar/menu[0]/menu[3]/menu[1]”).Select
        session.findById(“wnd[1]/tbar[0]/btn[11]”).press

        Application.OnTime Now + TimeValue(“00:00:7”), “SAP_3LTS”
        Set session = Nothing

        Windows(“Data.xlsm”).Activate
        Sheets(“zmm4tr021”).Select
        With Sheets(“zmm4tr021”).ListObjects(“Table3”)

        ‘Check If any data exists in the table
        If Not .DataBodyRange Is Nothing Then
        ‘Clear Content from the table
        .DataBodyRange.ClearContents
        Application.Wait Now + TimeValue(“0:00:01”)
        End If

        End With

        Windows(“export.xlsx”).Activate
        Sheets(“Sheet1”).Select
        Application.Wait Now + TimeValue(“0:00:01”)
        Application.DisplayAlerts = False
        Cells.Select
        Selection.Copy
        Workbooks(“export.xlsx”).Close SaveChanges:=False
        Application.Wait Now + TimeValue(“0:00:01”)

        Windows(“Data.xlsm”).Activate
        Sheets(“zmm4tr021”).Select
        Range(“A1”).Select
        ActiveSheet.Paste
        Application.Wait Now + TimeValue(“0:00:01”)

        Windows(“Data.xlsm”).Activate
        Sheets(“zmm4tr021”).Select
        Columns(“L:L”).Select
        Selection.Copy
        Application.Wait Now + TimeValue(“0:00:01”)

        ‘How to activate SAP windows again?

        End Sub

        1. Hello Mykola
          First of all, avoid using .Select & .Activate. It’s making your code easy to fail.
          Secondly, You can activate SAP window again the same way You ‘activated’ it for the first time 🙂
          But for the future, comment section is not the place for the whole code module.

  16. Your code is great. Works perfectly. One little question: how can i can close the Logon window/app after the application/transaction window has opened?

    1. Thanks.
      Try to “find” this window and close/quit/kill it. Helpful tip for finding any window is function FindWindowEx Lib “user32” 🙂

    1. It’s something that You need to know, what connection You or your company is using 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *