Earlier, I posted about using ADO to query a select number of columns from a spreadsheet. It used the CopyFromRecordset method to get the data in the spreadsheet. In this example, I change the CommandText (SQL) property of an existing QueryTable to show the data.
First, I used Data – Get External Data – New Database Query to bring in some data from the spreadsheet. I selected the ID, Category, and the first two date columns using the Query Wizard.
Then I ran the following code which builds a new SQL statement just like the previous example. Except this time I don’t Execute a new Command into a new Recordset, I simply change the CommandText property of the QueryTable on Sheet1 and Refresh. I got this result
And the code
Sub DynamicColumnsQT()
Dim adCn As ADODB.Connection
Dim adRs As ADODB.Recordset
Dim sCon As String
Dim sSql As String
Dim i As Long
Const lLAST As Long = 2
‘Get the whole table
‘Note the accent graves and double-double quotes
sCon = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _
“C:Documents and SettingsdickMy DocumentsTestAdo.xls;” & _
“Extended Properties=”“Excel 8.0;HDR=Yes;IMEX=1”“;”
sSql = “SELECT * FROM ‘Sheet1$’;”
Set adCn = New ADODB.Connection
adCn.Open sCon
Set adRs = adCn.Execute(sSql)
‘Build new sql string
sSql = “SELECT Category, “
For i = (adRs.Fields.Count – lLAST) To (adRs.Fields.Count – 1)
sSql = sSql & “‘” & adRs.Fields(i).Name & “‘, “
Next i
sSql = Left$(sSql, Len(sSql) – 2)
sSql = sSql & ” FROM ‘Sheet1$’;”
adRs.Close ‘close the whole table recordset
adCn.Close
‘Here’s the new part
Sheet1.QueryTables(1).CommandText = sSql
Sheet1.QueryTables(1).Refresh
Set adRs = Nothing: Set adCn = Nothing
End Sub
Dim adCn As ADODB.Connection
Dim adRs As ADODB.Recordset
Dim sCon As String
Dim sSql As String
Dim i As Long
Const lLAST As Long = 2
‘Get the whole table
‘Note the accent graves and double-double quotes
sCon = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _
“C:Documents and SettingsdickMy DocumentsTestAdo.xls;” & _
“Extended Properties=”“Excel 8.0;HDR=Yes;IMEX=1”“;”
sSql = “SELECT * FROM ‘Sheet1$’;”
Set adCn = New ADODB.Connection
adCn.Open sCon
Set adRs = adCn.Execute(sSql)
‘Build new sql string
sSql = “SELECT Category, “
For i = (adRs.Fields.Count – lLAST) To (adRs.Fields.Count – 1)
sSql = sSql & “‘” & adRs.Fields(i).Name & “‘, “
Next i
sSql = Left$(sSql, Len(sSql) – 2)
sSql = sSql & ” FROM ‘Sheet1$’;”
adRs.Close ‘close the whole table recordset
adCn.Close
‘Here’s the new part
Sheet1.QueryTables(1).CommandText = sSql
Sheet1.QueryTables(1).Refresh
Set adRs = Nothing: Set adCn = Nothing
End Sub
Dick,
I believe that the present URL to the previously post is incorrect. When I click on the result is “Page not found.”.
Kind regards,
Dennis
Good post!
I think it may be better to use “Select top 1 from …” to get the fields name than “Select * “.
Hi Dick. I’m getting the error ‘User-defined type not defined
Whoops, pushed enter accidentally.
I’m getting the compile error ‘User-defined type not defined’ when I run this code.
I’m using Excel 2007, so my steps for creating the original table differ slightly…I used Data/From other sources/From Microsoft Query, chose the excel files* option, selected the appropriate workbook, then pulled the same columns into the query as you did. Works fine. Should mention that the source workbook was created in Excel 2007 but saved as an Excel 2003 workbook.
Obviously I’ve amended your code with the address of my particular file, given I can’t seem to access C:Documents and SettingsdickMy DocumentsTestAdo.xls from here :-)
Any idea what I’m missing here?
And now for something completely different….
For a large set of numbers, I would be tempted to:
1. “Twist” the data into a pivot table compatible data set (and then dates could stay as dates)
2. Read up (or write up) to SQL
3. Read down into a SQL-linked pivot table
Much more complex (sophisticated? :-)), but more suited to a scenario where you are reading from multiple source sheets.
Thanks Dennis – fixed.
amolin: Good point. If you have a large dataset, that would speed it up considerably.
Jeff: User-defined type not defined = reference not set. In the VBE, go to Tools – References, and scroll down to Microsoft ActiveX Data Objects 2.7 Library and check it. When you try to use ADODB.Connection or ADODB.Recordset, VBA doesn’t know what you’re talking about so it thinks you’re trying to use a user-defined type and that you forgot to set up the type.
With the reference set, mine looks like this:
Good idea Alex. I never thought of normalizing the data as an interim step, but I like it. I’d like to see it done to see how difficult it would be.
Dick,
I’ll send along an example of my Twist routine in a day or so.
I would suggest this approach to obtain the last 2 columns:
With Sheets(1).QueryTables.Add(“ODBC;DSN=Excel-bestanden;DBQ=E:OF\__daily dose 001.xls;DriverId=790”, Range(“A1”))
.CommandText = “SELECT * FROM ‘E:OF\__daily dose 001’.’Sheet1$'”
.Refresh False
End With
Do Until sheets(1).cells(1,1) <> “”
DoEvents
Loop
sq = Sheets(1).Cells(1, 1).CurrentRegion
With Sheets(1).QueryTables(1)
.CommandText = “SELECT ‘” & sq(1, UBound(sq, 2) – 1) & “‘, ‘” & sq(1, UBound(sq, 2)) & “‘ FROM ‘E:OF\__daily dose 001’.’Sheet1$'”
.Refresh False
End With
End sub
Hans, that’s great. Thanks.