Excel VBA and SAP

[jay_dudley]

Hello, I have noticed that SAP has an option where we can record a macro similiar to the way MS excel allows you to record macros, and then saves this as a .vbs file. Im wondering if there is any way to write some code in excel vba, to fetch data from SAP and/or emulate user interaction with SAP and return the data into excel. Let me know if my question is too vague and i will try and come up with an example. Any help/pointers/tips/direction is extremely appreciated!

 

[GermanPath]

Hello.

Yes, all of this is possible. You may should be more specific what you want to do. Then we can be more specific.

 

[jay_dudley]

sure, here is a sample of some code, i'm trying to copy a value from cell a1 and paste it into a field in SAP.

Sub lookup()
Dim lookupnum As Integer


lookupnum = Range("a1").Value






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]").resizeWorkingPane 133, 43, False
session.findById("wnd[0]/tbar[0]/okcd").Text = "me23n"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0020/subSUB1:SAPLMEVIEWS:1100/subSUB2:SAPLMEVIEWS:1200/subSUB1:SAPLMEGUI:1102/tabsHEADER_DETAIL/tabpTABHDT7").Select


End Sub

 

[GermanPath]

in which field you watnt to fill it in?

There is one rule in SAP GUI Scripting. Each field which should appear in recorded script need to touch manual even when the correct value is in. Otherwise you will not have it in recording and you will not be able to pass it via VBA.

Please make an recording where this field is visible in recording. Thanks.

 

[jay_dudley]

will do!

 

[jay_dudley]

here it is (hopefully i understood your request correctly)

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]").resizeWorkingPane 133,43,false
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00003"
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00003"
session.findById("wnd[0]/tbar[1]/btn[17]").press
session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").text = "4506076370"
session.findById("wnd[1]/usr/subSUB0:SAPLMEGUI:0003/ctxtMEPO_SELECT-EBELN").caretPosition = 10
session.findById("wnd[1]").sendVKey 0

 

[GermanPath]

I will write tomorrow some information about this. ME23N are an enjoj transaction. This means that SAPLMEGUI screen can change

e.g.

~~SAPLMEGUI:0003~~

can change to screen 0006 or anyone else.

So to avoid interruption you need to include many IF-statements which will ensure that under all circumstances the code is allocated to correct screen.

I will post tomorrow some standard code which make interaction between SAP and Excel more comfortable. You should discover as many as you can about this topic.

When I have post my example you should be able to connect Excel VBA to any open SAP session on your PC.
Know how to loop over many rows in Excel and how to transfer them to SAP
Errorhandling from SAP-messages in SAP
Execution of recorded scripts.

Do not expect that I will always solve your issues

Therefore I´m too busy. It´s your chance for development.

 

[jay_dudley]

I appreciate your help very much, and if you wouldnt mind pointing me to some resources...either a book or a website etc, that i could use i would be in your debt!

 

[GermanPath]

Space: Scripting Languages | SCN[thread]

This is an good start point.