vendredi 25 juin 2021

Loop Stops due to an IF Statement

I created this script that allows me to change multiple dates within SAPs DEP - Production envirnoment VA02, and add an attachment (usually an email) as a proof of request.

To deal with the multiple and unexpected pop-up windows that may appear, I introduced an error handler. My present issue is that, once the order has had the date changed, the attachment added and the whole process saved, it seems that the script goes into the Error Handler for some reason and gets stuck there.

Sometimes a pop-up window appears when the process is completed. However, the EH should take care of it. It looks like the function goes through all the IFs written there even if it does not need to.

I know the problem is within the EH, however, I do not know what is the root cause. I am thinking it might be the way is structured. Anyone has an idea?

Sub Roller()
    
    Dim ws          As Worksheet
    Dim i           As Long
    Dim maxi        As String
    
    Set ws = Excel.ThisWorkbook.Worksheets("Roll")
    i = 2
    maxi = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    Do While i < maxi + 1
        
        On Error GoTo PopUpHandler
        
        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)
        
        If Cells(4, 10) = "YES" Then
        
            If Cells(2, 5) = "" Then
            
                        session.StartTransaction "va02" 'To directly Open a SAP transaction
                        session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = Cells(i, 1)
                        session.findById("wnd[0]").sendVKey 0
                        
                        session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/btnBT_HEAD").press
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16").Select
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9100/ctxtGX_VBAK-VDATU").Text = Cells(i, 2)
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9100/ctxtGX_VBAK-ZZWADAT").SetFocus
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9100/ctxtGX_VBAK-ZZWADAT").caretPosition = 0
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17").Select
                                    
                        session.findById("wnd[0]").sendVKey 11
                                    
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9200/tblSAPLZV444_PRIMARYTC_RC/ctxtGX_REASON_CODES-REASON_CODE[5,0]").Text = Cells(i, 3)
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9200/tblSAPLZV444_PRIMARYTC_RC/ctxtGX_REASON_CODES-REASON_CODE[5,0]").caretPosition = 4
                        session.findById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"
                        session.findById("wnd[0]/titl/shellcont/shell").SelectContextMenuItem "%GOS_PCATTA_CREA"
                        session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Cells(2, 7)
                        session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 4)
                        session.findById("wnd[1]/tbar[0]/btn[0]").press
                        session.findById("wnd[0]").sendVKey 11
            
            Else
                        session.StartTransaction "va02"        'To directly Open a SAP transaction
                        session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = Cells(i, 1)
                        session.findById("wnd[0]").sendVKey 0
                        session.findById("wnd[0]/usr/subSUBSCREEN_HEADER:SAPMV45A:4021/btnBT_HEAD").press
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16").Select
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\16/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9100/ctxtGX_VBAK-VDATU").Text = Cells(i, 2)
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17").Select
                        session.findById("wnd[0]").sendVKey 11
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9200/tblSAPLZV444_PRIMARYTC_RC/ctxtGX_REASON_CODES-REASON_CODE[5,0]").Text = Cells(i, 4)
                        session.findById("wnd[0]").sendVKey 0
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9200/tblSAPLZV444_PRIMARYTC_RC/ctxtGX_REASON_CODES-TAGGED_ITEMS[6,0]").Text = Cells(i, 5)
                        session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_HEAD/tabpT\17/ssubSUBSCREEN_BODY:SAPMV45A:4323/subCUSTOMER_SCREEN:SAPLZV444_PRIMARY:9200/tblSAPLZV444_PRIMARYTC_RC/txtGX_REASON_CODES-TAGGED_QTY[7,0]").Text = Cells(i, 6)
                        session.findById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"
                        session.findById("wnd[0]/titl/shellcont/shell").SelectContextMenuItem "%GOS_PCATTA_CREA"
                        session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Cells(2, 10)
                        session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 7)
                        session.findById("wnd[1]/tbar[0]/btn[0]").press
                        session.findById("wnd[0]").sendVKey 11
                        
                            If session.ActiveWindow.Name = "wnd[1]" Then
                            session.findById("wnd[1]/usr/btnZSPOP_PRIMARY-OPTION1").press        'Outstanding issues present
                            End If
                            
            End If
                
        Else
            
            session.StartTransaction "va02"        'To directly Open a SAP transaction
            session.findById("wnd[0]/usr/ctxtVBAK-VBELN").Text = Cells(i, 1)
            session.findById("wnd[0]").sendVKey 0
            session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/subSUBSCREEN_BUTTONS:SAPMV45A:4050/btnBT_MKAL").press
            session.findById("wnd[0]/mbar/menu[1]/menu[1]/menu[3]").Select
            session.findById("wnd[1]/usr/ctxtRV45A-S_ETDAT").Text = Cells(i, 2)
            session.findById("wnd[1]/usr/ctxtRV45A-S_EZEIT").SetFocus
            session.findById("wnd[1]/usr/ctxtRV45A-S_EZEIT").caretPosition = 0
            session.findById("wnd[1]/tbar[0]/btn[7]").press
            
            session.findById("wnd[0]/titl/shellcont/shell").PressContextButton "%GOS_TOOLBOX"
            session.findById("wnd[0]/titl/shellcont/shell").SelectContextMenuItem "%GOS_PCATTA_CREA"
            session.findById("wnd[1]/usr/ctxtDY_PATH").Text = Cells(2, 7)
            session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = Cells(i, 4)
            session.findById("wnd[1]/tbar[0]/btn[0]").press
            session.findById("wnd[0]").sendVKey 11
            session.findById("wnd[0]").sendVKey 11
            session.findById("wnd[1]/usr/tblSAPLZV01_IBERIATC_OTIFVAL/ctxtX_OTIFVAL-RCODE[3,0]").Text = Cells(i, 3)
            session.findById("wnd[1]/tbar[0]/btn[8]").press
            
        End If
        
        Range("H" & i) = "RDD updated And attachment added."
        
        i = i + 1
        
    Loop
    
    MsgBox "RDDs have been updated accordingly To the request."
    
    Exit Sub
    
PopUpHandler:
    
    If session.ActiveWindow.Name = "wnd[1]" Then
        session.findById("wnd[1]/tbar[0]/btn[0]").press        'Consider subsequent documents pop-up
    End If
    
        If session.ActiveWindow.Name = "wnd[1]" Then
        session.findById("wnd[1]/usr/btnZSPOP_PRIMARY-OPTION1").press        'Outstanding issues present
    End If
    
    If session.ActiveWindow.Name = "wnd[2]" Then
        session.findById("wnd[2]/tbar[0]/btn[0]").press        'Credit check (maximum % for open items exceeded pop-up
    End If
    
    If session.ActiveWindow.Name = "wnd[2]" Then
        session.findById("wnd[2]/tbar[0]/btn[0]").press        'Consider subsequent documents pop-up
    End If
        
    Resume
    
End Sub ```

Aucun commentaire:

Enregistrer un commentaire