Excel VBA SAP logon pad

[Former Member]

Hello,

I have successfully written VBA code to emulate user interaction with SAP on one "system".  However when i want to switch over to another SAP system i have been unsuccessful.  I may be using the wrong terminology referring to the systems, for example, on the SAP logon pad 710 i have a list of 56 system's i can log in to.  I only use two of those systems, one is called SAP ECC production [BH1] and the other is called SAP SCM 5.0 Production [BA1].  in the middle of my code i would like to switch over to the second system (SAP SCM 5.0) but i have not been able to do it.  Any help would be tremendous!  Thank you

[former_member213011 · Accepted solution]

Hi Jason,

Basically, you need to change the sap connection to the other system using either OpenConnection or OpenConnectionByConnectionString method on the GuiApplication object.

Can you share the codes that you have and indicate at which point do you want to change?

Thanks,

Sayuti

[Former Member · Accepted solution]

well, i guess my question should be:  How can i identify all the open SAP sessions through VBA?

[former_member213011 · Accepted solution]

You need to identify the open connections, and the sessions for each connection.

Here's a quick VBA script which loops through all connections, then loops through all sessions for that connection and list down the server name, the session number on that connection and the current running transaction.

Sub listSAPSessions()

    Dim sap As Object

    Dim sapGui As Object

    Dim sapCon As Object

    Dim sapSession As Object

    Dim i As Integer

    Dim j As Integer

    ActiveSheet.Range("A2").Select

    Set sap = GetObject("sapgui")

    Set sapGui = sap.getscriptingEngine

    'loop through open connection

    For i = 0 To sapGui.Connections.Count - 1

        Set sapCon = sapGui.Children(CLng(i))

        'loop through sessions on the connection

        For j = 0 To sapCon.Sessions.Count - 1

            Set sapSession = sapCon.Children(CLng(j))

            'list down the info

            With sapSession.info

                ActiveCell = .systemname

                ActiveCell.Offset(0, 1) = .sessionnumber

                ActiveCell.Offset(0, 2) = .transaction

                ActiveCell.Offset(1, 0).Select

            End With

        Next j

    Next i

    Set sapSession = Nothing

    Set sapCon = Nothing

    Set sapGui = Nothing

    Set sap = Nothing

End Sub

Assuming the object sapCon is not declared and set, if you connect to Server1 first, the connection will become sapGui.Children(0) and the 1st session on that connection will become sapGui.Children(0).Children(0), 2nd session becomes sapGui.Children(0).Children(1) and so on.. Connection to Server2 will become sapGui.Children(1) and the 1st session will become sapGui.Children(1).Children(0), second session becomes sapGui.Children(1).Children(1) and so on...

Hope this helps.

[Former Member · Accepted solution]

yes that helps!  thank you. 

[Former Member · Accepted solution]

Can i ask a really basic question:

in the following code i sometimes get an error that reads, "unable to find control by id" on this line

session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").Text = PoNum

I am theorizing that the id is being called out in between the paranthesis however i dont really understand what anything in between the paranthesis is, would you be able to take a few moments to explain what that is?     

Sub change_po_date()

Dim App, Connection, session As Object

Set SapGuiAuto = GetObject("SAPGUI")

Set App = SapGuiAuto.getscriptingEngine

Set Connection = App.Children(0)

Set session = Connection.Children(0)

Dim PoNum As Variant

Dim PoDate As Date

    'session.findById("wnd[0]").resizeWorkingPane 133, 43, False

    session.findById("wnd[0]/tbar[0]/okcd").Text = "me22n"

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

For i = 2 To 131

    PoNum = Cells(i, 1).Value

    PoDate = Cells(i, 6).Value

        If Cells(i, 1).Value <> Cells(i, 1).Offset(-1, 0).Value Then

label1:

                    On Error GoTo ErrorHandler1

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

                    session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").Text = PoNum

                    session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").caretPosition = 10

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

                    session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB2:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1211/tblSAPLMEGUITC_1211/ctxtMEPO1211-EEIND[9,0]").Text = PoDate

                    session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0010/subSUB3:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1301/subSUB2:SAPLMEGUI:1303/tabsITEM_DETAIL/tabpTABIDT5/ssubTABSTRIPCONTROL1SUB:SAPLMEGUI:1320/tblSAPLMEGUITC_1320/ctxtMEPO1320-SLFDT[5,0]").Text = PoDate

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

[former_member213011 · Accepted solution]

The id is basically the 'address' of that particular control on SAP GUI much like a URL address. The "/" is the structure indicator.

In your case: "wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN":

"wnd(1)" indicates that this is a popup or a sub-window to the main session screen (which is "wnd(0)")

"usr" is usually the user input container on that window.

"subSUB0:SAPLMEGUI:0003" is probably the frame or tab in which the controls reside.

"ctxtMEPO_SELECT-EBELN" is the textbox where you input your PO number or some other text..

I would recommend that you try the Scripting Tracker Lite by herehttp://scn.sap.com/docs/DOC-32728 to understand the id structure. Go through the SAP Scripting API help file to learn more.

[Former Member · Accepted solution]

Thank you very much, your help is very valuable!

[former_member213011 · Accepted solution]

BTW, you probably get the error if the particular popup window did not come up 'on time' for the script after the button is pressed, or the popup did not come up for some other reason.

You can wait by putting a do while loop like this

Do while session.Children.Count = 1

     'do nothing

Loop

or set a condition to only execute the PO entry if the popup window exist

If session.Children.count > 1 then

     'your codes here

end if

Both ways above are a bit crude and risky so be wary.

[Former Member · Accepted solution]

Hello, is there a way to identify the control id in a field

there is a field that could either contain

session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON

or

session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/txtDV70A-STATUSICON

and depending on which it is i need SAP to respond accordingly.  How can i identify that?  Thank you!

[former_member213011 · Accepted solution]

Dear Jason,

To get the control id you just need to call the ID property e.g session.findbyid("wnd[0]/etc...").ID and it will return the text in the bracket.

Kind of weird when I think about it, how do you get into the control in the first place?

Thanks,

MSA

[Former Member · Accepted solution]

so something like

if session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3").id = "lblDV70A-STATUSICON" then do something...

[former_member213011 · Accepted solution]

Actually it will return the ID, i.e. "/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3".

Looking at your example, I'm assuming you want to check for the content of this GuiTable, which one way is to look for it's Children, e.g

session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3").Children(0).ID = "/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON"

However, I'm not sure whether the field is the 1st Children of that GuiTable so it will probably not work.

Another dirty way you can try:

Dim test as object

on error resume next

set test = session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON")

if not test is nothing then do something...

[Former Member · Accepted solution]

That ALMOST worked!!

i am doing this to step through each row in the table

for r = 0 to 15
     if session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3").Children(r).ID = something then do something
else
end if
next r

it doesnt like the Children(r)

i get a " bad index type for collection access" error...

how else can i step through each row?

[former_member213011 · Accepted solution]

Try Children(CLng(r)) and see whether it fixes the error.

[Former Member · Accepted solution]

Actually here is how i solved it:

            For r = 0 To 15
                If session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/txtNAST-DATVR[8," & r & "]").Text = "" Then
                    If session.findbyid("/app/con[0]/ses[0]/wnd[0]/usr/tblSAPDV70ATC_NAST3/txtNAST-UHRVR[9," & r & "]").Text <> "" Then
                        session.findbyid("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(r).Selected = True
                    Else
                    End If
                Else
                End If
          Next r