Solved: Loop through all worksheets via Access

[CreganTur]

I use the following code, in Access, to get the name of the first worksheet in an Excel workbook. This code, in its current state, will only pull the first worksheet name:

Note: strFilepath is the file path of the chosen workbook- it is fed to this code via a choose file dialog box.

'Capture Worksheet name of selected Excel file
'This code only pulls the name of the first worksheet name
Dim bIsWorksheet As Boolean
Dim objConnection As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim lIndex As Long
Dim szConnect As String

'Establish parameters for connection with selected data source
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strFilepath & ";Extended Properties=Excel 8.0;"
Set objConnection = New ADODB.Connection
objConnection.Open szConnect '<<<Create data connection with selected Excel file
'Get list (recordset) of all worksheet names
Set rsData = objConnection.OpenSchema(adSchemaTables)
strSheetName = rsData.Fields("TABLE_NAME").Value '<<<captures name of 1st worksheet

Can anyone help me figure out how to adapt this code so that it will loop through all of the worksheets in the selected workbook?

[OBP]

I just created This and it works for me.

Private Sub Command0_Click()
Dim excelname As String, AppExcel As New Excel.Application, Wkb As Workbook, Wksh As Worksheet
Dim obj As AccessObject, dbs As Object, tempTable As String, spaceIn As Integer
Dim count As Integer
On Error GoTo Errorcatch

excelname = "c:\Access\Survey_Final.xls"
Set Wkb = AppExcel.Workbooks.Open(excelname)
For Each Wksh In Wkb.Worksheets
MsgBox Wksh.Name
Next Wksh

Wkb.Close
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing
Exit Sub

Errorcatch:
MsgBox Err.Description
If IsNull(Wkb) = False Then Exit Sub
Wkb.Close
AppExcel.Quit
Set Wkb = Nothing
Set AppExcel = Nothing

End Sub

[CreganTur]

Thanks OPB- that method works really well.

But, thanks to the law of unintended consequences, it brings up a new error in the rest of my code I'm using this with. I'll probably start a new Thread once I track down what's going on.

Note to future users: you must set reference to Microsoft Excel to use this method.