SAP connected with Excel

[Former Member]

Hello everyone,

i try to write a macro which transports changes in an excel sheet directly to SAP.

My macro already starts as soon as there is a change in the excel sheet. The problem is: sometimes it works fine with the SAP sometimes i get this "Run-time error 91 : Object variable or with block variable not set

My macro starts like this:

Public Einheit As Variant
Public TechnischerPlatz As Variant
Public SAPGuiAuto
Public SAPConnetion
Public SAPsession
Public j

Sub SAP_trennen()

Set SAPsession = Nothing
Set SAPConnection = Nothing
Set SAPGuiAuto = Nothing

End Sub

Sub SAP_anbinden()

If Not IsObject(SAPapplication) Then
   Set SAPGuiAuto = GetObject("SAPGUI")
   Set SAPapplication = SAPGuiAuto.GetScriptingEngine
End If
If Not IsObject(SAPConnection) Then
   Set SAPConnection = SAPapplication.Children(0)
End If
If Not IsObject(SAPsession) Then
   Set SAPsession = SAPConnection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAPsession, "on"
   WScript.ConnectObject Application, "on"
End If

End Sub

I use different Subs because i don´t want to allow the SAP Access every single time there is a change in the Excel-Sheet. I the end it should run kind of automated. So that someone start the SAP every morning and for the rest of the day the excel sheet and SAP run on there own.

It´s the second time i use macros and SAP Scripting so i hope there is some "easy mistake" i can´t find because of my bad macro skills.

Can anybody help me?

Best regards

Eva

[Former Member · Accepted solution]

Hi ScriptMan,

thank you for your answer. I still don´t now what the problem is but i figured out how to work with it.

When i first opened my Excel-Sheet everything worket perfect but in the moment i change something in an makro and open the developer something happens because afterwards it´s not working anymore. So my solution is everytime i change something i close Excel, open Excel, start my macro and it works perfect...

Best regards

Eva

[script_man · Accepted solution]

Hi Eva,

It could be that the SAP objects are no longer empty and therefore they are not reset.

Try disabling the appropriate lines and try it out, then if it works out sometimes.

Sub SAP_anbinden()

'If Not IsObject(SAPapplication) Then
   Set SAPGuiAuto = GetObject("SAPGUI")
   Set SAPapplication = SAPGuiAuto.GetScriptingEngine
'End If
'If Not IsObject(SAPConnection) Then
   Set SAPConnection = SAPapplication.Children(0)
'End If
'If Not IsObject(SAPsession) Then
   Set SAPsession = SAPConnection.Children(0)
'End If
'If IsObject(WScript) Then
   WScript.ConnectObject SAPsession, "on"
   WScript.ConnectObject Application, "on"
'End If

End Sub

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Scriptman,

thank you for your effort but now i get an error in the line WScript.ConnectObject SAPsession, "on".

But it´s ok because the person who will use the macro will never open the debugger...

Now i have another problem and i could not find any solution in this board yet.

I want to transport data from SAP to Excel within my macro. I want to give SAP the number of a unit (works perfect) and get the data of Kostenstelle und Ort back (works not).  I tried it with the following macro. I used the SAP Scriptin Recorder and the Shortcuts strg+c and strg+v to the transport the data from SAP to Excel while recording. But i don´t know how to get the data i copied into excel within the macro. I hope you can understand what i mean...

Best regards,

Eva

Sub Verfizieren()

Dim i
Dim Unit
Dim Kostenstelle
Dim Ort

If Not IsObject(SAPapplication) Then
   Set SAPGuiAuto = GetObject("SAPGUI")
   Set SAPapplication = SAPGuiAuto.GetScriptingEngine
End If
If Not IsObject(SAPConnection) Then
   Set SAPConnection = SAPapplication.Children(0)
End If
If Not IsObject(SAPsession) Then
   Set SAPsession = SAPConnection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAPsession, "on"
   WScript.ConnectObject Application, "on"
End If

x = ActiveSheet.UsedRange.Rows.Count

For i = 1 To x

Unit = Range("A" & i).Value

