Spot the mistake....

[PaulSpell]

I want a list of each sheet in a workbook. Can someone tell me where I have gone wrong here?:

Sub ListSheets()

Dim exlObj As Object
Dim exlSht As Object

Set exlObj = CreateObject("Excel.Application")
Set exlSht = CreateObject("Excel.Sheet")

exlObj.Visible = True

With exlObj
.workbooks.Open "C:\SomeXLDoc.xls"
End With

For Each exlSht In exlObj.Worksheets
MsgBox exlSht.Name
Next exlSht

Set exlObj = Nothing


End Sub

 

[Dugantrain]

Isn't the correct object reference "Excel.Worksheet" rather than "Excel.Sheet"?

 

[The_Doc_Man]

two comments.

1. First CreateObject is good. Second one creates a blank workbook (unless Excel was already open elsewhere in your code). I would not bother with the second CreateObject. When you open the workbook with the .Open method, you can identify that workbook from the object.documents collection using a shortcut object-variable, then just iterate through the elements.

2. You can set the object to Nothing. However, as a matter of future readability, I would make it a point to always explicitly close anything I had previously opened BEFORE I reclaim its space. Saves loads of confusion later. But that's just me.

 

[PaulSpell]

The_Doc_Man, can you elaborate? How do I do that exactly?