VBA and SAP

[kiasor]

Hi all,

Although i only recently joined as a member, this forum has been invaluable and often popped up when searching the internet for solutions to problems i have had in the past.

Thankyou to the community for all your hard work.

I have been working on macro's now between excel and SAP for a while, automated scripting between the two to build databases automatically but I am still a beginner and have been learning as I go.

My current problem is that I want to use the data in a cell in excel to fill a box in SAP. I have worked out how to do it the other way around e.g.

Cells(9, 15).Value = session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB08/ssubSUB_GROUP_10:SAPLIQS0:7218/ctxtRMIPM-PLNNR").Text

Anyone any ideas on how to do it the other way around.

PS

session.findById("wnd[0]/usr/tabsTAB_GROUP_10/tabp10\TAB08/ssubSUB_GROUP_10:SAPLIQS0:7218/ctxtRMIPM-PLNNR").Text =

Cells(9, 15).Value

Doesn't work because I guess I am not activating the workbook and worksheet, IE:

​

​

Windows("XXX.xlsm").Activate
Sheets("XXX").Activate

###### Second Question

The code which enables the connection between SAP and Excel has to be repeated every time you swap between the two - I have tried putting this code as a sub on its own, and then calling it within another sub (example below), but it doesn't work and debugs in the fashion that it would if you tried to manipulate SAP without the code being there (A problem encountered in the early days of my current work!).

Can anyone suggest a way of globally declaring it? and then referencing it multiple times throughout the module without having to repeat it? As I said im not a programmer but an analyst so I appreciate this might be a easy task.

Sub Enable_SAP

On Error Resume Next

'######## Get SAP GUI scripting object and test connection

Set SapGuiAuto = GetObject("SAPGUI")
If SapGuiAuto Is Nothing Then
MsgBox ("No Connection to SAP")
Exit Sub
End If

'######## Get the currently running SAP GUI and Allow scripts

Set SAPApp = SapGuiAuto.GetScriptingEngine
Set SAPCon = SAPApp.Children(0) '<---- First session to binded SAP server (can have multiple sessions active)
If SAPCon Is Nothing Then
MsgBox ("No Open Sessions")
Exit Sub
End If

Etc ……

Sub X

Call Enable_SAP

session.findById("wnd[0]").resizeWorkingPane 201, 30, False <------ Debug Error Here as if module has not been called
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nziw39"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/chkDY_MAB").Selected = True
session.findById("wnd[0]/usr/chkDY_HIS").Selected = True

….

Thanks in advance

 

[jkpieterse]

#1 is simple enough:

Workbooks("xxx.xlsm").Worksheets("XXX").Cells(9, 15).Value

#2 is a bit hard to answer, the code you posted doesn't initiate the object variable "session" anywhere so looks incomplete.

 

[kiasor]

Thanks jkp ill try No.1 Out.

No2: yes the code isn't complete in the example but that's because its actually irrelevant (I cut the end off to make the post shorter). Its actually the principle I am asking.

Could that section of code be defined in some way that would allow it to be called multiple times throughout the rest of the module?

Kind Regards,

 

[jkpieterse]

That is my point, it is NOT irrelevant, you've omitted crucial bits. There must be code that sets session to something and that bit is missing.

 

[kiasor]

I think you have misunderstood i have been using this code and it works fine for months.

I am now trying to tidy it up, shorten it and add functionality.

To do this i am trying to "call" a defined section of code WHATEVER IT MAY BE (i am not asking you to evaluate that syntax).

In principle do you know how you do this.

As you seem to insist on seeing the irrelevant code here it is.


Sub Enable_SAP()


On Error Resume Next

Set SapGuiAuto = GetObject("SAPGUI")
If SapGuiAuto Is Nothing Then
MsgBox ("No Connection to SAP")
Exit Sub
End If


Set SAPApp = SapGuiAuto.GetScriptingEngine
Set SAPCon = SAPApp.Children(0)
If SAPCon Is Nothing Then
MsgBox ("No Open Sessions")
Exit Sub
End If




Set session = SAPCon.Children(0)
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If
Application.DisplayAlerts = False

Sub Get_Hours_Before16th()

Call Enable_SAP <------ Doesn't Appear to work in this situation



session.findById("wnd[0]").resizeWorkingPane 201, 30, False <------ Because it breaks here - but if the code within enable sap sub was manually here, rather than being called, it would work.
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nziw39"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/chkDY_MAB").Selected = True 'Completed
session.findById("wnd[0]/usr/chkDY_HIS").Selected = False 'Historical
session.findById("wnd[0]/usr/chkDY_OFN").Selected = False 'Outstanding
session.findById("wnd[0]/usr/chkDY_IAR").Selected = True 'In Process
MsgBox "Enter 1st day of month into Period Box 1 (Date) in SAP, then press OK on this Box"
session.findById("wnd[0]/usr/ctxtDATUB").Text = ""
session.findById("wnd[0]/usr/ctxtDY_PARNR").Text = ""

ETC ETC

 

[jkpieterse]

I have not misunderstood a thing. You now posted bits of code you did not post before:

Set session = SAPCon.Children(0)
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject Application, "on"
End If

It is exactly this piece that was missing for me to be able to answer your questions.

I expect this'll work:

Function Get_SAPSession() As Object
    On Error Resume Next

    Set SapGuiAuto = GetObject("SAPGUI")
    If SapGuiAuto Is Nothing Then
        MsgBox ("No Connection to SAP")
        Exit Function
    End If


    Set SAPApp = SapGuiAuto.GetScriptingEngine
    Set SAPCon = SAPApp.Children(0)
    If SAPCon Is Nothing Then
        MsgBox ("No Open Sessions")
        Exit Function
    End If

    Set session = SAPCon.Children(0)
    If IsObject(WScript) Then
        WScript.ConnectObject session, "on"
        WScript.ConnectObject Application, "on"
    End If
    Application.DisplayAlerts = False
    Set Get_SAPSession = session
End Function

Sub Get_Hours_Before16th()

    Set session = Get_SAPSession
    If session Is Nothing Then
        MsgBox "Something went wrong, couldn't get SAP session"
        Exit Sub
    End If
    session.findById("wnd[0]").resizeWorkingPane 201, 30, False    '<------ Because it breaks here - but if the code within enable sap sub was manually here, rather than being called, it would work.
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nziw39"
    session.findById("wnd[0]").sendVKey 0
    session.findById("wnd[0]/usr/chkDY_MAB").Selected = True    'Completed
    session.findById("wnd[0]/usr/chkDY_HIS").Selected = False    'Historical
    session.findById("wnd[0]/usr/chkDY_OFN").Selected = False    'Outstanding
    session.findById("wnd[0]/usr/chkDY_IAR").Selected = True    'In Process
    MsgBox "Enter 1st day of month into Period Box 1 (Date) in SAP, then press OK on this Box"
    session.findById("wnd[0]/usr/ctxtDATUB").Text = ""
    session.findById("wnd[0]/usr/ctxtDY_PARNR").Text = ""
End Sub

 

[kiasor]

Ok I see it was me who misunderstood the principle of how you would go about it.

Now I see why you needed all the code .

Thanks so much Jkp I will try this out tomorrow and let you know if it works.