This Domain Is For Sell, Contact: admin@mrvsto.com
Apr 082010
 

Code:

Sub test()
    Dim cn As Object, cat As Object, tbl As Object, str$
    Set cn = CreateObject("ADODB.Connection")
    Set cat = CreateObject("ADOX.Catalog")
    Set tbl = CreateObject("ADOX.Table")
    cn.Open "dsn=excel files;dbq=" & ThisWorkbook.Path & "/test.xls"
    cat.ActiveConnection = cn
    For Each tbl In cat.Tables
        str = str & Replace(tbl.Name, "$", "") & vbCrLf
    Next
    MsgBox str
    cn.Close
    Set cn = Nothing
    Set cat = Nothing
    Set tbl = Nothing
End Sub

Notice: The names are arrayed in alphabetical order, but not from beginning to end in the workbook.


Hot:

● Use Microsoft Office 2007 like a Pro!
● Excel Expert User-Your One Stop Solution In Mastering Microsoft Excel
● 101 Secrets of a Microsoft Excel Addict
● Excel VBA Made Easy
● Doc2Txt
● Create Your Own Programming Language


  3 Responses to “Excel/VBA: Get worksheets’ names without opening the workbook”

  1. It would be helpful to see how to get the worksheet names in the same order they exist on the worksheet. I have been able to do so using DAO, but that is not working with XLSX files.

  2. Is there a way to not return named ranges and print ranges with this code?

    Thanks

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>