Code without label gives Compile error: Label not defined

Question

I'm trying to run a SAP GUI script using a button in Excel, I have the script created in a way that it takes info from Excel to use in SAP, that script works great, if I open it, it runs smoothly but I can't manage to add that script to Excel command button.

This is the original script that works:

If Not IsObject(application) Then
   Set SapGuiAuto  = GetObject("SAPGUI")
   Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.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 application, "on"
End If
session.findById("wnd[0]").maximize


'REM ADDED BY EXCEL *************************************

Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(,"Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 to objSheet.UsedRange.Rows.Count

COL1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
COL2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


'REM ADDED BY EXCEL *************************************


session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00028"
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00028"
session.findById("wnd[0]/usr/ctxtVBRK-VBELN").text = Col1
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").select
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).selected = true
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").setFocus
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[6]").press
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").key = "1"
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").setFocus
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/usr/chkNAST-DELET").selected = true
session.findById("wnd[0]/usr/ctxtNAST-LDEST").text = "LOCALNEW"
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").text = Col2
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").setFocus
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").caretPosition = 8
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press



'REM FINALIZATION CONTROL CHECK ************************

aux=col1 & " " & col2
CreateObject("WScript.Shell").run("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
next
msgbox "Process Completed"

'REM FINALIZATION CONTROL CHECK ************************

If I add this code to Excel VBA it doesn't work, so I followed this post and I changed 'application' to 'GuiApplication' and did a few changes based on the code that was given on that post answer.

After doing the changes I ended up with this code:

Private Sub CommandButton1_Click()

If Not IsObject(GuiApplication) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set GuiApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = GuiApplication.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 GuiApplication, "on"
End If

If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP

Set aw = session.ActiveWindow()
aw.findById("wnd[0]").Maximize



'REM ADDED BY EXCEL *************************************

Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 To objSheet.UsedRange.Rows.Count

col1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
col2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


'REM ADDED BY EXCEL *************************************

On Error GoTo Err_Description
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00028"
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00028"
session.findById("wnd[0]/usr/ctxtVBRK-VBELN").Text = col1
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").Select
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).Selected = True
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").SetFocus
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[6]").press
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").Key = "1"
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").SetFocus
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/usr/chkNAST-DELET").Selected = True
session.findById("wnd[0]/usr/ctxtNAST-LDEST").Text = "LOCALNEW"
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").Text = col2
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").SetFocus
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").caretPosition = 8
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press

