GetSchema: List Worksheets and Names

Need to know available data sources in workbooks?

Worksheets and named ranges are recognized by ADO, OLEDB, ODBC as data sources for SQL. We can list those data sources using ADO's GetSchema method. Here is simple function that returns all data sources separated by commas for a workbook, even a closed workbook. 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