Excel Closes SAP at End of Sub

[Former Member]

Hi All, thank you for the help in advance.

I have written Excel macros that use VB to control SAP, and when they reach the end of the subroutine, SAP closes. Is there a way to prevent this from happening?

Thanks!

Alex

[holger_khn]

Hello.

Can you post your code to understand how you adopt SAP from Excel VBA?

[Former Member]

Yes, Here it is! (I have omitted some things with xxxxx)

Sub UploadToSap()

Dim strText As String
Dim xxxxxx As String
user = InputBox(Prompt:="SAP Username?", Title:="", Default:="")
pass = InputBox(Prompt:="SAP Password?", Title:="", Default:="")

'Get operator-inputted values here
xxxxx = Range("H3").Value

'Select SAP Server
Set Alex_SAP = CreateObject("SAPGUI.ScriptingCtrl.1")
Set Connection = Alex_SAP.OpenConnection("xxxxxx", True)
Set session = Connection.Children(0)
'Connect to SAP Server
session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "xxxxxxx"
session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = user
session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = pass
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]/tbar[0]/btn[0]").press

'Run SAP Macro Below
If Not IsObject(Alex_SAP) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set Alex_SAP = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = Alex_SAP.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 Alex_SAP, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "IH01"
session.findById("wnd[0]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/chkDY_BOMEX").Selected = True
session.findById("wnd[0]/usr/chkDY_LVORM").Selected = False
session.findById("wnd[0]/usr/ctxtDY_TPLNR").Text = "xxxxxx"
session.findById("wnd[0]/usr/chkDY_LVORM").SetFocus
session.findById("wnd[0]/tbar[1]/btn[8]").press

End Sub

[Former Member]

Excel also freezes after the macro finishes, and the SAP GUI looks weird and is missing graphics and buttons... Ideas on how to fix this as well?

[former_member213011]

Dear Alex,

I noticed you used the CreateObject("SAPGUI.ScriptingCtrl.1") method at the beginning of the script, which I believe causes all the issues you mentioned, including the missing graphics and buttons. This is based on my 'experiment' on this method which you can read here: along with discussions on the different methods to connect Excel to SAP using VBA.

My recommended method is to start saplogon.exe manually or by inserting shell command in your VBA codes and use the GetObject("sapgui") method instead.

Thanks,

Sayuti