Help needed with VBA script Method ’Children' of object ’ISapMainWindowTarget' failed

[valquisser]

Hello experts,

I am trying to write a vba script to script data from excel to SAP. The script below is to assign SAP roles to user in SAP via tcode SU10.

When i run the script, it's give 2 error as below from line - Sheets("SAP_DATA").Cells(lDataRow, 19) = Session.ActiveWindow.children.Item(4).Text

Run-time error ’-2147483638 (8000000a)’: Method ’Children' of object ’ISapMainWindowTarget' failed

Run-time error '91: Object variable or With block variable not set

Below is the full script:

Option Explicit Dim oSapGuiAuto As Object, oGuiApplication As Object, oConnection As Object, Session As Object Dim lLastRow As Long, lDataRow As Long, lStart As Long, lPauseTime As Long Dim A As String, B As String, C As String, D As String, E As String, F As String, G As String, H As String Dim I As String, J As String, K As String, L As String, M As String, N As String, O As String, sMessage As String Dim sStartingTransaction As String, Cell2Swap As String, sContinue Private Sub PlayBack_The_Script() 'called from the main sub - "Excel_Starts_Here" On Error GoTo ErrorHandler '//************************************************ '//******PASTE YOUR RECORDED SCRIPT BELOW********** '// ** Lines that begin Session.findById("wnd * '//************************************************ '// *** to use variable data in Spreadsheet Column 'A', change from .Text = "..." to .Text = A (no quotes) '// *** to use variable data in Spreadsheet Column 'B', change from .Text = "..." to .Text = B ect. '//__________________________________________________________________________________________________________ Session.findById("wnd[0]").resizeWorkingPane 84, 27, False Session.findById("wnd[0]/tbar[0]/okcd").Text = "/NSU10" Session.findById("wnd[0]").sendVKey 0 Session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").Text = A Session.findById("wnd[0]/usr/tblSAPLSUID_MAINTENANCETC_USERS/ctxtSUID_ST_BNAME-BNAME[0,0]").caretPosition = 6 Session.findById("wnd[0]/tbar[1]/btn[18]").press Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG").Select Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0, "SUBSYSTEM", B Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0, "AGR_NAME", C Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0, "UPDATE_FROM_DAT", D Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").modifyCell 0, "UPDATE_TO_DAT", E Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").currentCellColumn = "UPDATE_TO_DAT" Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").firstVisibleColumn = "UPDATE_TO_DAT" Session.findById("wnd[0]/usr/tabsTABSTRIP1/tabpACTG/ssubMAINAREA:SAPLSUID_MAINTENANCE:1106/cntlG_ROLES_CONTAINER/shellcont/shell").pressEnter Session.findById("wnd[0]").sendVKey 11 Session.findById("wnd[0]/tbar[0]/okcd").Text = "/N" Session.findById("wnd[0]").sendVKey 0 '//__________________________________________________________________________________________________________ '//******PASTE YOUR RECORDED SCRIPT ABOVE********** '//******END OF RECORDED SAP SCRIPT********************************* '//***************************************************************** ' test for error and log off messages If Session.ActiveWindow.Text = "Error" Then Err.Raise 99 ' error detected? ' If Session.ActiveWindow.children.Count = 5 Then ' see if the collection item exists sMessage = Session.ActiveWindow.children.Item(4).Text ' message found If sMessage = "" Then sMessage = "OK" Sheets("SAP_DATA").Cells(lDataRow, 17) = sMessage Else Sheets("SAP_DATA").Cells(lDataRow, 17) = "OK" ' message - no error found End If ' If Session.ActiveWindow.Name = "wnd[1]" And Session.ActiveWindow.Text = "Log Off" Then Session.findById("wnd[1]/usr/btnSPOP-OPTION2").press Sheets("SAP_DATA").Cells(lDataRow, 16) = "error" End If '------------------------------------------------------- Exit Sub ErrorHandler: Look (2) Sheets("SAP_DATA").Cells(lDataRow, 18) = Session.ActiveWindow.Text If Session.ActiveWindow.children.Count = 5 Then Sheets("SAP_DATA").Cells(lDataRow, 19) = Session.ActiveWindow.children.Item(4).Text End If If Session.ActiveWindow.SystemFocus Is Nothing Then If sStartingTransaction <> "/nSESSION_MANAGER" Then AppActivate "Microsoft Excel" MsgBox " Lost SAP focus " ContinueOrCancel 'ask End If Else Sheets("SAP_DATA").Cells(lDataRow, 20) = Session.ActiveWindow.SystemFocus.Text If Session.ActiveWindow.Name = "wnd[3]" Then Session.findById("wnd[3]/tbar[0]/btn[0]").press 'auto ackn error Session.findById("wnd[2]/tbar[0]/btn[12]").press ' hit cancel Session.findById("wnd[1]/tbar[0]/btn[12]").press ' hit cancel again End If If Session.ActiveWindow.Name = "wnd[2]" Then Session.findById("wnd[2]/tbar[0]/btn[0]").press 'auto ackn error Session.findById("wnd[1]/tbar[0]/btn[12]").press ' hit cancel End If If Session.ActiveWindow.Name = "wnd[1]" Then Session.findById("wnd[1]/tbar[0]/btn[12]").press ' pound on that cancel key End If If Session.ActiveWindow.Name = "wnd[0]" Then 'test for starting at the menu If sStartingTransaction = "/nSESSION_MANAGER" Then 'avoid an error If Session.ActiveWindow.Text <> "SAP Easy Access" Then Session.findById("wnd[0]/tbar[0]/btn[12]").press ' hit cancel Else Session.findById("wnd[0]/tbar[0]/okcd").Text = sStartingTransaction Session.findById("wnd[0]/tbar[0]/btn[0]").press End If Else ContinueOrCancel 'ask End If 'Session.ActiveWindow.Name = "wnd[0]" End If 'Session.ActiveWindow.SystemFocus Is Nothing ' Exit Sub End Sub Sub Excel_Starts_Here() '--------------------------------------------------------------- ' Cas Orzechowski 698-5153 February 12, 2004 | '--------------------------------------------------------------- ' Activate Scripting | Set oSapGuiAuto = GetObject("SAPGUI") ' | Set oGuiApplication = oSapGuiAuto.GetScriptingEngine ' | If (oConnection Is Nothing) Then ' | Set oConnection = oGuiApplication.children(0) ' | End If ' | If (Session Is Nothing) Then ' | Set Session = oConnection.children(0) ' | End If ' | If (Session Is Nothing) Then Exit Sub ' | sStartingTransaction = "/n" & Session.info.transaction '| '---------------------------------------------------------------- ' Get a count - # of Rows on SAP_DATA spreadsheet ' put a temporary formula in the spreadsheet at V-1 to count total rows ' The cell must be formated as 'General'. Cell2Swap = Sheets("SAP_DATA").Cells(1, 22) Sheets("SAP_DATA").Cells(1, 22).Formula = "=counta(a:a)" lLastRow = (Sheets("SAP_DATA").Cells(1, 22)) Sheets("SAP_DATA").Cells(1, 22) = Cell2Swap ' remove temporary formula ' ------------------------------------------------------------------- If lLastRow = 0 Then lLastRow = 1 ' run at least once '--------------------------------------------------------- '******************************************* ' Shrink Excel and move it out of the way Application.WindowState = xlMinimized '******************************************** ' Try one lDataRow = 1 Read_Spreadsheet PlayBack_The_Script 'Give the user a chance to verify If lDataRow < lLastRow Then AppActivate "Excel" Application.WindowState = xlMaximized sContinue = MsgBox("First Transaction is complete... Continue Processing?", vbOKCancel) If sContinue = vbOK Then ' User chose Yes. Application.WindowState = xlMinimized '-------------------------------- ' Run the script For lDataRow = 2 To lLastRow Read_Spreadsheet PlayBack_The_Script Next lDataRow '-------------------------------- End If ' - run all transactions End If ' lDataRow < lLastRow ' Cleanup Set Session = Nothing Set oConnection = Nothing Set oGuiApplication = Nothing Set oSapGuiAuto = Nothing Application.WindowState = xlMaximized MsgBox "Done" End Sub Private Sub Read_Spreadsheet() A = Trim(Sheets("SAP_DATA").Cells(lDataRow, 1)) B = Trim(Sheets("SAP_DATA").Cells(lDataRow, 2)) C = Trim(Sheets("SAP_DATA").Cells(lDataRow, 3)) D = Trim(Sheets("SAP_DATA").Cells(lDataRow, 4)) E = Trim(Sheets("SAP_DATA").Cells(lDataRow, 5)) F = Trim(Sheets("SAP_DATA").Cells(lDataRow, 6)) G = Trim(Sheets("SAP_DATA").Cells(lDataRow, 7)) H = Trim(Sheets("SAP_DATA").Cells(lDataRow, 8)) I = Trim(Sheets("SAP_DATA").Cells(lDataRow, 9)) J = Trim(Sheets("SAP_DATA").Cells(lDataRow, 10)) K = Trim(Sheets("SAP_DATA").Cells(lDataRow, 11)) L = Trim(Sheets("SAP_DATA").Cells(lDataRow, 12)) M = Trim(Sheets("SAP_DATA").Cells(lDataRow, 13)) N = Trim(Sheets("SAP_DATA").Cells(lDataRow, 14)) O = Trim(Sheets("SAP_DATA").Cells(lDataRow, 15)) End Sub Private Sub Look(lPauseTime) '----------------------------------------- lStart = Timer ' Set start time. | Do While Timer < lStart + lPauseTime '| 'wait | Loop ' | '----------------------------------------- End Sub Private Sub ContinueOrCancel() ' ------------------------------------------------------------------- sContinue = MsgBox("Do you want to Continue?", vbRetryCancel) If sContinue = vbRetry Then ' User chose Yes. MsgBox "recycle SAP transaction " & sStartingTransaction Else lDataRow = lLastRow ' End If ' ----------------------------------------------------------------------- End Sub

 

[Zaifee]

The error you are encountering is related to the SAP scripting interface and not specifically to VBA. It seems that the Session.ActiveWindow.children collection is not available or accessible in your script, causing the errors.
To troubleshoot this issue, you can try the following steps:

1. Ensure that you have the SAP GUI Scripting enabled in your SAP system. You can check this by going to SAP Logon > Options > Scripting and enabling the "Enable scripting" checkbox.
2. Verify that the Session object is properly initialized and connected to the SAP system. Check if the Session object is set correctly by debugging or printing its properties.
3. Confirm that the specific SAP transaction (tcode SU10) is open and active when running the script. Make sure the transaction is open before executing the script.
4. Double-check the window hierarchy and object structure in SAP when the transaction is open. The error might occur if the script is referencing an incorrect object or if the window hierarchy has changed.

If the issue persists after trying these steps, it might be necessary to consult with your SAP system administrator or an SAP expert who can provide specific guidance for interacting with SAP via scripting in VBA. They can help you ensure that the required objects and methods are available and guide you on how to navigate the SAP GUI using VBA.


Regenerate response