VBA script to sign on to SAP using button in Excel

[Former Member]

First of all, I am a newbie using SAP scripting; however, I have used macros in Excel on a regular basis. I have recorded a script in SAP that I want to use as a VBA macro in Excel 2010 that is attached to a button. I have researched several scripts in this discussion board, but none seem to work. Below is the script that I recorded in SAP. The only change I made was to the "application" lines (from "application" to "SAPapplication") since Excel has its own term for "application". I get the error message " the enumerator of the collection cannot find en element with the specified index" at the "Set Connection = SAPApplication.Children(0)" line.

My goal is to click a button on the Excel worksheet, have the user be prompted to select the SAP system to connect to and enter their sign-on information, then have the script run that generates an invoice. Can you please help me?

Sub Button63_Click()

If Not IsObject(SAPApplication) Then

Set SapGuiAuto = GetObject("SAPGUI")

Set SAPApplication = SapGuiAuto.GetScriptingEngine

End If

If Not IsObject(Connection) Then

Set Connection = SAPApplication.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 SAPApplication, "on"

End If

session.findById("wnd[0]/usr/txtRSYST-MANDT").Text = "010"

session.findById("wnd[0]/usr/txtRSYST-BNAME").Text = " "

session.findById("wnd[0]/usr/pwdRSYST-BCODE").Text = " "

session.findById("wnd[0]/usr/txtRSYST-LANGU").Text = "EN"

session.findById("wnd[0]/usr/txtRSYST-LANGU").SetFocus

session.findById("wnd[0]/usr/txtRSYST-LANGU").caretPosition = 2

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

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

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000033"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000103"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000117"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000224"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").expandNode "0000000229"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000230"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").topNode = "Favo"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "0000000230"

session.findById("wnd[0]/usr/ctxtRF022-BUKRS").Text = "554"

session.findById("wnd[0]/usr/ctxtRF022-BUKRS").caretPosition = 3

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

session.findById("wnd[1]/usr/lbl[1,7]").SetFocus

session.findById("wnd[1]/usr/lbl[1,7]").caretPosition = 3

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

session.findById("wnd[1]/usr/txtRF022-BELNR").Text = "1300000258"

session.findById("wnd[1]/usr/txtRF022-GJAHR").Text = "2013"

session.findById("wnd[1]/usr/txtRF022-GJAHR").SetFocus

session.findById("wnd[1]/usr/txtRF022-GJAHR").caretPosition = 4

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

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

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "0000000231"

session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "0000000231"

session.findById("wnd[0]/usr/ctxtBUKRS-LOW").Text = "554"

session.findById("wnd[0]/usr/txtBELNR-LOW").Text = "1300000258"

session.findById("wnd[0]/usr/txtBELNR-LOW").SetFocus

session.findById("wnd[0]/usr/txtBELNR-LOW").caretPosition = 10

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

session.findById("wnd[0]/usr/lbl[1,11]").SetFocus

session.findById("wnd[0]/usr/lbl[1,11]").caretPosition = 2

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

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

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

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

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

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

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

End Sub

[holger_khn]

Hi Sheila.

Below an example how I use one open SAP session from enduser and perform my mass update task via Excel VBA.

Sub function_button2()
'**************************************************************************
'* Sub : Run SE38 Report ZZCOBK from Excel foreground---------------------*
'* Author : Holger Köhn---------------------------------------------------*
'* Created : 22.03.2013---------------------------------------------------*
'* Changed :--------------------------------------------------------------*
'**************************************************************************

Dim sap_applic
Dim Connection
Dim session
Dim WScript
Dim iCounter%
Dim strKOSKR$
Dim strBELNR$
Dim strMSG$
Dim strSession_Title
Dim UPD_COUNT&
Dim iMsg%
Dim iMSG1%
Dim iMSG2%
Dim a%
Dim b%

