Why does Excel hang/slow when it gets to "Set SAPguiAPP = Nothing"?

[Former Member]

I am preparing to develop a number of Excel programs to automate various SAP processes in our manufacturing area. (For example Tcodes: MFBF, CO11, MB11, MB31). After reviewing many of the posts in this group and others via Google I have developed the test program (Logontrial) you see below. It logs onto SAP and executes Tcode: MB31, it then brings up Excel with a msgbox. If you hit enter it exits out of SAP by clearing all the object variables I used to make the SAP connection. The problem I am having rests on the line in red "Set SAPguiAPP = Nothing". It takes many seconds to complete this line of code. Every time I run the code it takes a bit longer to complete. To test this, step through the code. Any suggestions to solve this problem will be greatly appreciated.

Option Explicit
Public SAPguiAPP As SAPFEWSELib.GuiApplication 'Set reference to SAP GUI Scripting API "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\sapfewse.ocx"
Public Connection As SAPFEWSELib.GuiConnection
Public MySession As SAPFEWSELib.GuiSession
Sub Logontrial()
    Dim MyPassword, MyUserName
    MyUserName = "XXXXXX" 'InputBox("Enter your SAP user name.")
    MyPassword = "XXXXXXXX." 'InputBoxDK("Enter your SAP password.", "SAP Password")
    If IsObject(SAPguiAPP) Then
        Set SAPguiAPP = CreateObject("Sapgui.ScriptingCtrl.1")
    End If
    'SAPguiAPP.Visible = True
    If IsObject(Connection) Then
        'Set Connection = SAPguiAPP.OpenConnection("Bethyl ERP 6.0 Development", True)
        Set Connection = SAPguiAPP.OpenConnectionByConnectionString("xxx.xxx.xxx.xxx 10")
    End If
    If IsObject(MySession) Then
        Set MySession = Connection.Children(0)
    End If
    MySession.FindById("wnd[0]/usr/txtRSYST-MANDT").Text = "X00"
    MySession.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = MyUserName
    MySession.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text = MyPassword
    MySession.FindById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
    MySession.FindById("wnd[0]/usr/txtRSYST-LANGU").SetFocus
    MySession.FindById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2
    MySession.FindById("wnd[0]").SendVKey 0
    MySession.FindById("wnd[0]/tbar[0]/okcd").Text = "/nMB31"
    MySession.FindById("wnd[0]").SendVKey 0
    MySession.FindById("wnd[0]").Maximize
    AppActivate ("Microsoft Excel")
    MsgBox ("Hit enter to Close SAP.")
    Connection.CloseSession (MySession.ID)
    DoEvents
    Set MySession = Nothing
    Set Connection = Nothing
    Set SAPguiAPP = Nothing
    DoEvents
End Sub

[Former Member · Accepted solution]

I figured it out. It is not necessary to clear the variables when you use "Connection.CloseSession (Session.ID)"

See code below:

Sub Logontrial()
    Dim MyPassword, MyUserName
    MyUserName = "xxx" 'InputBox("Enter your SAP user name.")
    MyPassword = "xxxx." 'InputBoxDK("Enter your SAP password.", "SAP Password")
    If IsObject(SAPguiAPP) Then
        Set SAPguiAPP = CreateObject("Sapgui.ScriptingCtrl.1")
    End If
    If IsObject(Connection) Then
        'Set Connection = SAPguiAPP.OpenConnection("Bethyl ERP 6.0 Development", True)
        Set Connection = SAPguiAPP.OpenConnectionByConnectionString("xx2.1x8.xxx.x2 10")
    End If
    If IsObject(Session) Then
        Set Session = Connection.Children(0)
    End If
    Session.FindById("wnd[0]/usr/txtRSYST-MANDT").Text = "x00"
    Session.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = MyUserName
    Session.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text = MyPassword
    Session.FindById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"
    Session.FindById("wnd[0]/usr/txtRSYST-LANGU").SetFocus
    Session.FindById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2
    Session.FindById("wnd[0]").SendVKey 0
    Session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nSM04"
    Session.FindById("wnd[0]").SendVKey 0
    Session.FindById("wnd[0]").Maximize
    AppActivate ("Microsoft Excel")
    MsgBox ("Hit enter to Close SAP.")
    Connection.CloseSession (Session.ID)
    DoEvents
End Sub