list of sheets names in closed workbook

[Sebastien]

Hi,
I need to get a list of the sheets names and named ranged
in a closed workbook without opening the workbook.
How can I do this?

Actually, what I really need is to know whether or not
a sheet name and a named range exist for a particular
workbook. I ve been trying with formulas, e.g:
.range("A1").formula=rows('c:\seb\temp\[test.xls]Sheet3'!Name1)
but when the sheet does not exist, the Select Sheet
dialog box pops up. How to prevent this dialog box to be
displayed?

Thank you,
Sébastien

Sent via Deja.com http://www.deja.com/
Before you buy.

[Jake Marx]

Hi Sebastien,

You can use ADO and ADOX to get a list of the sheetnames and named ranges in
a closed workbook. The following routine should get you started:

Dim cn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim t As ADOX.Table

Set cn = New ADODB.Connection
cn.Open "Provider=MSDASQL.1;Data Source=Excel Files;" _
& "Initial Catalog=D:\report.xls"
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn

For Each t In cat.Tables
Debug.Print t.Name
Next t
Set cat = Nothing
cn.Close
Set cn = Nothing

To use this, you must set references to "Microsoft ActiveX Data Objects 2.x
Library" and "Microsoft ADO Ext. 2.x for DDL and Security". If the table
ends in a dollar sign ("$"), that means it is a sheetname. Not all named
ranges will appear in the list - only ones that could possibly represent a
"table" of some type (i.e. no single row ranges will be returned).

You should be able to take this code and wrap it in a function (and include
some error handling) that will give you the flexibility you are looking for.

Regards,
Jake Marx

"Sebastien" <sebas...@emp.pdx.edu> wrote in message
news:90eg5q$kmv$1...@nnrp1.deja.com...

[Sebastien]

Excellent! Thank you, Jake!

Sébastien

In article <OygGiYYXAHA.303@cppssbbsa05>,