worksheets list in workbook without opening workbook in VBA

[davesyd1970]

Hi.

Background:
I am designing a menu which will allow users to navigate to an excel workbook (windows explorer dialog). Once a workbook is selected, I need to display all the worksheets in the workbook. These selections will be stored in a database. Assume that the menu will be in an Access database.

I don't really want to open the Excel workbook in the background to get a list of worksheets, as these workbooks have a number of VBA routines which get run using the workbook Open event. I could potentially play with the workbook security settings before opening it to disable macros running when loading a new workbook.

The question is does anyone have a clever solution on this. I tried investigating DDE to see whether this might give me the functionality but could not find the code I needed. Ideally, there is a simple way to get a list of worksheets in a workbook without having to open up the workbook(and a list of range names in those worksheets would be a bonus).

Thanks for reading this.

David

[Bob Phillips]

Sub GetSheetNames()
Dim oConn As Object
Const sFilename As String = "C:\test\test.xls"
Dim oCat As Object
Dim tbl As Object
Dim iRow As Long
Dim sConnString As String
Dim sTableName As String
Dim cLength As Integer
Dim iTestPos As Integer
Dim iStartpos As Integer

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConnString
Set oCat = CreateObject("ADOX.Catalog")
Set oCat.ActiveConnection = oConn

iRow = 1
For Each tbl In oCat.Tables
sTableName = tbl.Name
cLength = Len(sTableName)
iTestPos = 0
iStartpos = 1
'Worksheet name with embedded spaces enclosed by single quotes
If Left(sTableName, 1) = "'" And Right(sTableName, 1) = "'" Then
iTestPos = 1
iStartpos = 2
End If
'Worksheet names always end in the "$" character
If Mid$(sTableName, cLength - iTestPos, 1) = "$" Then
MsgBox Mid$(sTableName, iStartpos, cLength - (iStartpos + iTestPos))
iRow = iRow + 1
End If
Next tbl

oConn.Close
Set oCat = Nothing

End Sub

[davesyd1970]

Hi xld

Thank you for replying to the post..

I will certainly give this a go. If I were going to use the menu in an excel add in, the code I believe would still work? The only thing necessary I imagine would be microsoft Jet OLE.

Do you know if a machine that is installed with Excel 2007 or 2010 (and NOT microsoft access) whether these drivers are automatically installed? or is the OLE something that is provided with windows?

Regards
David

[Bob Phillips]

My understanding is that the Jet engine comes with Windows, not Access. I cannot verify that though as I have both. I don't know if it comes with Windows 7 but I can check later. If not, I believe it is freely available.