Display all "Open" workbook and worksheet names when they're Dim'd as Object

[polarisking]

I'm out of my depth here. I'm opening 5 workbooks dim'd as Objects (the code lives in an Access module, that's why they're objects). All but one of the workbooks have one sheet. One workbook has 4 sheets.

I start the macro with an Excel.Application object and end the macro quitting the Excel.Application object.

For each workbook, I set it to a wbXXXX variable. For each worksheet, I set it to a wsXXXX variable. I can easily access wbXXXX.name and wsXXXX.name.

Interestingly (because I'm ignorant), when I open (workbooks.open) the workbooks they don't physically activate in Excel, but I can write to them as if they were visible.

I want to verify that all my wbXXXX and wsXXXX variables are assigned correctly. See the line in the code below "Here's the Problem"

Dim xlApp           As Object

Dim rowsCompass     As Long
Dim rowsDUpVal      As Long
Dim rowsDUpReinv    As Long
Dim rowsDUpIdxLvl   As Long
Dim rowsDUpIdxVal   As Long
Dim rowsDUpPD       As Long
Dim rowsIdxLvl      As Long
Dim rowsFairVal     As Long
Dim colsCompass     As Long
Dim colsDUpVal      As Long
Dim colsDUpReinv    As Long
Dim colsDUpIdxLvl   As Long
Dim colsDUpIdxVal   As Long

Dim colsDUpPD       As Long
Dim colsIdxLvl      As Long
Dim colsFairVal     As Long

Dim ws              As Object
Dim wsDUpVal        As Object
Dim wsDUpReinv      As Object
Dim wsDUpIdxLvl     As Object
Dim wsDUpIdxVal     As Object
Dim wsCompass       As Object
Dim wsIdxLvl        As Object
Dim wsFairVal       As Object

Dim wb              As Object
Dim wbCompass       As Object
Dim wbDUp           As Object
Dim wbDUpPD         As Object
Dim wbIdxLvl        As Object
Dim wbFairVal       As Object
'==============================================================================
Sub mainPAATest()

    Set xlApp = CreateObject("Excel.Application")
'==============================================================================
'Open files and set references
 
    Set wbDUp = xlApp.Workbooks.Open(Range("FN_DUp"))
    Set wsDUpVal = xlApp.Sheets("Valuation")
    colsDUpVal = wsDUpVal.Cells(1, wsDUpVal.Columns.Count).End(xlToLeft).Column
    rowsDUpVal = wsDUpVal.Cells(wsDUpVal.Rows.Count, "A").End(xlUp).Row
 
    Set wsDUpReinv = xlApp.Sheets("Reinvestment")
    colsDUpReinv = wsDUpReinv.Cells(1, wsDUpReinv.Columns.Count).End(xlToLeft).Column
    rowsDUpReinv = wsDUpReinv.Cells(wsDUpReinv.Rows.Count, "A").End(xlUp).Row
 
    Set wsDUpIdxLvl = xlApp.Sheets("Index Level - Daily")
    colsDUpIdxLvl = wsDUpIdxLvl.Cells(1, wsDUpIdxLvl.Columns.Count).End(xlToLeft).Column
    rowsDUpIdxLvl = wsDUpIdxLvl.Cells(wsDUpIdxLvl.Rows.Count, "A").End(xlUp).Row
 
    Set wsDUpIdxVal = xlApp.Sheets("Index Value")
    colsDUpIdxVal = wsDUpIdxVal.Cells(1, wsDUpIdxVal.Columns.Count).End(xlToLeft).Column
    rowsDUpIdxVal = wsDUpIdxVal.Cells(wsDUpIdxVal.Rows.Count, "A").End(xlUp).Row
     
    'ThisWorkbook.Activate
    Set wbDUpPD = xlApp.Workbooks.Open(Range("FN_DUpPD"))
 
    'ThisWorkbook.Activate
    Set wbCompass = xlApp.Workbooks.Open(Range("FN_Compass"))
    Set wsCompass = Sheets(1)
    colsCompass = wsCompass.Cells(1, wsCompass.Columns.Count).End(xlToLeft).Column
    rowsCompass = wsCompass.Cells(wsCompass.Rows.Count, "A").End(xlUp).Row
    DailyFile = ActiveWorkbook.Name
 
    'ThisWorkbook.Activate
    Set wbIdxLvl = xlApp.Workbooks.Open(Range("FN_IdxLvl"))
    Set wsIdxLvl = Sheets(1)
    colsIdxLvl = wsIdxLvl.Cells(1, wsIdxLvl.Columns.Count).End(xlToLeft).Column
    rowsIdxLvl = wsIdxLvl.Cells(wsIdxLvl.Rows.Count, "A").End(xlUp).Row
 
    'ThisWorkbook.Activate
    Set wbFairVal = xlApp.Workbooks.Open(Range("FN_FairVal"))
    Set wsFairVal = Sheets(1)
    colsFairVal = wsFairVal.Cells(2, wsFairVal.Columns.Count).End(xlToLeft).Column
    rowsFairVal = wsFairVal.Cells(wsFairVal.Rows.Count, "A").End(xlUp).Row
    FairValueFile = ActiveWorkbook.Name
'==============================================================================
'Here's the Problem
 
    For Each wb In Workbooks
        Debug.Print wb.Name
        For Each ws In wb.Worksheets
            Debug.Print "  " & ws.Name
        Next ws
    Next wb
'==============================================================================
'Close files
    wbDUp.Activate
    wbDUp.Close Savechanges:=False
 
    wbDUpPD.Activate
    wbDUpPD.Close Savechanges:=False
 
    wbCompass.Activate
    wbCompass.Close Savechanges:=False
 
    wbFairVal.Activate
    wbFairVal.Close Savechanges:=False
 
    wbIdxLvl.Activate
    wbIdxLvl.Close Savechanges:=False
'==============================================================================

    xlApp.Quit
    MsgBox "Done"
 
End Sub

It's only displaying the physically open workbooks (the macro workbook, PERSONAL.XLSB) and their respective sheets.

Any ideas?

 

[Marc L]

It seems you just forgot xlapp. ! Compare with codelines opening workbooks …​

​

I hope at least at the end of the procedure you do not forget to release each object variable !​

As often this kind of variable may be useless …​

​

 

[polarisking]

Marc, thank you for your response and solution.

I'm using xlapp. because this is running from an ACCESS VBA module. The workbooks are "open" and accessible, but they don't open in the traditional Excel macro sense. Any idea why? I can put checkpoints in the macro and get workbook and worksheet names, I can verify that when I write to the worksheets the data is there, but I just can't see the worksheet in view - make sense? Anyway I can see them open while the macro runs?

 

[Chihiro]

Do you mean that you want see the workbooks on screen for visual check?
If so, may be try setting xlApp.Visible = True.

Or try using wbDUp.Windows(1).Visible = True.

 

[polarisking]

Your first suggestion was PERFECT! Thank you so much!