Open SAP in Excel

[Former Member]

Dear all,

I am new at this forum. I am a general ledger employee at a leaflet distribution organisation in the Netherlands.

We work with SAP fico

I have a question about script recording in SAP.

My question is how can you  use the recorded script in vba excel

I've already tried myself but without result.

The following script i recorded myself:

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]/usr/txtRSYST-BNAME").text = "Soufian"

session.findById("wnd[0]/usr/pwdRSYST-BCODE").text = "  "

session.findById("wnd[0]/usr/pwdRSYST-BCODE").setFocus

session.findById("wnd[0]/usr/pwdRSYST-BCODE").caretPosition = 8

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

Then I tried using a script that I found on this forum:

http://scn.sap.com/message/10290802#10290802

If Not IsObject(SAPguiApp) Then

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

End If

If Not IsObject(Connection) Then

    Set Connection = SAPguiApp.OpenConnection("SYSTEM", True)

End If

If Not IsObject(Session) Then

    Set Session = Connection.Children(0)

End If

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

Session.findById("wnd[0]/usr/txtRSYST-LANGU").SetFocus

Session.findById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2

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

In Excel i got the following error message

Private Sub CommandButton1_Click()

If Not IsObject(SAPguiApp) Then

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

End If

If Not IsObject(Connection) Then

    Set Connection = SAPguiApp.OpenConnection("SYSTEM", True) according to the system the error leis intis line of the script.

End If

If Not IsObject(Session) Then

    Set Session = Connection.Children(0)

End If

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

Session.findById("wnd[0]/usr/txtRSYST-LANGU").SetFocus

Session.findById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2

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

End Sub

It is not clear to me what I am doing wrong.

Could somebody help me with this problem?

Soufian

[stefan_schnell]

Hello Soufian,

try this:

Private Sub Test()

  Dim SAPguiAPP As SAPFEWSELib.GuiApplication
  Dim Connection As SAPFEWSELib.GuiConnection
  Dim Session As SAPFEWSELib.GuiSession

  If SAPguiAPP Is Nothing Then
    Set SAPguiAPP = CreateObject("Sapgui.ScriptingCtrl.1")
  End If

  If Connection Is Nothing Then
    Set Connection = SAPguiAPP.OpenConnection("Description of connection")
  End If

  If Session Is Nothing Then
    Set Session = Connection.Children(0)
  End If

  Session.FindById("wnd[0]/usr/txtRSYST-MANDT").Text = "999"   'Client
  Session.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = "HUGO"  'User
  Session.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text = "BAMBI" 'Password
  Session.FindById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"    'Language
  Session.FindById("wnd[0]").SendVKey 0

End Sub

But you must bind before sapfewse.ocx in your VBA project via menu Extras and References.

You find sapfewse.ocx in the directory C:\Program Files\SAP\FrontEnd\SAPgui.

And with the method OpenConnection use as argument the description of the connection, not the SID or something else, really the description.

With this way you can control your SAP GUI via SAP GUI Scripting from Excel respectively VBA.

Hope it works, let us know.

Cheers

Stefan

[Former Member]

Hello Stefan,

With MS Excel 10 I am facing error " The enumerator of the collection cannot find

en element with the specific index"

while populating "Session" variable. Code line resulting error is given below...

If Session Is Nothing Then

    Set Session = Connection.Children(0)

End If

Can you please guide where I am commiting mistake ?

[stefan_schnell]

Hello K R Awasthi,
sure, I hope.

Under normal circumstances the example should be executed without any errors. The command SAPguiAPP.OpenConnection("Description of connection") opens a new connection with session 0.

You got an enumeration error, so I think you do not have open the connection or session 0. You can open up to six sessions for each connection. Look in the status bar of the session, e.g. NSP (1) 000. This is session 1 from 6, but the technical enumeration is from 0 to 5. Be sure that session (1) resp. 0 is open. Also be sure that the "Description of connection" is the same as in the SAP Logon description. "Description of connection" is only an example text. You should use the correct description, e.g "NSP Testsystem".

Please check it and let us know your result.

Cheers
Stefan

[Former Member]

Thanks Stefan for prompt response....

I made changes to "SAPguiAPP" population and problem got resolved. Now my code is working fine.. Please refer the changes i have made into the code...

  Dim SAPguiAPP As SAPFEWSELib.GuiApplication
  Dim Connection As SAPFEWSELib.GuiConnection
  Dim Session As SAPFEWSELib.GuiSession

  If SAPguiAPP Is Nothing Then
     Set SapGuiAuto = GetObject("SAPGUI")
     Set SAPguiAPP = SapGuiAuto.GetScriptingEngine
  End If

  If Connection Is Nothing Then
    Set Connection = SAPguiAPP.OpenConnection("XYZ [Description of connection ]")
  End If
  ses = 0

  If Session Is Nothing Then
    Set Session = Connection.Children(Int(ses))  ' And the problem is resolved at this line
  End If

  Session.FindById("wnd[0]/usr/txtRSYST-MANDT").Text = "321"   'Client

  Session.FindById("wnd[0]/usr/txtRSYST-BNAME").Text = "XXXXXXX"  'User

  Session.FindById("wnd[0]/usr/pwdRSYST-BCODE").Text = "XXXXXXX" 'Password

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

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

.......................

..............

[stefan_schnell]

Hello K R Awasthi,

thanks for your reply and your hint. The type casting of the session number is very interesting.

Cheers

Stefan

[Former Member]

Hi,

Is it possible to use the "SID" of the SAP system? not the "Description" ? This is in case different users have different "System Descriptions" in their respective machines but have the same "SID" for the specific SAP System.

Thanks!

[stefan_schnell]

Hello Bernhardt,

try the command OpenConnectionByConnectionString. The Connection string is a technical term and must be equal in your company environment.

Cheers

Stefan

[Former Member]

Hi Stefan,

Thanks for the input. I did some research and arrived to the conclusion to use OpenConnectionByConnectionString. However, i found out that it uses the Application/Message Server of the SAP System, hence it would not be possible to be directed to the correct SAP system in case it has different "Instance Numbers". Now, I am trying to find a way to open the saplogon.ini file to capture the "System Description" so i can use the OpenConnection command. By any chance, would there be any other easier/efficient/appropriate way to connect to the correct SAP system given this kind of flexibility?

Thanks!

[Former Member]

http://scn.sap.com/docs/DOC-33425

Maybe this is what you need

@Stefan: I use this approach to find a session. Thanks!

[Former Member]

Hi Gunter,

Thanks for this. I will try to incorporate this in my development as well. However, my goal is to recreate the SAP logon with only the SAP picklist open (no active sessions yet). That way, the user will be able to freely open a SAP system that is included in his/her picklist.

Thanks,

Andrew