Query an Excel table using SQL from VBA

When you say table, do you mean a 2007-style Table, or just a sheet of data?

If you mean just a worksheet:

Sub GetData()
   ' Sample demonstrating how to return a recordset from a workbook
   ' requires a reference to the Microsoft ActiveX Data Objects 2.n library
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim strFileName As String
   Dim varData As Variant
   
   strFileName = "C:\blah.xls"
   
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strFileName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "SELECT * FROM [Sheet name here$]"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
   ' dump array of data into variable
   varData = rst.GetRows
   rst.Close
   Set rst = Nothing
'   cn.Close
   Set cn = Nothing
End Sub

Select allOpen in new window

If you mean a 2007-style table, then you are out of luck unless the workbook is open, and there are memory leak issues with that.

It is just rows of data (not a Table or List)
I did a reference to 2.8
I tried the code as below and got Automation Error
Also, how to get data out of varData?
Regards
Brian
 

Sub GetData()
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim strFileName As String
   Dim varData As Variant
   strFileName = "C:\Users\Owner\Documents\My\BDC\Excel\DataBase3.xlsm"
   Set cn = New ADODB.Connection
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strFileName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "SELECT * FROM DB15 where key = 131110"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText 'Runtime error Automation error
   varData = rst.GetRows 'how to get rows out of here and into a worksheet?
   rst.Close
   Set rst = Nothing
   Set cn = Nothing
End Sub

Select allOpen in new window

For a 2007 format workbook, you need a different connection:

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=C:\ADO source.xlsx;" & _
                    "Extended Properties=""Excel 12.0;HDR=NO"";"
...

Select allOpen in new window

for example.

Get Automation error at the first .open statement
Regards
Brian

Sub GetData()
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
   Dim strFileName As String
   Dim varData As Variant
   strFileName = "C:\Users\Owner\Documents\My\BDC\Excel\DataBase3a.xlsm"
   Set cn = New ADODB.Connection
   With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = strFileName & "; " & _
                    "Extended Properties=""Excel 12.0;HDR=NO"";"
      .Open
   End With
   strQuery = "SELECT * FROM DB15" ' where key = 131110"
   Set rst = New ADODB.Recordset
   rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText 
   varData = rst.GetRows 'how to get rows out of here and into a worksheet?
   rst.Close
   Set rst = Nothing
   Set cn = Nothing
End Sub

Select allOpen in new window

What error specifically and where are you running this from?

Run-time error '-2147467259 (80004005)': Could not find installable ISAM.
(OMG that's my amex number!)
Running from Module1 in Excel Workbook.
I tried both in a new empty wb with the target wb open and closed. And in the target "db workbook" itself.
Regards
Brian

Which version of Excel are you running this in?

2007

Did you do a full install of 2007? You haven't installed any other versions subsequently? The provider should come with 2007 by default, but you might try searching for the latest version of MDAC on the MS site.

A failed attempt at installing Excel 2010 trial. Which caused installation of Office 2010 to fail.
I downloaded  (MDAC) 2.8 but I continue to get same error.

Have you tried Office Diagnostics?

Nope.
How is that done?

Under the Resources section in Excel Options. Sounds to me like your attempt at installing Off 2010 cocked something up.

Is that a technical term?
I ran Office Diagnostics - found 1 error and corrected it without disclosing what it was.
Continue to get error.

Oops - I've just noticed your connection string is wrong. You are missing the "Data Source=" part off the start.

Now I get "Run-time error Cannot update. Database or object if read-only."

.ConnectionString = "Data Source= " & strFileName & "; " & _
                    "Extended Properties=""Excel 12.0;HDR=NO"";"

Select allOpen in new window

On that line?

no, on the .open

Is the workbook already open? (it shouldn't be)

Also, if it's .xlsm, it shoudl really be as follows (though it doesn't make any difference in my experience):

.ConnectionString = "Data Source=" & strFileName & "; " & _
                    "Extended Properties=""Excel 12.0 Macro;HDR=NO"";"

Select allOpen in new window

or if you have a header row:

.ConnectionString = "Data Source=" & strFileName & "; " & _
                    "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

Select allOpen in new window

Ok, no more errors
I got an error if I used the sheet name; but it completed when I used a named range.
So
How does one get the results out of varData?
Regards
Brian

If DB15b was a sheet name, you need:

strQuery = "SELECT * FROM [DB15$]"

Select allOpen in new window

If you want the data in a worksheet, don't use GetRows, use something like:

Range("A2").Copyfromrecordset rst

Select allOpen in new window

Ok the Range A2 worked, but it didn't output the field names (in row 1 in the external database sheet).
I tried the following strQuery ="SELECT Seq, AccountValue FROM Base3 where seq <'500'" and got the Run-time error "No value given for one or more required parameters."
Regards
Brian

You have to write the field names yourself.
You also have to enclose the sheet name in square brackets and end it with a $ sign.

The [DB15$] is not working for me. THe named range is working for "SELECT * FROM Base3"
But if I try "SELECT combo, seq FROM Base3" where combo and seq are in row1 of Base3, I get error "No value given for one or more required parameters" on the rst.Open line.
Regards
Brian

Perfecto
It works with the database workbook open or closed
It works even within the database workbook
Thanks
Regards
Brian