Solved

Query an Excel table using SQL from VBA

Posted on 2011-02-21
28
888 Views
Last Modified: 2012-05-11
Looking for a way to query a table that resides in an Excel worksheet using SQL-Select -from-where statements.
Not looking to connect to external sources such as Access or SQL Server...
Regards
Brian
0
Comment
Question by:canesbr
  • 15
  • 13
28 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946169
When you say table, do you mean a 2007-style Table, or just a sheet of data?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946200
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

Open 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.
0
 

Author Comment

by:canesbr
ID: 34946387
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946503
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"";"
...

Open in new window

for example.
0
 

Author Comment

by:canesbr
ID: 34946725
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

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946796
What error specifically and where are you running this from?
0
 

Author Comment

by:canesbr
ID: 34946853
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

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34946923
Which version of Excel are you running this in?
0
 

Author Comment

by:canesbr
ID: 34946974
2007
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947025
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.
0
 

Author Comment

by:canesbr
ID: 34947102
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947108
Have you tried Office Diagnostics?
0
 

Author Comment

by:canesbr
ID: 34947137
Nope.
How is that done?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947214
Under the Resources section in Excel Options. Sounds to me like your attempt at installing Off 2010 cocked something up.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:canesbr
ID: 34947310
Is that a technical term?
I ran Office Diagnostics - found 1 error and corrected it without disclosing what it was.
Continue to get error.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947348
Oops - I've just noticed your connection string is wrong. You are missing the "Data Source=" part off the start.
0
 

Author Comment

by:canesbr
ID: 34947401
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"";"

Open in new window

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947453
On that line?
0
 

Author Comment

by:canesbr
ID: 34947460
no, on the .open
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947473
Is the workbook already open? (it shouldn't be)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947495
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"";"

Open in new window

or if you have a header row:
.ConnectionString = "Data Source=" & strFileName & "; " & _
                    "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

Open in new window

0
 

Author Comment

by:canesbr
ID: 34947519
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947540
If DB15b was a sheet name, you need:
strQuery = "SELECT * FROM [DB15$]"

Open in new window


If you want the data in a worksheet, don't use GetRows, use something like:
Range("A2").Copyfromrecordset rst

Open in new window

0
 

Author Comment

by:canesbr
ID: 34947669
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34947737
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.
0
 

Author Comment

by:canesbr
ID: 34947947
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
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34949298
Did you change the HDR info in the connection?
0
 

Author Comment

by:canesbr
ID: 34956637
Perfecto
It works with the database workbook open or closed
It works even within the database workbook
Thanks
Regards
Brian
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now