'**************************************************************************
'* get for how many entries ZZCOBK need to run----------------------------*
'**************************************************************************

Application.ScreenUpdating = False
ThisWorkbook.Sheets(1).Activate

With ActiveSheet
  UPD_COUNT = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = True

'**************************************************************************
'* check validity of key fields-------------------------------------------*
'**************************************************************************

If UPD_COUNT = 1 Then
  MsgBox "Please check sheet 'SAPLFMCO issue ZZCOBK' as there are no entries.", vbCritical + vbMsgBoxSetForeground, "ERROR - No entries in sheet"
  ThisWorkbook.Sheets(1).Activate
  Exit Sub
End If

ThisWorkbook.Sheets(1).Activate

iMsg = MsgBox("Only execute this upload when you´re absolutely sure that you want rund SE38 report ZZCOBK for this template content! Do you want to proceed?", vbYesNo + vbDefaultButton2 + vbMsgBoxSetForeground + vbExclamation, "FINAL CHECK")
If iMsg = "7" Then
MsgBox "You have clicked 'No' => Update terminated. Execute again when master data is correct.", vbExclamation, "Information"
Exit Sub
End If

'**************************************************************************
'* set SAP session--------------------------------------------------------*
'**************************************************************************

If Not IsObject(sap_applic) Then

  Dim SapGuiAuto

  On Error Resume Next

  Set SapGuiAuto = GetObject("SAPGUI")

  On Error GoTo 0

  If SapGuiAuto Is Nothing Then

  MsgBox "Please start SAPlogon"

  Exit Sub

  End If

  On Error Resume Next

  Set sap_applic = SapGuiAuto.GetScriptingEngine
'SAPLogonNachHinten 'comment out as this require more code which is not posted in this answer
ExcelWBNachVorn
  On Error GoTo 0

  If sap_applic Is Nothing Then

  MsgBox "Scripting disabled"

  Exit Sub

  End If
'SAPLogonNachHinten'comment out as this require more code which is not posted in this answer
'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
End If

'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
ThisWorkbook.Sheets(1).Activate
ThisWorkbook.Sheets(1).Cells(2, 1).Select

If Not IsObject(Connection) Then
  For a = 0 To (sap_applic.Children.Count - 1)
  For b = 0 To 6
  On Error Resume Next
  'SAPLogonNachHinten
  ' ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
  ThisWorkbook.Sheets(1).Activate
  ThisWorkbook.Sheets(1).Cells(2, 1).Select
  iMSG1 = MsgBox("Window: " & sap_applic.Children(0 + a).Children(0 + b).info.sessionnumber & "| System: " & sap_applic.Children(0 + a).Children(0 + b).info.systemname & " | Transaction: " & sap_applic.Children(0 + a).Children(0 + b).info.transaction & " | User: " & sap_applic.Children(0 + a).Children(0 + b).info.user & vbCr & "Do you want to use this session for Upload?", vbQuestion + vbYesNo + vbDefaultButton2, "Select correct SAP Session")
  If iMSG1 = "6" Then
  Set Connection = sap_applic.Children(0 + a)
  If Not IsObject(session) Then
  Set session = Connection.Children(0 + b)
  End If
  Exit For
  End If

  Next b
  If iMSG1 = "6" Then Exit For
  Next a
End If

If sap_applic.Children.Count = "0" Then
  MsgBox "Please logon to SAP-System where you want run report ZZCOBK!", vbCritical + vbMsgBoxSetForeground, "Error"
  Exit Sub
End If

If iMSG1 = "7" Then
  'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
  ThisWorkbook.Sheets(1).Activate
  MsgBox "You have not selected any available SAP Session. Execution aborted!", vbInformation + vbMsgBoxSetForeground, "Exit Information"
  Exit Sub
Else
  'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
  ThisWorkbook.Sheets(1).Activate
  iMSG2 = MsgBox("You have selected System: " & session.info.systemname & " | Open transaction: " & session.info.transaction & " | Executed by user: " & session.info.user, vbInformation + vbMsgBoxSetForeground + vbYesNo + vbDefaultButton2, "System Information")
