Using Microsoft Excel Driver to Query Workbook

[Shreedhar]

Gurus,

Is it possible to query workbook without specifying worksheet name ?

This workbook contains only one sheet but the sheet name can vary at time hence the need to query workbook with out hard-coding its name.

Thanks for your time.

Appreciate your help

Regards, Shreedhar

 

[Debaser]

Not with ADO/DAO if that's what you are using. You can use ADOX to get a list of the tables in the workbook, which would include sheets and any static named ranges.

 

[Chihiro]

If it's Excel workbook. You could use ADO to read schema and get sheet name, then use it in query (works for 2007 and up, or 2003 with some updates).

Ex: To read Sheet/Table names and put it into active sheet.

Sub GetSheetName()
Dim cn As Object, oSch As Object
Dim strQuery As String, fisrtFile As String
Dim i As Integer

Set cn = CreateObject("ADODB.Connection")
firstFile = "C:\Test\Test.xlsx"
' Add IMEX=1; at end of connection string, if you know some column is alphanumeric mixed
' Or if you don't know if data type is consistent in a column
' It is safer to use IMEX=1; in general. Just note that imported data will be in text format
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & firstFile & ";" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""
    .CursorLocation = 3
    .Open
End With


Set oSch = cn.OpenSchema(20)

i = 2
While Not oSch.EOF
    If Right(oSch("TABLE_NAME"), 1) = "$" Or Right(oSch("TABLE_NAME"), 2) = "$'" Then
        Cells(i, 1) = oSch("TABLE_NAME")
        i = i + 1
    End If
        oSch.movenext
Wend

oSch.Close
cn.Close

End Sub

Note: Code has some unnecessary variables etc. Left over from my general purpose query code. You can get rid of them.

 

[Shreedhar]

Chihiro, Thanks for your code snippet, It works Brilliantly.

Just curious it is possible to use sheets internal id while querying workbook with single sheet.

Thanks Again.

Regards,
Shreedhar

If it's Excel workbook. You could use ADO to read schema and get sheet name, then use it in query (works for 2007 and up, or 2003 with some updates).

Ex: To read Sheet/Table names and put it into active sheet.

Sub GetSheetName()
Dim cn As Object, oSch As Object
Dim strQuery As String, fisrtFile As String
Dim i As Integer

Set cn = CreateObject("ADODB.Connection")
firstFile = "C:\Test\Test.xlsx"
' Add IMEX=1; at end of connection string, if you know some column is alphanumeric mixed
' Or if you don't know if data type is consistent in a column
' It is safer to use IMEX=1; in general. Just note that imported data will be in text format
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & firstFile & ";" & _
                          "Extended Properties=""Excel 12.0 Xml;HDR=Yes;"""
    .CursorLocation = 3
    .Open
End With


Set oSch = cn.OpenSchema(20)

i = 2
While Not oSch.EOF
    If Right(oSch("TABLE_NAME"), 1) = "$" Or Right(oSch("TABLE_NAME"), 2) = "$'" Then
        Cells(i, 1) = oSch("TABLE_NAME")
        i = i + 1
    End If
        oSch.movenext
Wend

oSch.Close
cn.Close

End Sub

Note: Code has some unnecessary variables etc. Left over from my general purpose query code. You can get rid of them.

 

[Shreedhar]

Not with ADO/DAO if that's what you are using. You can use ADOX to get a list of the tables in the workbook, which would include sheets and any static named ranges.

Thanks for your suggestion

 

[Chihiro]

Just curious it is possible to use sheets internal id while querying workbook with single sheet.

Unfortunately no. As best practice, I tend to name 1st sheet the same on almost all my workbooks. Either "Raw" or "Data" depending on how the workbook's data is generated (Raw, if straight export from external system. Data, if I used some transformation steps within Excel).