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

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

124 thoughts on “Connect to SAP via Excel VBA”

      1. Thanks for this code. it really solved the problem.
        But I need to click when it attaches to the session ( saying “yes”) and again when script starts.

        Could you suggest to login without clicking “yes” anywhere?

          1. you are the man Tomasz !
            Thanks a lot. though it was tiny issue but indeed restricting the full automation.
            Thanks again.
            cheers.

          2. how to get no of rows in any grid and get is displayed.
            I need to loop a code repeating no of rows times.

            help ?

  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. Not really, but it depends on your code and which line the error appears.

      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. I can open until the login page, then connection.children error

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

    2. Hi Wiseley,
      I know it is late response, but in case that anybody will have the same issue in the future on this awesome page…
      You do not have enabled scripting in SAP – RZ11 -> sapgui/user_scripting
      I have just faced and fixed it by enabling, code then works like a charm.

  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. Great work on the tutorial. I had the same question about closing the SAP Logon. Using WM_CLOSE only minimizes the Logon if there are sessions running. How do I close it for good (manually I just click the X at the top-right), while continuing to work in my session?

        PS, I could not find a kill command and tried WM_DESTROY, but that was a bad idea. It appears to have broken my GUI, but I can still logon using your script.

          1. Thanks for your response! Your referenced article is a good one, but sadly it only refers to commands within a session. As far as I can see, closing the SAP logon, while keeping the session open, requires a different type of code. As I mentioned, WM_CLOSE appears to be on the right path, but it only works if I have closed my session, which I do not want to do. Here is the code I use for this (it also works for closing other external applications, but only with SAP Logon do I have issues with it just minimizing the window when a session is open):

            Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
            Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
            Private Const WM_CLOSE = &H10
            Sub CloseSAPLogon()
            'Only works when no SAP session is running
            Dim SAP_Logon_window As Long
            SAP_Logon_window = FindWindow(vbNullString, "SAP Logon 760")
            Debug.Print SAP_Logon_window 'makes sure the window has been found

            Call SendMessage(SAP_Logon_window, WM_CLOSE, CLng(0), CLng(0))
            End Sub

            Any chance you know how to fix this?

          2. Unfortunately I can’t get a point of the whole issue, I think I can’t help You, I’m sorry.

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

  17. Hi,

    My code is not working after open the sap.. I am unable to connection with the correct name.

    Error message i am getting is (A script is attempting to access SAP GUI.

    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

    Dim conn1 As Object

    Set SapGui = GetObject(“SAPGUI”)
    Set Appl = SapGui.GetScriptingEngine
    Set Connection = Appl.Connection.Count(“P01”, _
    True)
    Set session = Connection.Children(0)

    Please help me with this.

    1. This is not an error, it is just message box from SAP.
      You need to change the settings in SAP to allow your scripts to access into SAP.

  18. Hi Tomasz,

    I was just browsing around looking for VBA memes when I stumbled here. Good job for sharing your knowledge.

    I wrote a class module for SAP in VBA a few years ago, and just completed a proper Excel add-in for SAP data downloading and uploading in my last project.

    Instead of declaring the SAP apps, connection and session as variable type ‘Object’, you can add a reference to sapfewse.ocx library in the SAPGUI folder and declare them as their proper object type e.g. GuiConnection, GuiSession etc. This way, you can take advantage on SAP scripting API to handle SAP objects model.

    Putting the codes in a class module instead of a normal module gives you access to SAP objects’ events. You can use them to identify SAP fields IDs, if a session has been terminated etc.

    SAP scripting was an interesting experience for me.

    Have fun!

    1. Hi Sayuti!
      Thanks for this piece of information. I will try that for sure!

  19. Hello, thank you for this very helpfull code,

    would be possible to check if there is already an oppened session after to have connected manually (without these lines in the code :

    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”)

    the vba macro doesn’t continue to the “if” code after to have press “enter” manually..

    Thank you !!

    1. I think i’ve found the solution :

      Sub Export_articles_PRD()

      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 SapGui = GetObject(“SAPGUI”)
      Set Appl = SapGui.GetScriptingEngine
      Set Connection = Appl.Openconnection(“PRD-ECC-Production”, True)
      Set session = Connection.Children(0)

      session.findbyid(“wnd[0]/usr/txtRSYST-LANGU”).Text = “FR”

      Do Until WshShell.AppActivate(“SAP Easy Access”) Or WshShell.AppActivate(“Info licence en cas de connexion multiple”)
      Application.Wait Now + TimeValue(“0:00:01”)
      Loop

      If WshShell.AppActivate(“Info licence en cas de connexion multiple”) Then
      session.findbyid(“wnd[1]/usr/radMULTI_LOGON_OPT2”).Select
      session.findbyid(“wnd[1]/usr/radMULTI_LOGON_OPT2”).SetFocus
      session.findbyid(“wnd[1]/tbar[0]/btn[0]”).press

      End If

      Shell “wscript “”U:\9 Contrôle FM-SAP\SAP\Scripts export sap\Export_articles_PRD.vbs”””

      Dim t As Single
      t = Timer
      Do
      DoEvents
      Loop Until (Timer – t) > 60 Or Workbooks(Workbooks.Count).Name = “03-Export articles PRD.xlsx”

      Workbooks(“03-Export articles PRD.xlsx”).Close
      ThisWorkbook.Activate
      ‘ThisWorkbook.RefreshAll

      but now, the sap script is always executed on the first session instead of the latest opened session. How can i select the latest session to execute the script ?

      Because if i have more than one session i choose option 2, continue with this session without closing the other. and from there I wish to execute the script on the lattest oppened session and not on an other one.. Thank you !!!

    2. session.findById("wnd[0]").sendVKey 0 stands for ENTER in SAP.
      You can find it in the article 🙂

  20. Script works fine until the step that username & password needed to be input. The script can input username & password automatically? or needs to input manually?

    1. Hello
      According to the code from artictle You need to replace “user” and “password” with your own username and password. Hard code that or make a reference to the range value for example.

  21. Hi Tomasz,

    Firstly many thanks for your code. This will help and benefit many of us who are new to this. Really appreciate your work.
    I just have on question: When I am executing by vba I am getting an error at line “Set session = Connection.Children(0)” which says “Run-time error ‘1000’: Application-defined or object-defined error” . I am not sure how to rectify this.

    Can you please help me with this.

    Thanks,
    Ashish

    1. Hello Ashish
      For now I got 2 ideas in my head: 1. did You declared session object? 2. Did You typed correctly the name of the connection line above?

  22. Hi,
    I have to log on SAP kernel wise, like MBP, WPP, MP1 and so on…, i have to put kernel first and then execute, then user id and password,
    what coding should be there in Macro for kernel execution first.

    Thanks
    Sadik

    1. Hello Sadik,
      is that SAP kernel wise a connection name?
      If yes everything is described in the article, if not tell me more what is the difference between your situation and the one from the article.

  23. Hi Tomasz,

    thanks a lot for the code. I will try it the next days.

    But I have a question in advance. I can open SAP with code, run T-Codes and export with my own code. But at the end I need an VBA-code that closes SAP. Do you have something for?

    At the moment I’m working with a VB-Script which opens SAP, gives control to my VBA-Code and at the end the VB-Script closes SAP.
    Unfortunately something changed and my code doesn’t work properly any more.
    And to be honest, it’s not a really comfortable way…

    Best regards,
    Lutz

      1. Hi Tomasz,

        I’ve read your other articles, but missed the last line in “SAP session.findbyid – 10 code lines you can identify”. Shame on me.

        I had to change the code a little bit:
        session.findById(“wnd[0]”).Close
        session.findById(“wnd[1]/usr/btnSPOP-OPTION1”).press
        Your code ends in a runtime error 619: “The control could not find by id.”

        How can you see the code to close SAP? I started scripting in SAP closed it by clicking the cross in the top right corner, got the message, clicked ok. But no .vbs file in my folder (e.g. Desktop).

        Many thanks for your help,
        Lutz

        1. Hi Lutz,
          Try to record exiting the SAP, but click “No” button.
          I checked recording with confirming the exit – there is no .vbs file after recording, but SAP is creating SAPSCRTEMP.TXT
          And there is recorded “Yes” button 🙂

          1. Hi Tomasz,
            where do I find the SAPSCRTEMP.TXT?

            I have a kind of Off-Topic-Question and I hope you as an expert can help.
            I have a VBA that exports a xlsx.-file with a unique name out of T-Code LT22. This file should be processed by the same VBA-code. For this I set the export-file to an object variable:
            Set WkbTransOrders = GetObject(StrPfadDaten & “\” & StrTransOrders)

            As long as the export just takes a short moment it works fine. But in case the file is bigger (approx. 600 lines with 14 columns) it takes longer to open the file. Than the GetObject opens the existing file and when the actually exported file comes up, a message appears that the file is already open.
            I tried to let Excel wait for 10 seconds:
            Application.Wait (Now + TimeValue(“0:00:10”))
            But obviously this makes all processes wait and the exported file comes up 10 seconds later. So no improvement.
            And another problem of the GetObject is, that at the end of the VBA-code there is the file as a hidden one left.
            Do you know how to solve this problem?

            Thanks for your help,
            Lutz

          2. Hi Lutz,
            This file is created in the same location as .vbs scripts.

            To your off topic question – I’m just trying to figure out why are You using GetObject to open .xlsx file.
            But please, if You got offtopic questions lets not spam articles – contact me via Contact me or About me page 🙂

  24. Hi Tomaz,
    I hope you can really help me. I did exactly what is explained in your screenshots; however i am getting an error on this line “Set Connection = Appl.Openconnection(“xyz abc def”, _
    True)
    Run-time error ‘462’:
    The remote server machine does not exist or is unavailable

    1. Hello Pranav
      Did You check for any typos in the connection name? Is this exactly the same as the connection in SAP?
      Do You have manual access to that connection?

  25. Hello Mr. Plocinski
    Thank you so much for this!
    It has worked just fine for me before our company upgraded to SAP Hana last week.
    Now we have a different way of opening SAP -> powershell.
    Now the script crashes at the second last line and dosent open the session 🙁 Do you maybe have any ideas?

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

    Call CreateObject(“Shell.Application”).ShellExecute(“C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Grundfos Basic\SAP HANA Logon SSO.lnk”, 4)
    Set WshShell = CreateObject(“WScript.Shell”)

    Do Until WshShell.AppActivate(“SAP Logon “)
    Application.Wait Now + TimeValue(“0:00:05”)
    Loop

    Set WshShell = Nothing

    Set SapGui = GetObject(“SAPGUI”)
    Set Appl = SapGui.GetScriptingEngine
    Set Connection = Appl.Openconnection(“P20 : S/4 HANA [GRUNDFOS-Users] SSO”, True)
    Set session = Connection.Children(0)

  26. Hello,
    I have the following code and it manages to connect with the production module but fails to connect with the testing module. I cannot find the issue:

    Sub Test()
    Dim companyCode As String

    companyCode = Cells(3, 1)

    Application.ScreenUpdating = False
    ‘Get the SAP GUI Scripting object
    Set SapGuiAuto = GetObject(“SAPGUI”)
    ‘Get the currently running SAP GUI
    Set SAPApp = SapGuiAuto.GetScriptingEngine
    ‘Get the first system that is currently connected
    Set SAPCon = SAPApp.Children(0)
    ‘Get the first session (window) on that connection
    Set session = SAPCon.Children(0) ‘->>>> here it gives me error 614

    session.findById(“wnd[0]”).maximize
    session.findById(“wnd[0]/tbar[0]/okcd”).Text = “FBL3N”
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/usr/ctxtSD_SAKNR-LOW”).Text = “80200”
    session.findById(“wnd[0]/usr/ctxtSD_BUKRS-LOW”).Text = “0412”
    session.findById(“wnd[0]/usr/ctxtPA_VARI”).Text = “/PENSION”
    ‘session.findById(“wnd[0]/usr/ctxtPA_VARI”).SetFocus
    ‘session.findById(“wnd[0]/usr/ctxtPA_VARI”).caretPosition = 8
    session.findById(“wnd[0]/tbar[1]/btn[8]”).press

    ‘Export to computer
    session.findById(“wnd[0]”).maximize
    session.findById(“wnd[0]/mbar/menu[0]/menu[3]/menu[1]”).Select
    session.findById(“wnd[1]/tbar[0]/btn[0]”).press
    session.findById(“wnd[1]/usr/ctxtDY_FILENAME”).Text = “FirstTest.xlsx” ‘the name of the excel file
    session.findById(“wnd[1]/usr/ctxtDY_PATH”).Text = ” C:\Users\karaje\Ascripts ” ‘the path where the folder is going to be saved
    session.findById(“wnd[1]/tbar[0]/btn[11]”).press ‘Replace button, if you want to just download it replcae 11 with 0
    Set session = Nothing

    MsgBox “Download is now completed”
    Application.ScreenUpdating = True

    End Sub

    1. Hello Enxhi
      What is the error saying?
      Have You tried other numbers than “0”?
      If You would try the actual code from the article You would be able to pass the exact name of the connection, which using this approach – from the script recorder – does not allow You to.

  27. Dear Tomasz

    It’s a great helpul to me
    Thank you so much.

    But I have one question.
    On progress, some popup message come up.
    How can I automatically pass that popup message.
    Now I have to click “OK” manully in each.

    The two popup messages are

    A script is attempting to access SAP GUI

    A script is opening a connection to system: Colbat – Z2L(Prod)Link

  28. Dear Tomasz

    I have one more question.

    I made one macro
    It is Logon SAP -> Enter T-code -> Fill in the a few blank -> SAVE.
    It is working thanks for your reference

    But if already open SAP there is some error.
    How can I hanle this.
    I want proceed to complete even SAP opened.

    Below is about that code.

    If you don’t mind could you check it for me?
    and please let me know which code do I have to modify?
    ———————————
    Sub Sap_work()

    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(“Cobalt – Z2L (Prod) Link”, _
    True)
    Set session = Connection.Children(0)

    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]”).sendVKey 0 ‘ENTER

    session.findById(“wnd[0]”).resizeWorkingPane 147, 29, False
    session.findById(“wnd[0]/tbar[0]/okcd”).Text = “/nmb52”
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/usr/ctxtWERKS-LOW”).Text = “KR5W”
    session.findById(“wnd[0]/usr/ctxtLGORT-LOW”).Text = “34w1”
    session.findById(“wnd[0]/usr/ctxtP_VARI”).Text = “Apple”
    session.findById(“wnd[0]/usr/ctxtP_VARI”).SetFocus
    session.findById(“wnd[0]/usr/ctxtP_VARI”).caretPosition = 6
    session.findById(“wnd[0]”).sendVKey 0
    session.findById(“wnd[0]/tbar[1]/btn[8]”).press
    session.findById(“wnd[0]/tbar[1]/btn[43]”).press
    session.findById(“wnd[1]/usr/ctxtDY_PATH”).SetFocus
    session.findById(“wnd[1]/usr/ctxtDY_PATH”).caretPosition = 0
    session.findById(“wnd[1]”).sendVKey 4
    session.findById(“wnd[2]”).Close
    session.findById(“wnd[1]”).sendVKey 4
    session.findById(“wnd[2]”).Close
    session.findById(“wnd[1]/tbar[0]/btn[11]”).press

    End Sub

    1. Hello Young
      First of all if You got this kind of question, please contact me directly not to spam comment section.
      What kind of error? It is really important information 🙂

  29. Hallo Thomasz,
    I am looking for some examples on how to use the “GuiOfficeIntegration Object”.

    Background: Once an embedded pop-up window with MS Word starts, the content can be edited there directly. I would like to prepare the content upfront and deploy it in an automated way.

    Using the script recorder, I have figured out that “setDocument 1, XXX” gets recorded with XXX being a base64 encoded RTF document.

    I now try to prepare a RTF document (as easy as writing the header and footer as “text” and adding the content in-between) with content from Excel cells. After encoding the document as base64, I try to perform the very same setDocument command.
    But this one fails for whatever reason…

    Do you have any experience? Thanks!

    1. Hello Andrej
      I’d like to help You, but unfortunately I don’t have such experience.

  30. Thank you so much for this code. It worked perfectly.
    But is it possible to run it even if the sap session is open?.
    Like instead of giving the error message and exiting sub, I want to run the same sap recorded codes in existing screen.

    Please help me if u have a solution..
    Thanks

    1. You can try to check the amount of sessions before connecting to any, someting like this Appl.Connections.Count
      If already bigger than 0 it means that it is connected, so You can use this connection in further code 🙂

  31. Hi Tomasz, Thank you for the amazing code. However, I’m facing with module name. I have two system BH9 And RP1 . I need to loginto RP1. I am always receiving an error SAP logon connection entry not found. Please help me out. If we can connect it would be help me out alot . I have a daily task of exporting files from SAP in Excel formate I need to auto mate the entire process and learn the VBA coding for it. Thanks in advance.

  32. Hi Tomasz!

    I have multiple SAP IDs, so I would like not to choose one from three every time. How could I implement this step into the code? This part of the code doesn’t work for my case:

    session.findById(“wnd[0]/usr/txtRSYST-BNAME”).Text = “user”

    Would appreciate your help.

    1. Hi Kate
      Have You replaced ‘user’ with your actual SAP username?
      If yes and it still doesn’t work maybe there is issue with SAP id (in brackets) – try to record the login part and see what the script recorder show.