Option Explicit Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Dim oSapGuiAuto As Object, oGuiApplication As Object, oConnection As Object, Session As Object Dim lLastRow As Long, lDataRow 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, 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. '//__________________________________________________________________________________________________________ '//__________________________________________________________________________________________________________ '//******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 > 1 Then ' see if the collection item exists sMessage = Session.ActiveWindow.children.Item((Session.ActiveWindow.children.Count - 1)).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: Sleep (1000) 'return the error message 3/4/06 - *** sMessage = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description '& Err.HelpFile & Err.HelpContext Sheets("SAP_DATA").Cells((lDataRow), 21) = sMessage On Error Resume Next If Session.ActiveWindow.Type = "GuiModalWindow" Then Sheets("SAP_DATA").Cells(lDataRow, 18) = Session.ActiveWindow.Text & " " & Session.ActiveWindow.PopupDialogText '3/16/2006 - *** - additional message to return Else Sheets("SAP_DATA").Cells(lDataRow, 18) = Session.ActiveWindow.Text End If ' If Session.ActiveWindow.children.Count > 1 Then Sheets("SAP_DATA").Cells(lDataRow, 19) = Session.ActiveWindow.children.Item((Session.ActiveWindow.children.Count - 1)).Text End If If Session.ActiveWindow.SystemFocus Is Nothing Then ' ------------ If sStartingTransaction = "SESSION_MANAGER" Then sStartingTransaction = "" ' 12/15/04 test and close window[1] - *** If Session.ActiveWindow.Name = "wnd[1]" Then Session.findById("wnd[1]").Close Else If Session.ActiveWindow.Name <> "wnd[0]" Then '3/17/06 - *** AppActivate "Microsoft Excel" MsgBox " Lost SAP focus " ContinueOrCancel 'ask Else Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" & sStartingTransaction Session.findById("wnd[0]/tbar[0]/btn[0]").press ' End If 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 '********** test before pressing the cancel key on window [1]- *** 12/15/04 Sleep (10) End If If Session.ActiveWindow.Name = "wnd[2]" Then Session.findById("wnd[2]/tbar[0]/btn[0]").press 'auto ackn error '********** test before pressing the cancel key on window [1]- *** 12/15/04 Sleep (10) End If If Session.ActiveWindow.Name = "wnd[1]" Then If Session.ActiveWindow.children.Count > 0 Then ' 3/16/06 - *** If Session.ActiveWindow.Text = "Information" Then ' blow off the message Session.findById("wnd[1]/tbar[0]/btn[0]").press Resume '***This retries the keystroke that caused the error***3/5/2004 End If End If 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 = "SESSION_MANAGER" Or sStartingTransaction = "" Then 'avoid an error 3/17/06 - *** If Session.ActiveWindow.Text <> "SAP Easy Access" Then Session.findById("wnd[0]/tbar[0]/btn[12]").press ' hit cancel ' Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" '3/17/06 - *** Session.findById("wnd[0]/tbar[0]/btn[0]").press ' Else Session.findById("wnd[0]/tbar[0]/okcd").Text = "/n" & 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_v1() '--------------------------------------------------------------- ' *** abc abc February 12, 2004 | ' changed error handling to ignore informational messages 3/5/04| ' replaced Look routine with Sleep. 1/22/07 | '--------------------------------------------------------------- ' 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 = Session.info.transaction '| '---------------------------------------------------------------- Application.CutCopyMode = False Rows("1:1").Select '3/4/06 - *** Selection.Insert Shift:=xlDown ' give it a blank row to find and delete [A:A].SpecialCells(xlBlanks).EntireRow.Delete lLastRow = Sheets("SAP_DATA").UsedRange.Rows.Count '--------------------------------------------------------- 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 "Microsoft 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 ContinueOrCancel() ' ------------------------------------------------------------------- sContinue = MsgBox("Do you want to Continue?", vbRetryCancel) If sContinue = vbRetry Then ' User chose Yes. MsgBox "recycle SAP transaction (Green Arrow back) to " & sStartingTransaction Else lDataRow = lLastRow ' End If ' ----------------------------------------------------------------------- End Sub