Problem with SAP logon by VBA

[Former Member]

Hi, I'm new in the community and I'm also new with SAP, so sorry for any errors.

For my work I need to open SAP by excel (VBA); and I have found the code below but it don't work.

Sub Work()

Set app = CreateObject("Sapgui.ScriptingCtrl.1")

Set Connection = app.openconnection("my system", True)

Set session = Connection.Children(0)

session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "Client"

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 = "Language"

Session.findById("wnd[0]").sendVKey 0

End Sub

When the macro runs there is this error  

Can you help me?

Thank you,

ax_salerno

[former_member432043]

I agree with Holger.  I do not understand the need to connect to a restricted use application.  Why not connect to SAP GUI or shortcut specific to your required transaction in SAP GUI?

You're setting the scripting engine connection prior to logging in to SAP?  I've not any luck with that... i tackle logon altogether differently.  As an example, the below is a snipped of a script that i use on a monthly basis that is executed automatically via Task Scheduler to perform a specific transactional task to advance the material master posting period based on stored information in a text file, then advance the posting period by 1 period in the text file and save for the next month's usage.  Blah blah... regardless of what my script does, i start an SAP connection and log in utilizing sendkeys, then i engage the scripting engine

Dim objShell

Dim Application

Set objShell = WScript.CreateObject( "WScript.Shell" )

objShell.Run("""C:\Users\p00137\Documents\SCRIPTS\RUN MONTHLY\Close Period for Material Master Rec.SAP""")

WScript.Sleep (1000)

objShell.SendKeys "PASSWORD"

objShell.SendKeys "{TAB}"

objShell.SendKeys "{ENTER}"

WScript.Sleep (1000)

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

Do SAP stuff here.

[holger_khn]

Hello.

I have tried your code and it´s working for me (with required adjustment to our System landscape).

However, this type of session Connection use an UI with restricted views and Options.

So I recommend to adopt to existing sessions created via SAP GUI application. As they will give same Option as you have via SAP GUI.

Br, Holger

[Former Member]

Hi Alessandro - I am sharing an Excel sub that works for me by opening up logon pad, signing on to production, gettting credentials, then going into SAP and doing a transaction, and finally asking the user to click ok to terminate the session. I think this is something that there were questions on how to do the session close nicely but not sure if there is a solution yet.

Hope this helps.

Regards

Umur

------

Sub Logon()

'starts by executing logon pad, selecting prod, then enters
id/pwd

'works to sign on to sap and then kill the connection.
refers to saplogon.exe in Program Files/SAP71/FrontEnd/SAPGui folder

Dim SAPguiAPP As Object

Dim oConnection As Object

Dim Session As Object

Dim SAPCon As Object, SAPSesi As Object

Dim SapGuiAuto As Object, SAPApp As Object

If SAPguiAPP Is Nothing Then

    Set SAPguiAPP =
CreateObject("Sapgui.ScriptingCtrl.1")

End If

If oConnection Is Nothing Then

    Set oConnection =
SAPguiAPP.OpenConnection("1. Prod", True)

End If

If SAPSesi Is Nothing Then

   Set SAPSesi =
oConnection.Children(0)

End If

  
Application.DisplayAlerts = False

With SAPSesi    

    .FindById("wnd[0]/usr/txtRSYST-MANDT").Text
= "010"

  
.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = "USER"

  
.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text =
"PASSW"

  
'.FindById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

  
.FindById("wnd[0]").SendVKey 0

    'start extraction

  
.FindById("wnd[0]").Maximize

  
.FindById("wnd[0]/tbar[0]/okcd").Text = "/Niw33"

  
.FindById("wnd[0]").SendVKey 0

  
.FindById("wnd[0]").Maximize

  
.FindById("wnd[0]/usr/ctxtCAUFVD-AUFNR").Text =
"8762449"

    .FindById("wnd[0]/usr/ctxtCAUFVD-AUFNR").CaretPosition
= 7

  
.FindById("wnd[0]/tbar[1]/btn[8]").Press

  
.FindById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1107/tabsTS_1100/tabpKOAU/ssubSUB_AUFTRAG:SAPLICO1:1100/tabsTABSTRIP/tabpTS05/ssubVALUES:SAPLICO1:1105/cntlPMCO_SUM_TREE/shellcont/shell/shellcont[1]/shell").HierarchyHeaderWidth
= 162

  
.FindById("wnd[0]/usr/subSUB_ALL:SAPLCOIH:3001/ssubSUB_LEVEL:SAPLCOIH:1107/tabsTS_1100/tabpKOAU/ssubSUB_AUFTRAG:SAPLICO1:1100/btnPUSH1").Press

    .FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").SelectedRows
= "0"

  
.FindById("wnd[0]/usr/cntlGRID1/shellcont/shell/shellcont[1]/shell").DoubleClickCurrentCell

  
.FindById("wnd[0]/tbar[0]/btn[3]").Press

  
.FindById("wnd[1]/usr/btnSPOP-OPTION1").Press

  
.FindById("wnd[0]/tbar[0]/btn[3]").Press

.FindById("wnd[0]/tbar[0]/btn[3]").Press
.FindById("wnd[0]/tbar[0]/btn[3]").Press

    '...

    'etc

    '...

End With

     MsgBox
"After clicking OK, this SAP session is terminated."

   
Application.DisplayAlerts = True

     Set Session =
Nothing

    Set Connection =
Nothing

    Set SAPguiAPP =
Nothing

End Sub

[Former Member]

Hi Humur,

thank you for your help.

I hve tried your code but I have the same problem:

I don't know if I need to change some Excel or SAP setting, can You help me?

Thank you so much,

Ale

[Former Member]

Hi Ale - All I can think of for Excel setting is this:

Do you have this set up.. see the linkage to sapfewse.ocx at the bottom...

Other than this, I am not sure why the same code would not work for you...maybe someone else has an idea...

Regards

Umur