VBA ActiveSheet and ActiveWorkbook are empty - problem with opening Excel-CodePudding

I ama fresh starter in VBA, coming over from Python. I am trying to open Excel and use information in multiple sheets at different times to automate a Word document. Judging by the status of the local variables throughout debugging, Excel seems to launch successfully and the Workbook object is also assigned successfully. However, when the "DB Schedules" worksheet is activated, the ActiveSheet variable remains empty and fails to use the property "Name", instead throwing an "Object Required" error and jumping to the finally block. The path of the workbook and the name of the sheet are both correct, yet even before activating an arbitrary sheet the ActiveSheet variable is empty. The sheet is known to contain information, and I have tried with multiple files just to be sure. Below is the code to replicate the problem. Thanks!

Sub CompileReport()

    Dim XLInstance As Object
    Dim XLWorkbook As Object
    Dim XLPath As String

    XLPath = "C:\Users\SaracchiG\OneDrive - AECOM\Documents\M11 Jn7A\GC300 Certificates\" & _
    "Construction Compliance\Certificates Data Project_Name.xlsx"

    On Error Resume Next
    Set XLInstance = GetObject(, "Excel.Application")
    XLInstance.Quit
    If Err Then
        Set XLInstance = CreateObject("Excel.Application")
    End If
    On Error GoTo Finally
    Set XLWorkbook = XLInstance.Workbooks.Open(XLPath, ReadOnly:=True)
  
    'Testing it all works (doesn't!)
    XLWorkbook.Worksheets("DB Schedules").Activate
    MsgBox ActiveSheet.Name
    MsgBox ActiveWorkbook.Name
    'Do some stuff with different sheets

    Finally:
        XLWorkbook.Close
        XLInstance.Quit

End Sub

CodePudding user response：

You don't have to activate the sheet to work with it

    'Do some stuff with different sheets
    With XLWorkbook.Worksheets("DB Schedules")
           MsgBox .Name
           MsgBox .Parent.Name
    End With