'Execute in SAP
If Not IsObject(SAPApp) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApp = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
Set Connection = SAPApp.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 SAPApp, "on"
End If
'Open Vim and apply parameters (inserting Doc Numbers)
session.findById("wnd[0]/tbar[0]/btn[12]").press
session.findById("wnd[0]/tbar[0]/btn[12]").press
't-code entered in bar
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n/opt/vim_analytics"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/btn%_S_XBLNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]/usr/btn%_S_LIFNR_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,0]").Text = Cells(3, 1).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").Text = Cells(4, 1).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,2]").Text = Cells(5, 1).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,3]").Text = Cells(3, 3).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,4]").Text = Cells(4, 3).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,5]").Text = Cells(5, 3).Value
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").SetFocus
session.findById("wnd[1]/usr/tabsTAB_STRIP/tabpSIVA/ssubSCREEN_HEADER:SAPLALDB:3010/tblSAPLALDBSINGLE/ctxtRSCSEL_255-SLOW_I[1,1]").caretPosition = 7
session.findById("wnd[1]").sendVKey 8
session.findById("wnd[0]").sendVKey 8
''''''The line below is the line causing the error'''''''
If IsError(session.findById("wnd[1]/tbar[0]/btn[0]").press) = True Then
'Export to Excel (Existing XXL Format)
VIM_Export:
session.findById("wnd[0]/usr/cntlCL_GRID/shellcont/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/cntlCL_GRID/shellcont/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/tbar[0]/btn[0]").press
'Select Export and Paste into Workbook
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Windows("Updated Auto Recon.xlsm").Activate
Sheets(5).Select
Range("A1").Select
ActiveSheet.Paste
'Clear Formatting
ThisWorkbook.ActiveSheet.Cells.ClearFormats
'Close temporary export sheet
Windows("Worksheet in Basis (1)").Activate
ActiveWindow.Close
'Move Reference to Column A; Move CoCode to Column B; Move Doc. ID to Column C; Move Document Status to Column D
Range("A1").End(xlToRight).Select
LastColumn = ActiveCell.Column
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "CoCd" Then
ActiveCell.Value = "CoCode"
ElseIf ActiveCell.Value = "Company Code" Then
ActiveCell.Value = "CoCode"
ElseIf ActiveCell.Value = "DOC Status" Then
ActiveCell.Value = "Document Status"
ElseIf ActiveCell.Value = "Document Id" Then
ActiveCell.Value = "Doc. Id"
ElseIf ActiveCell.Value = "Vendor Nam" Then
ActiveCell.Value = "Vendor Name"
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "Reference" Then
If ActiveCell.Column <> 1 Then
Columns(i).Select
Selection.Cut
Columns("A").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "CoCode" Then
If ActiveCell.Column <> 2 Then
Columns(i).Select
Selection.Cut
Columns("B").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "Doc. Id" Then
If ActiveCell.Column <> 3 Then
Columns(i).Select
Selection.Cut
Columns("C").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "Document Status" Then
If ActiveCell.Column <> 4 Then
Columns(i).Select
Selection.Cut
Columns("D").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "Vendor" Then
If ActiveCell.Column <> 5 Then
Columns(i).Select
Selection.Cut
Columns("E").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
For i = 1 To LastColumn
Cells(1, i).Select
If ActiveCell.Value = "Vendor Name" Then
If ActiveCell.Column <> 6 Then
Columns(i).Select
Selection.Cut
Columns("F").Insert Shift:=xlToRight
Else
End If
Else
End If
Next i
'Remove Confirmed Duplicate Status
i = Application.WorksheetFunction.CountIf(Columns("D:D"), "Confirmed Duplicate")
If i > 0 Then
LastRow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
Selection.AutoFilter
ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)).AutoFilter Field:=4, Criteria1:="Confirmed Duplicate", _
Operator:=xlAnd
Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.ShowAllData
Else
End If
'Remove Cancelled Status
i = Application.WorksheetFunction.CountIf(Columns("D:D"), "Cancelled")
If i > 0 Then
LastRow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
Selection.AutoFilter
ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn)).AutoFilter Field:=4, Criteria1:="Cancelled", _
Operator:=xlAnd
Rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlToLeft
ActiveSheet.ShowAllData
Else
End If
Application.ScreenUpdating = True
Application.ScreenUpdating = False
'Force Doc Id to convert to number
Range("C:C").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Application.ScreenUpdating = True
Application.ScreenUpdating = False
'Force Vendor # to convert to number
Range("E:E").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
'Sorts Reference number by most recent document status
LastRow = ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
Range("A2").Activate
ActiveWorkbook.Worksheets("VIM Export").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VIM Export").Sort.SortFields.Add Key:=Range( _
"C2:C" & LastRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("VIM Export").Sort.SortFields.Add Key:=Range( _
"A2:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("VIM Export").Sort
.SetRange Range(Cells(1, 1), Cells(LastRow, LastColumn))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
Else
session.findById("wnd[1]/tbar[0]/btn[0]").press
End If
Bookmarks