'REM FINALIZATION CONTROL CHECK ************************
aux = col1 & " " & col2
CreateObject("WScript.Shell").Run ("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
Next
MsgBox "Process Completed"
'REM FINALIZATION CONTROL CHECK ************************

         
End Sub

When I hit Debug > Compile VBA project, an error pops saying

Compile error: Label not defined

and highlights a line on the code Set objExcel = GetObject(, "Excel.Application")

Any ideas of what I'm doing wrong? I'm sure I'm doing a lot of things wrong, I'm learning. I just need to be able to run that script with a click of a button on Excel.

Please read this article on how to debug. It will change your life: http://www.cpearson.com/Excel/DebuggingVBA.aspx — HackSlash, Feb 01 '22 at 00:03
Does this answer your question? [VBA "Compile Error: Label not defined"](https://stackoverflow.com/questions/31030162/vba-compile-error-label-not-defined) — Sandra Rossi, Mar 20 '22 at 08:13

score 1 · Accepted Answer · answered Jan 31 '22 at 19:23

Getting the Excel object is for automation of Excel from a different host. When your host IS Excel then the Application object contains the Excel.Application object.

There are many problems with this code. If you put Option Explicit at the top you'll find that you haven't declared all of your variables. When you have, they have no types.

You've got some error trapping that uses GoTo but no matching line labels. You need to have line labels for each GoTo and you also need to decide what to do in those error events. Notice the line labels at the very end of the sub. They end in a colon :. Without error handling code here the sub will just end. You normally want to at least warn the user of the error. If you're feeling adventurous you can give feedback on how to fix the problem or attempt to fix the problem programmatically.

I have mended your code so that it compiles but I have no idea if it does what you want:

Option Explicit
Dim SAPGuiApp As Object
Dim SapGuiAuto As Object
Dim Connection As Object
Dim SAP_session As Object
Dim WScript As Object
Dim aw As Object

Private Sub CommandButton1_Click()

    If Not IsObject(SAPGuiApp) Then
        Set SapGuiAuto = GetObject("SAPGUI")
        Set SAPGuiApp = SapGuiAuto.GetScriptingEngine
    End If
    
    If Not IsObject(Connection) Then
        Set Connection = SAPGuiApp.Children(0)
    End If
    
    If Not IsObject(SAP_session) Then
        Set SAP_session = Connection.Children(0)
    End If
    
    If IsObject(WScript) Then
        WScript.ConnectObject SAP_session, "on"
        WScript.ConnectObject SAPGuiApp, "on"
    End If

    If (Connection.Children.Count > 1) Then GoTo Err_TooManySAP

    Set aw = SAP_session.ActiveWindow()
    aw.findById("wnd[0]").Maximize

    Dim objSheet As Worksheet
    Set objSheet = ActiveSheet
    Dim i As Integer
    For i = 2 To objSheet.UsedRange.Rows.Count

        Dim col1 As String
        col1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
        Dim col2 As String
        col2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2

        On Error GoTo Err_Description
        With SAP_session
            .findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00028"
            .findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00028"
            .findById("wnd[0]/usr/ctxtVBRK-VBELN").Text = col1
            .findById("wnd[0]").sendVKey 0
            .findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").Select
            .findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).Selected = True
            .findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").SetFocus
            .findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").caretPosition = 0
            .findById("wnd[0]/tbar[1]/btn[6]").press
            .findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").Key = "1"
            .findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").SetFocus
            .findById("wnd[0]/tbar[0]/btn[11]").press
            .findById("wnd[0]/usr/chkNAST-DELET").Selected = True
            .findById("wnd[0]/usr/ctxtNAST-LDEST").Text = "LOCALNEW"
            .findById("wnd[0]/usr/txtNAST-TDRECEIVER").Text = col2
            .findById("wnd[0]/usr/txtNAST-TDRECEIVER").SetFocus
            .findById("wnd[0]/usr/txtNAST-TDRECEIVER").caretPosition = 8
            .findById("wnd[0]/tbar[0]/btn[3]").press
            .findById("wnd[0]/tbar[0]/btn[11]").press
            .findById("wnd[0]/tbar[0]/btn[3]").press
        End With

    
        'REM FINALIZATION CONTROL CHECK ************************
        Dim aux As String
        aux = col1 & " " & col2
        CreateObject("WScript.Shell").Run ("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
    Next
    MsgBox "Process Completed"
    'REM FINALIZATION CONTROL CHECK ************************

    ' Put error handling here
Err_Description:

Err_TooManySAP:

End Sub

Yes I was sure that there will be more than one error on my code, is hard when your teacher is google and youtube, i tried your code and yes it compiles but it doesnt run, I tried using the original code and just changing the "application' for gui application and it compiles but doesnt run either — Rosenfeld_62, Jan 31 '22 at 19:57
I tried with a different code, this time I didn't change it too much from the original script that currently works, I added the final part of the code that you shared with me and it worked. — Rosenfeld_62, Jan 31 '22 at 20:08

score -1 · Answer 2 · answered Jan 31 '22 at 20:07

@HackSLash

I tried with a different code, this time I didn't change it too much from the original script that currently works, I added the final part of the code that you shared with me and it worked.

Private Sub CommandButton1_Click()

 If Not IsObject(GuiApplication) Then
   Set SapGuiAuto = GetObject("SAPGUI")
   Set GuiApplication = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(Connection) Then
   Set Connection = GuiApplication.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 GuiApplication, "on"
End If
session.findById("wnd[0]").maximize


'REM ADDED BY EXCEL *************************************

Dim objExcel
Dim objSheet, intRow, i
Set objExcel = GetObject(, "Excel.Application")
Set objSheet = objExcel.ActiveWorkbook.ActiveSheet

For i = 2 To objSheet.UsedRange.Rows.Count

col1 = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
col2 = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


'REM ADDED BY EXCEL *************************************


session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").selectedNode = "F00028"
session.findById("wnd[0]/usr/cntlIMAGE_CONTAINER/shellcont/shell/shellcont[0]/shell").doubleClickNode "F00028"
session.findById("wnd[0]/usr/ctxtVBRK-VBELN").Text = col1
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/mbar/menu[2]/menu[0]/menu[3]").Select
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3").getAbsoluteRow(0).Selected = True
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").SetFocus
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/lblDV70A-STATUSICON[0,0]").caretPosition = 0
session.findById("wnd[0]/tbar[1]/btn[6]").press
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").Key = "1"
session.findById("wnd[0]/usr/tblSAPDV70ATC_NAST3/cmbNAST-NACHA[3,0]").SetFocus
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/usr/chkNAST-DELET").Selected = True
session.findById("wnd[0]/usr/ctxtNAST-LDEST").Text = "LOCALNEW"
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").Text = col2
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").SetFocus
session.findById("wnd[0]/usr/txtNAST-TDRECEIVER").caretPosition = 8
session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press

    
        'REM FINALIZATION CONTROL CHECK ************************
        Dim aux As String
        aux = col1 & " " & col2
        CreateObject("WScript.Shell").Run ("cmd /c @echo %date% %time% " & aux & " >> C:\SCRIPT\PlOrCreationLog.txt")
    Next
    MsgBox "Process Completed"
    'REM FINALIZATION CONTROL CHECK ************************

    ' Put error handling here
Err_Description:

Err_TooManySAP:

End Sub

but do you know why? What did you learn? – HackSlash Feb 01 '22 at 00:02 — HackSlash, Feb 01 '22 at 00:02

Code without label gives Compile error: Label not defined

2 Answers2