List Worksheets/Named Ranges in Closed Excel Workbooks w/ADO

Post date: Dec 8, 2013 9:35:28 PM

Need to know available datasources in workbooks?

Worksheets and named ranges are recognized by ADO, OLEDB, ODBC as data sources for SQL. We can list those datasources using ADO's OpenSchema method. Here is simple function that returns all datasources for a workbook separated by commas. All worksheet names end in $.

Function GetSchema(sPath As String, sWorkbook As String) As String

' ?GetSchema(Thisworkbook.Path, thisworkbook.Name)

Dim cn As Object

Dim rs As Object

Set cn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & sPath & "\" & sWorkbook & ";" & _

"Extended Properties=Excel 8.0;"

Set rs = cn.OpenSchema(20) '20 =adSchemaTables

While Not rs.EOF

GetSchema = GetSchema & _

IIf(Trim(GetSchema) = "", "", ",") & rs("TABLE_NAME")

rs.movenext

Wend

rs.Close

cn.Close

End Function