Problem: The 2nd loop of For j fails on the line of code where I "Test for which line accrual is on 66-71"
Amt = session.FindById("wnd[0]/usr/lbl[" & CStr(g) & ",11]").Text
This was working a few weeks ago but my IT and our SAP consultant advise nothing has changed..
Error: Run-time error '619'. The control could not be found by id
Attempts: I have tried a few error trappings, included in my code below, but nothing works.
I have done a lot of research but come up blank. I've also re-recorded the macro and nothing has changed.. I'm lost and looking for help!
edit
Alternate Reslution: How can I get all items/amounts/string on this ROW
session.FindById("wnd[0]/usr/lbl[11]").Text - row 11
end edit
Code:
Option Explicit
Sub SAP_TPM_Payment()
'Declare Variables
Dim wkb As Workbook
Dim wks_TPM As Worksheet
Dim LR_TPM_A As Long, LR_TPM_E As Long, LR_DeletedSku As Long, AccAmt As Long, LR_Copy As Long, LR_clearing As Long, PayEntries As Long, PayCount As Long
Dim Response As VbMsgBoxResult, Response2 As VbMsgBoxResult
Dim WSHshell, proc
Dim wkb2 As String, wkb2_fname As String
Dim wkb2_name As String
Dim dblStartTime As Double 'time elapsed counter
Dim strMinutesElapsed As String
Dim i As Long, j As Long, k, n As Long, o As Long, p As Long, c As Long
Dim Amt, Amt1, Amt2, Amt3, Amt4, Amt5, Amt6
Dim LR_TPM_J As Long, Line, AccRow
Dim Status, sku, SAP_Acc, SAP_Pay, ClearNo, SAP_Accrual, PayAmt, Customer
Dim Pcheck1, Pcheck2
Dim CustomerName
Dim SapGuiAuto
Dim SAPApp As GuiApplication
Dim SAPCon As GuiConnection
Dim session As GuiSession
'Set Variables
Set wkb = ThisWorkbook
Set wks_TPM = wkb.Sheets("TPM Payment")
'Timer
dblStartTime = Timer
'Speed up code
NeedForSpeed
'Start Code
If wks_TPM.Range("Q2") = "" Then
MsgBox "No claim no. - exiting sub"
Exit Sub
End If
wks_TPM.Range("H" & Cells(Rows.Count, "I").End(xlUp).Row + 1) = Application.UserName
wks_TPM.Range("H" & Cells(Rows.Count, "I").End(xlUp).Row + 2) = Date
'Gets unique Accruals from col 'A', copies to col 'E'
If wks_TPM.Range("A4") = "" Then
wks_TPM.Range("E3:F3").Value = wks_TPM.Range("A3:B3").Value
Else:
LR_TPM_A = wks_TPM.Range("A" & Rows.Count).End(xlUp).Row
wks_TPM.Range("A2:A" & LR_TPM_A).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wks_TPM.Range("E2"), Unique:=True
End If
'Sums unique Accruals from col 'B', copies to col 'F'
LR_TPM_E = wks_TPM.Range("E" & Rows.Count).End(xlUp).Row
For i = 3 To LR_TPM_E
wks_TPM.Range("F" & i).Value = Application.SumIf(wks_TPM.Range("A:A"), wks_TPM.Range("E" & i), wks_TPM.Range("B:B"))
Next i
'Checks if SAP is open
On Error GoTo ErrRef
Response = MsgBox("Are you logged into SAP?" & vbCrLf & "" & vbCrLf & "Click 'Yes' if you are already logged into SAP" & vbCrLf & "Click 'No' to log into SAP" & vbCrLf & "Click 'Cancel' will exit the macro", vbYesNoCancel, "SAP Login Query")
If Response = vbNo Then
Set WSHshell = CreateObject("WScript.Shell")
Set proc = WSHshell.Exec("C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe")
Response2 = MsgBox("Are you logged into SAP?" & vbCrLf & "" & vbCrLf & "Click 'Ok' once you have logged into SAP" & vbCrLf & "Click 'Cancel' will exit the macro", vbOKCancel, "SAP Login Query")
If Response2 = vbCancel Then
CreateObject("WScript.Shell").PopUp "Exiting macro...", 1, "SAP Login Query"
Exit Sub
End If
ElseIf Response = vbCancel Then
CreateObject("WScript.Shell").PopUp "Exiting macro...", 1, "SAP Login Query"
Exit Sub
End If
On Error GoTo 0
Set SapGuiAuto = GetObject("SAPGUI") 'Get the SAP GUI Scripting object
Set SAPApp = SapGuiAuto.GetScriptingEngine 'Get the currently running SAP GUI
Set SAPCon = SAPApp.Children(0) 'Get the first system that is currently connected
Set session = SAPCon.Children(0) 'Get the first session (window) on that connection
For j = 3 To LR_TPM_E
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/n"
LR_TPM_J = wks_TPM.Range("J" & Rows.Count).End(xlUp).Row
SAP_Accrual = wks_TPM.Range("E" & j).Value
wks_TPM.Range("I" & LR_TPM_J + 1).Value = SAP_Accrual
session.FindById("wnd[0]").Maximize
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nVBO2"
session.FindById("wnd[0]").sendVKey 0
session.FindById("wnd[0]/usr/ctxtRV13A-KNUMA_BO").Text = SAP_Accrual
session.FindById("wnd[0]").sendVKey 0
'Confirms accrual is still active and not closed
Status = session.FindById("wnd[0]/usr/ctxtKONA-BOSTA").Text
If Status = "" Then
wks_TPM.Range("N" & LR_TPM_J + 1) = "Open"
Else
wks_TPM.Range("J" & LR_TPM_J + 1 & ":O" & LR_TPM_J + 1) = "Closed"
GoTo NextAccrual:
End If
'Confirms accrual is for the correct customer
Customer = session.FindById("wnd[0]/usr/txtKURGV-NAME1").Text
CustomerName = Split(Customer)(UBound(Split(Customer)))
If CustomerName = wks_TPM.Range("Q3") Then
wks_TPM.Range("Q4") = "Rebate Recipient matches claim"
Else
wks_TPM.Range("Q4") = "Rebate Recipient doesn't match claim"
GoTo NextAccrual:
End If
'Sales Volume (scrape accruals remaining)
session.FindById("wnd[0]").sendVKey 17
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:01"))
Dim Cust As String
Cust = session.FindById("wnd[0]/usr/lbl[3,9]").Text
Dim CustPos As Long
CustPos = InStr(Cust, "a")
Debug.Print Cust
Debug.Print CustPos
''Test for which line amt is on
'Accrual sometimes on row 66-71, code for possibilities
Dim g
For g = 66 To 71
On Error GoTo Next_g:
Amt = session.FindById("wnd[0]/usr/lbl[" & CStr(g) & ",11]").Text
If IsEmpty(Amt) = False Then
If Amt <> "" Then
'Amt = session.FindById("wnd[0]/usr/lbl[" & CStr(g) & ",11]").Text
Debug.Print Amt & CStr(g)
'On Error GoTo 0
Exit For
End If
Else
Debug.Print CStr(g) & "nope"
End If
Next_g:
'On Error GoTo 0
Next g
' On Error Resume Next
' Amt1 = session.FindById("wnd[0]/usr/lbl[66,11]").Text
' If Err.Number <> 0 Then
' Err.Clear
' End If
' Amt2 = session.FindById("wnd[0]/usr/lbl[67,11]").Text
' Amt3 = session.FindById("wnd[0]/usr/lbl[68,11]").Text
' Amt4 = session.FindById("wnd[0]/usr/lbl[69,11]").Text
' Amt5 = session.FindById("wnd[0]/usr/lbl[70,11]").Text
' Amt6 = session.FindById("wnd[0]/usr/lbl[71,11]").Text
' On Error GoTo 0
'' 'Accrual sometimes on row 66-71, code for possibilities
'' On Error GoTo Handler1:
'' Amt1 = session.FindById("wnd[0]/usr/lbl[66,11]").Text
''Waypoint1:
'' On Error GoTo Handler2:
'' Amt2 = session.FindById("wnd[0]/usr/lbl[67,11]").Text
''Waypoint2:
'' On Error GoTo Handler3:
'' Amt3 = session.FindById("wnd[0]/usr/lbl[68,11]").Text
''Waypoint3:
'' On Error GoTo Handler4:
'' Amt4 = session.FindById("wnd[0]/usr/lbl[69,11]").Text
''Waypoint4:
'' On Error GoTo Handler5:
'' Amt5 = session.FindById("wnd[0]/usr/lbl[70,11]").Text
''Waypoint5:
'' On Error GoTo Handler6:
'' Amt6 = session.FindById("wnd[0]/usr/lbl[71,11]").Text
'' On Error GoTo 0
'AccAmt used for payment
AccAmt = LR_TPM_J + 1
''''''''''
'Amt Code'
''''''''''
'If IsEmpty(Amt) = False And Amt <> "" Then
Line = LR_TPM_J + 1
AccRow = -1
ReRun:
'********************
Dim ScrollBarPosOrig As Long, ScrollBarPosNew As Long, ScrollBarPosUpdate As Long
ScrollBarPosOrig = session.FindById("wnd[0]/usr").VerticalScrollbar.Position
session.FindById("wnd[0]/usr").VerticalScrollbar.Position = 2
ScrollBarPosUpdate = session.FindById("wnd[0]/usr").VerticalScrollbar.Position
If ScrollBarPosUpdate = ScrollBarPosOrig Then
GoTo ScrollBarNone:
Else
GoTo ScrollBar:
End If
ScrollBarNone:
For k = 10 To 100 Step 2 '1024 to act like infinity
sku = session.FindById("wnd[0]/usr/lbl[3," & CStr(k) & "]").Text
If sku = "" Then
MsgBox "Exit For"
End If
wks_TPM.Range("J" & Line) = Split(sku)(UBound(Split(sku))) 'gets last numbr from string, which is the sku/material
wks_TPM.Range("I" & Line) = SAP_Accrual
Amt = session.FindById("wnd[0]/usr/lbl[" & CStr(g) & "," & CStr(k + 1) & "]").Text
If Right(Amt, 1) = "-" Then 'Converts to number
wks_TPM.Range("K" & Line).Value = Left(Amt, Len(Amt) - 1)
Else: wks_TPM.Range("K" & Line).Value = "0" 'Zero $$ is the amount is a debit (overpaid accrual)
End If
Line = Line + 1
AccRow = AccRow + 1
Next k
ScrollBar:
session.FindById("wnd[0]/usr").VerticalScrollbar.Position = 0
ScrollBarPosOrig = session.FindById("wnd[0]/usr").VerticalScrollbar.Position
For k = 2 To 1000 Step 2 '1024 to act like infinity
sku = session.FindById("wnd[0]/usr/lbl[3,10]").Text
If sku = "" Then
MsgBox "Exit For"
End If
wks_TPM.Range("J" & Line) = Split(sku)(UBound(Split(sku))) 'gets last numbr from string, which is the sku/material
wks_TPM.Range("I" & Line) = SAP_Accrual
Amt = session.FindById("wnd[0]/usr/lbl[" & CStr(g) & ",11]").Text
If Right(Amt, 1) = "-" Then 'Converts to number
wks_TPM.Range("K" & Line).Value = Left(Amt, Len(Amt) - 1)
Else: wks_TPM.Range("K" & Line).Value = "0" 'Zero $$ is the amount is a debit (overpaid accrual)
End If
Line = Line + 1
AccRow = AccRow + 1
On Error Resume Next
SAP_Acc = session.FindById("wnd[0]/usr/lbl[3,13]").Text
Debug.Print Split(SAP_Acc)(UBound(Split(SAP_Acc)))
On Error GoTo 0
Debug.Print SAP_Accrual 'testing
If Split(SAP_Acc)(UBound(Split(SAP_Acc))) = CStr(SAP_Accrual) Then
GoTo EndOfAccrual:
End If
session.FindById("wnd[0]/usr").VerticalScrollbar.Position = k
Next k
EndOfAccrual:
On Error GoTo 0
'Copy delete sku formula
wks_TPM.Range("O2").Copy
wks_TPM.Range("O" & LR_TPM_J + 1 & ":O" & Cells(Rows.Count, "I").End(xlUp).Row).PasteSpecial Paste:=xlPasteFormulas
'Only calculates changed/updated cells
Application.Calculate
'Format cells
wks_TPM.Range("O3:O" & Cells(Rows.Count, "I").End(xlUp).Row).HorizontalAlignment = xlCenter
'If any sku is "marked for deletion", change amount to 0
LR_DeletedSku = wks_TPM.Range("O" & Rows.Count).End(xlUp).Row
For n = LR_TPM_J + 1 To LR_DeletedSku
If wks_TPM.Range("O" & n) = "X" Then
wks_TPM.Range("K" & n) = "0"
End If
Next n
'Copy amount to be paid formula
wks_TPM.Range("M2").Copy
wks_TPM.Range("M" & LR_TPM_J + 1 & ":M" & Cells(Rows.Count, "J").End(xlUp).Row).PasteSpecial Paste:=xlPasteFormulas
'Only calculates changed/updated cells
Application.Calculate
'Format cells
wks_TPM.Range("M3:M" & Cells(Rows.Count, "J").End(xlUp).Row).NumberFormat = "#,##0.00_)"
wks_TPM.Range("M3:M" & Cells(Rows.Count, "J").End(xlUp).Row).HorizontalAlignment = xlRight
'Go back one screen (equivalent of "F3")
session.FindById("wnd[0]").sendVKey 3
'Payment of Accrual
PayAmt = wks_TPM.Range("M" & AccAmt).Value
PayEntries = Line - AccAmt
If PayAmt <> "No" Then 'Pay Claim
session.FindById("wnd[0]").sendVKey 24 'Pay (equivalent of "Shift+F12")
'Test if there is a scrollbar
ScrollBarPosOrig = session.FindById("wnd[0]/usr").VerticalScrollbar.Position
ScrollBarPosNew = ScrollBarPosOrig + (o + 1)
ScrollBarPosUpdate = session.FindById("wnd[0]/usr").VerticalScrollbar.Position
If ScrollBarPosUpdate = ScrollBarPosOrig Then
GoTo ScrollBarNonePay:
Else
GoTo ScrollBarPay:
End If
ScrollBarNonePay:
For o = 0 To PayEntries
PayAmt = wks_TPM.Range("M" & AccAmt).Value
session.FindById("wnd[0]/usr/sub:SAPMV13A:3007/txtKONPD-BZWRT[" & CStr(o) & ",48]").Text = PayAmt
session.FindById("wnd[0]").sendVKey 0
AccAmt = AccAmt + 1
PayEntries = PayEntries - 1
If PayEntries = 0 Then
GoTo SavePayment:
End If
Next o
ScrollBarPay:
For o = 0 To PayEntries
PayAmt = wks_TPM.Range("M" & AccAmt).Value
session.FindById("wnd[0]/usr/sub:SAPMV13A:3007/txtKONPD-BZWRT[0,48]").Text = PayAmt
session.FindById("wnd[0]").sendVKey 0
AccAmt = AccAmt + 1
PayEntries = PayEntries - 1
If PayEntries = 0 Then
GoTo SavePayment:
End If
session.FindById("wnd[0]/usr").VerticalScrollbar.Position = (o + 1)
Next o
Else: GoTo NextAccrual:
End If
SavePayment:
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:01"))
'Saves payment
session.FindById("wnd[0]").sendVKey 11
session.FindById("wnd[0]").sendVKey 0
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:02"))
'Press "Enter" to go back into agreement
session.FindById("wnd[0]").sendVKey 0
'Selects rebate paymnts -> rebate docs
session.FindById("wnd[0]/mbar/menu[3]/menu[3]").Select
'Selects partial settelment
session.FindById("wnd[1]").sendVKey 0
'Selects first line
session.FindById("wnd[2]/usr/cntlCUSTOM_CONTAINER/shellcont/shell").SelectItem "000000000001", "COL0"
session.FindById("wnd[2]/usr/cntlCUSTOM_CONTAINER/shellcont/shell").ClickLink "000000000001", "COL0"
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:02"))
'Selects Sales Doc -> Change
session.FindById("wnd[0]/mbar/menu[0]/menu[1]").Select
'Need to wait a couple of seconds for SAP to save - needs 2 secs, tried 1 sec but fails
Application.Wait (Now + TimeValue("00:00:02"))
'Updated date to today
session.FindById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\02/ssubSUBSCREEN_BODY:SAPMV45A:4415/ctxtVBKD-FKDAT").Text = Format(Date, "dd.mm.yyyy")
'Claim no.
session.FindById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/txtVBKD-BSTKD").Text = wks_TPM.Range("Q2")
session.FindById("wnd[0]").sendVKey 0
'Selects Sales Document -> Billing -> Save
session.FindById("wnd[0]/mbar/menu[0]/menu[8]").Select
'Get Clearing Doc no.
ClearNo = session.FindById("wnd[0]/sbar").Text
If Split(ClearNo, Chr$(32))(1) Like "*6*" Then
wks_TPM.Range("L" & AccAmt - 1) = Split(ClearNo, Chr$(32))(1)
End If
'Enter thru "warning"
session.FindById("wnd[0]").sendVKey 0
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:02"))
'Get Clearing Doc no. take 2 as enter thru errors producs different status bar text
ClearNo = session.FindById("wnd[0]/sbar").Text
If wks_TPM.Range("L" & AccAmt - 1) = "" Then
If Split(ClearNo, Chr$(32))(1) Like "*6*" Then
wks_TPM.Range("L" & AccAmt - 1) = Split(ClearNo, Chr$(32))(1)
End If
End If
session.FindById("wnd[0]").sendVKey 11
'Need to wait a second for SAP to catch up
Application.Wait (Now + TimeValue("00:00:02"))
NextAccrual:
Next j
'Copy clearing doc no.
For c = 3 To LR_TPM_A
wks_TPM.Range("C" & c).Value = Application.SumIf(wks_TPM.Range("I:I"), wks_TPM.Range("A" & c), wks_TPM.Range("L:L"))
Next c
Dim pathTPM_temp As String
Dim fnameTPM_temp As String
'Enter VB05N trans to get payment details
session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nVA05N"
session.FindById("wnd[0]").sendVKey 0
session.FindById("wnd[0]/usr/ctxtSKUNNR-LOW").Text = ""
session.FindById("wnd[0]/usr/txtPBSTKD").Text = wks_TPM.Range("Q2")
session.FindById("wnd[0]").sendVKey 8
'Export to 'local file'
session.FindById("wnd[0]").sendVKey 45
session.FindById("wnd[1]/usr/subSUBSCREEN_STEPLOOP:SAPLSPO5:0150/sub:SAPLSPO5:0150/radSPOPLI-SELFLAG[1,0]").Select
session.FindById("wnd[1]/tbar[0]/btn[0]").Press
pathTPM_temp = "C:\Users\adamsmit\Desktop\"
session.FindById("wnd[1]/usr/ctxtDY_PATH").Text = pathTPM_temp
fnameTPM_temp = "export.xls"
session.FindById("wnd[1]/usr/ctxtDY_FILENAME").Text = fnameTPM_temp
session.FindById("wnd[1]").sendVKey 11
'Need to wait a couple of seconds for SAP to save - needs 2 secs, tried 1 sec but fails
Application.Wait (Now + TimeValue("00:00:01"))
Dim wkbTPM_temp As Workbook
Dim wksTPM_temp As Worksheet
'Open "export" file and filter for current payments
Set wkbTPM_temp = Workbooks.Open(FileName:=pathTPM_temp & fnameTPM_temp)
Set wksTPM_temp = Workbooks("export.xls").Worksheets("export")
'Format file
With wksTPM_temp
.Rows("5:5").EntireRow.Delete
.Rows("1:3").EntireRow.Delete
.Columns("A:A").EntireColumn.Delete
.AutoFilterMode = False
With .Range("A1:O1")
.AutoFilter
.AutoFilter Field:=2, Criteria1:=Environ("UserName")
.AutoFilter Field:=4, Criteria1:=Format(Date, "dd.mm.yyyy")
End With
End With
Debug.Print pathTPM_temp & fnameTPM_temp
With wkbTPM_temp
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "copy"
End With
Dim wksTPM_copy As Worksheet
Dim LR_hidden As Long
Set wksTPM_copy = Workbooks("export.xls").Worksheets("copy")
wksTPM_temp.Range("A1:" & wksTPM_temp.Range("K1").End(xlDown).Address).Copy wksTPM_copy.Range("A1")
'Get LR for copy to claims file below
LR_Copy = wksTPM_copy.Range("A" & Rows.Count).End(xlUp).Row
'Find Open Claims file to paste data into
Dim wbcount2 As Long
wbcount2 = Workbooks.Count
For i = 1 To wbcount2
If Workbooks(i).Path & "\" & Workbooks(i).Name Like "*" & wks_TPM.Range("Q2") & "*" Then
wkb2 = Workbooks(i).Path & "\" & Workbooks(i).Name
wkb2_fname = Workbooks(i).Path & "\" & Workbooks(i).Name
wkb2_name = Workbooks(i).Name
Debug.Print wkb2_name
Exit For
End If
Next i
Dim wkbClaim As Workbook
Dim wksClaim_clearing As Worksheet
Set wksClaim_clearing = Workbooks(wkb2_name).Worksheets("Clearing")
LR_clearing = wksClaim_clearing.Range("A" & Rows.Count).End(xlUp).Row
wksTPM_copy.Range("A2:K" & LR_Copy).Copy wksClaim_clearing.Range("A" & LR_clearing + 1)
'Kill temp "export" workbook
wkbTPM_temp.Close SaveChanges:=False
strMinutesElapsed = Format((Timer - dblStartTime) / 86400, "hh:mm:ss") 'stops timer - Determine how many seconds code took to run
MsgBox "This code ran successfully in " & strMinutesElapsed, vbInformation 'Msg box for elapsed time & Claims consldaited
ResetSpeed
Exit Sub
ErrRef: MsgBox ("Liar!!!" & vbCrLf & "" & vbCrLf & "Adam - 1" & vbCrLf & "You - 0")
ResetSpeed
Exit Sub
''Handler1: 'jump done, error handling is now disabled
'' Resume Waypoint1
''Handler2: 'jump done, error handling is now disabled
'' Resume Waypoint2
''Handler3: 'jump done, error handling is now disabled
'' Resume Waypoint3
''Handler4: 'jump done, error handling is now disabled
'' Resume Waypoint4
''Handler5: 'jump done, error handling is now disabled
'' Resume Waypoint5
''Handler6: 'jump done, error handling is now disabled
'' Resume Next
End Sub
