Dynamic Columns in MS Query II

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
Posted in Uncategorized

10 thoughts on “Dynamic Columns in MS Query II

  1. 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

  2. Good post!

    I think it may be better to use “Select top 1 from …” to get the fields name than “Select * “.

  3. 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?

  4. 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.

  5. 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:

  6. I would suggest this approach to obtain the last 2 columns:

    Sub tst2()
      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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *