Issue with Excel SAP VBA scripting

[Former Member]

I have managed to run a few macros from VBA.

These mainly extract data to be used to generate some KPI's etc,

Sample code

If SapGuiApp Is Nothing Then

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

End If

If oConnection Is Nothing Then

    Set oConnection = SapGuiApp.OpenConnection("DataConnection]", True)

End If

If SAPSesi Is Nothing Then

   Set SAPSesi = oConnection.Children(0)

End If

The problem I am having is the following

When running the following command it comes up as can not find the item

.findById("wnd[0]/mbar/menu[0]/menu[10]/menu[2]").Select

This was recorded by SAP.

The action is to select List -> Save -> File via the menu bar.

When I add watch to excel SAP the SAPSesi I can drill down the children for each level and track through the wnd[0]/mbar/menu[0]

When I inspect the children there are no children items listed. The count is 0 so there is nothing to select.

Anyone had this problem before or found another way to call the List -> Save -> File

thanks

[script_man · Accepted solution]

Hi John,

one could use for example instead the above command the following command:

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

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi ScriptMan

Thanks for the information

The sendVkey 45 is linked to Ctrl+Shift+F9 which for say for simple transaction IH06 is not linked to list->save->file function.

This for instance seems to be linked to Equipment List.

Is there any reason that I am unable to see the submenu items in the session?

Any other ideas?

Regards

John

[script_man · Accepted solution]

Hi John,

I have no other ideas but a question. Can you run the following script in the SAP GUI?

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 = "/nih06"

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

'for example

session.findById("wnd[0]/usr/ctxtSTRNO-LOW").text = "2000-03105*"

session.findById("wnd[0]/tbar[1]/btn[8]").press

session.findById("wnd[0]/mbar/menu[0]/menu[10]/menu[2]").select

'for example

session.findById("wnd[1]/tbar[0]/btn[0]").press

session.findById("wnd[1]/usr/ctxtDY_PATH").text = "z:\"

session.findById("wnd[1]/usr/ctxtDY_FILENAME").text = "test.txt"

session.findById("wnd[1]/tbar[0]/btn[11]").press

When I record the script, the critical command is as follows:

session.findById("wnd[0]/mbar/menu[0]/menu[9]/menu[2]").select

I experienced no errors.

Regards,

ScriptMan

[Former Member · Accepted solution]

Yes that is the same command that I get from recording inside SAPGUI.

Yet when I attached from excel VBA I am unable to find this child

The best I can find is wnd[0]/mbar/menu[0] which is the List Level.

When I examine the children items to that item it shows Count of Item = 0

this causes the findbyID to fail in the excel VBA code.

Regards

John

[script_man · Accepted solution]

If you can run your script outside VBA without problems, try the following:

VBA-Code:

Sub Test()

Set wshell = CreateObject("WScript.Shell")

wshell.Run """c:\tmp\myScript.vbs""", 1, True

End Sub

myScript.vbs:

If SapGuiApp Is Nothing Then

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

End If

If oConnection Is Nothing Then

    Set oConnection = SapGuiApp.OpenConnection("DataConnection]", True)

End If

If SAPSesi Is Nothing Then

   Set SAPSesi = oConnection.Children(0)

End If

. . .

.findById("wnd[0]/mbar/menu[0]/menu[10]/menu[2]").Select

. . .

Regards,

ScriptMan

[Former Member · Accepted solution]

There are days I really hate SAP.

Found the solution to my problems,

When using the Sapgui.ScriptingCtrl.1 from Excel on a Low speed Connection the following occurs

There are no names to the fields sent to the scripting window. and the menu structure is not available to Excel VBA connection.

When switching the connection to High Speed Lan Connection setting the fields now have names i.e. ctxtVARIANT and the full menu structure is available in Excel VBA connection.

So there was nothing wrong with my code as such. Just one of those design features in SAP.

So recoding all the scripts to High Speed string names as the old txt[0] no longer work.

so my next question is there a way to do the following

Is there a function call that when searching for a particular wnd[0]/usr/ctxtVARIANT that does not raise exception error when the field is not found.

I want to be able to do something like.

If wnd[0]/usr/ctxtVARIANT doesn't exist then Msgbox (You need to switch to High Speed Connection)

Regards

John

[script_man · Accepted solution]

Unfortunately I don't know the required function. I would do it as follows:

. . .

on error resume next

session.findById("wnd[0]/mbar/menu[0]/menu[10]/menu[2]").Select

if not err.number = 0 then

   Msgbox "You need to switch to High Speed Connection"

   wscript.quit

end if

on error goto 0

. . .

Regards,

ScriptMan