End If

If iMSG2 = "7" Then
  Set session = Nothing
  Set Connection = Nothing
  Set sap_applic = Nothing
  Set SapGuiAuto = Nothing
  MsgBox "You have aborted this update. Please start again.", vbExclamation + vbMsgBoxSetForeground, "Information"
  Exit Sub
End If

If IsObject(WScript) Then
  WScript.ConnectObject session, "on"
  WScript.ConnectObject sap_applic, "on"
End If

'**************************************************************************
'* get selected session GuiTitle as handle--------------------------------*
'**************************************************************************

strSession_Title = session.findById("wnd[0]").Text

'**************************************************************************
'* /get selected session GuiTitle as handle-------------------------------*
'**************************************************************************

'SAPNachVorn (strSession_Title)'comment out as this require more code which is not posted in this answer

'**************************************************************************
'* start SE38 and report ZZCOBK-------------------------------------------*
'**************************************************************************

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nse38"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRS38M-PROGRAMM").Text = "ZZCOBK"
session.findById("wnd[0]/usr/ctxtRS38M-PROGRAMM").caretPosition = 6
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/chkP_TEST").Selected = False

'**************************************************************************
'* Counting loop for recreation of COBK-----------------------------------*
'**************************************************************************

For iCounter = 2 To UPD_COUNT

  If ThisWorkbook.Sheets(1).Cells(iCounter, 6).Value = "" Then

'**************************************************************************
'* fill the used variables------------------------------------------------*
'**************************************************************************

  strKOSKR = ActiveWorkbook.Sheets(1).Cells(iCounter, 2).Value
  strBELNR = ActiveWorkbook.Sheets(1).Cells(iCounter, 3).Value
  
  If strBELNR = "" Then Exit For

  session.findById("wnd[0]/usr/ctxtP_KOKRS").Text = strKOSKR
  session.findById("wnd[0]/usr/ctxtR_BELNR-LOW").Text = strBELNR
  session.findById("wnd[0]/usr/chkP_TEST").SetFocus
  session.findById("wnd[0]/tbar[1]/btn[8]").press
  session.findById("wnd[0]/tbar[0]/btn[3]").press

  strKOSKR = ""
  strBELNR = ""

  ThisWorkbook.Sheets(1).Cells(iCounter, 6).Value = "X"

  End If

Next

ThisWorkbook.Save
ThisWorkbook.Saved = True

'**************************************************************************
'* get selected session GuiTitle as handle--------------------------------*
'**************************************************************************

strSession_Title = session.findById("wnd[0]").Text

'**************************************************************************
'* /get selected session GuiTitle as handle-------------------------------*
'**************************************************************************

'SAPNachHinten (strSession_Title)'comment out as this require more code which is not posted in this answer
'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer

ThisWorkbook.Sheets(1).Activate
Application.ScreenUpdating = True

'**************************************************************************
'* clear objects----------------------------------------------------------*
'**************************************************************************

Set session = Nothing
Set Connection = Nothing
Set sap_applic = Nothing
Set SapGuiAuto = Nothing
'ExcelWBNachVorn'comment out as this require more code which is not posted in this answer
Beep
MsgBox "Update SE38 report ZZCOBK for sheet 'SAPLFMCO issue ZZCOBK' done.", vbInformation + vbMsgBoxSetForeground, "Done"
End Sub

Between line 48 and 118 the macro get through all existing SAP sessions and ask user if he want use one of them. In my original Excel sheets I have a little bit more functions integrated which will ensure, that Excel is in foreground during this operation. SAP Logon will be in background. And selected session will run in foreground when update is initiated. This is done by API-functions from user32.dll in my code. In above code this is not active!

When you have any qiestions let me know.

Br, Holger