SAPsession.findById("wnd[0]").maximize
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000032"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000259"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000260"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000346"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000352"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "F00006"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "0000000352"
SAPsession.findById("wnd[0]/usr/ctxtRM63E-EQUNR").Text = Unit
SAPsession.findById("wnd[0]").sendVKey 0
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03").Select
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1052/ctxtITOB-KOSTL").SetFocus
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1052/ctxtITOB-KOSTL").caretPosition = 0
Range("B" & i).Select
ActiveSheet.Paste
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04").Select
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1060/subSUB_1060A:SAPLITO0:1065/ctxtITOB-TPLNR").SetFocus
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1060/subSUB_1060A:SAPLITO0:1065/ctxtITOB-TPLNR").caretPosition = 0
Range("C" & i).Select
ActiveSheet.Paste
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000032"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000259"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000260"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000346"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000032"
SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

Next

End Sub

[script_man · Accepted solution]

Hi Eva,

If you have any problems at this point, you reverse the disabling there undone.:

Sub SAP_anbinden()

'If Not IsObject(SAPapplication) Then
   Set SAPGuiAuto = GetObject("SAPGUI")
   Set SAPapplication = SAPGuiAuto.GetScriptingEngine
'End If
'If Not IsObject(SAPConnection) Then
   Set SAPConnection = SAPapplication.Children(0)
'End If
'If Not IsObject(SAPsession) Then
   Set SAPsession = SAPConnection.Children(0)
'End If
If IsObject(WScript) Then
   WScript.ConnectObject SAPsession, "on"
   WScript.ConnectObject Application, "on"
End If

End Sub

Your new question:

Script Recorder records never commands as CTRL / C and CTRL / V.  You could for example do as follows.

Sub Verfizieren()

Dim i
Dim Unit
Dim Kostenstelle
Dim Ort

If Not IsObject(SAPapplication) Then
   Set SAPGuiAuto = GetObject("SAPGUI")
   Set SAPapplication = SAPGuiAuto.GetScriptingEngine
End If
If Not IsObject(SAPConnection) Then
   Set SAPConnection = SAPapplication.Children(0)
End If
If Not IsObject(SAPsession) Then
   Set SAPsession = SAPConnection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject SAPsession, "on"
   WScript.ConnectObject Application, "on"
End If

x = ActiveSheet.UsedRange.Rows.Count

For i = 1 To x

Unit = Range("A" & i).Value

'Deactivate the following lines

SAPsession.findById("wnd[0]").maximize
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000032"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000259"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000260"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000346"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000352"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "F00006"

'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "0000000352"

'Use this method instead of starting a transaction.

SAPsession.findById("wnd[0]/tbar[0]/okcd").text = "/n" & myTransaction  'e.g. myTransaction  = "abcdef"

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

SAPsession.findById("wnd[0]/usr/ctxtRM63E-EQUNR").Text = Unit
SAPsession.findById("wnd[0]").sendVKey 0
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03").Select

'The two lines are not required.
'SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1052/ctxtITOB-KOSTL").SetFocus
'SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1052/ctxtITOB-KOSTL").caretPosition = 0

myKOSTL = SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\03/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1052/ctxtITOB-KOSTL").text
Range("B" & i).Select
'ActiveSheet.Paste

Range("B" & i).Value = myKOSTL
SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04").Select

'The two lines are not required.

'SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1060/subSUB_1060A:SAPLITO0:1065/ctxtITOB-TPLNR").SetFocus
'SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1060/subSUB_1060A:SAPLITO0:1065/ctxtITOB-TPLNR").caretPosition = 0

myTPLNR =  SAPsession.findById("wnd[0]/usr/tabsTABSTRIP/tabpT\04/ssubSUB_DATA:SAPLITO0:0102/subSUB_0102A:SAPLITO0:1060/subSUB_1060A:SAPLITO0:1065/ctxtITOB-TPLNR").text
Range("C" & i).Select
'ActiveSheet.Paste

Range("C" & i).Value = myTPLNR
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press
SAPsession.findById("wnd[0]/tbar[0]/btn[3]").press

'The following lines are not required.

'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000032"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000259"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000260"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").collapseNode "0000000346"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000032"
'SAPsession.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

Next

End Sub

Regards,

ScriptMan

[Former Member · Accepted solution]

Hi Script Man,

Thank you very much!

Best regards

Eva

[script_man]

Hi Eva,

In the two subs I can not see any errors.

Where does occur this error?  Which command line does that mean? Are multiple SAP sessions open?

About Excel and SAP GUI Scripting I recommend the following link:

http://scn.sap.com/community/scripting-languages

Regards,

ScriptMan