QueryTable statement when converting from XL2003 to XL2007

[ebrooks54]

I have a macro (see code below) in an XL2003 spreadsheet that works fine until it is run in XL2007. It fails on the .Refresh statement with a run-time error 1004, ODBC failure.

I recorded a macro to try and recreate the code in XL2007, and noted that instead of using QueryTable.Add, XL2007 used ListObjects.Add.

I could live with that, but it is also inserting a header row, formatting the table style, and inserting filters. All I want it to do is retrieve the data rows.

I would appreciate any advice or references that will explain the difference between XL2003 and XL2007 and the way it handles QueryTable.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=Excel Files;DBQ=" & CurDir & "\EbTemp.xls;DefaultDir=" & CurDir & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A2"))
        
        .Sql = Array( _
        "SELECT SourceData.PubDiv, SourceData.PrdType, SourceData.Author, SourceData.Title, SourceData.ISBN, SourceData.MSDue, SourceData.Whse, SourceData.`On-Sale`, SourceData.Format, SourceData.Price, Source" _
        , _
        "Data.Quantity, SourceData.`Edr/PrMgr`, SourceData.Pages, SourceData.Txin, SourceData.Cvin, SourceData.Trim, SourceData.Spine, SourceData.BkWght, SourceData.Cnpk, SourceData.`OS-Mnth`" & Chr(13) & "" & Chr(10) & "FROM `Eb" _
        , _
        "Temp`.SourceData SourceData" & Chr(13) & "" & Chr(10) & _
        "WHERE SourceData.PrdType IN ('077','078','07B','045','076')" & Chr(13) & "" & Chr(10) & _
        "ORDER BY SourceData.`OS-Mnth`, SourceData.Title")
        .FieldNames = False
        .RefreshStyle = xlInsertDeleteCells
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .HasAutoFormat = False
        .BackgroundQuery = True
        '.TablesOnlyFromHTML = True
        .Refresh BackgroundQuery:=False
        .SavePassword = True
        .SaveData = True
    End With

 

[SkipVought]

Hi,

2007 has some significant changes.

I would record your macro from scratch.

You will notice that the Worksheet Object now has a ListObjects Collection and the ListObject Object has a QueryTable Object

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ebrooks54]

Skip,

Thanks very much for the feedback. I am looking at re-recording the macro, but I am not immediately seeing how to stop the macro from doing more than I want. XL2007 seems intent on inserting headings, activating filters, and applying a table-style.

All I want it to do is retreive the data rows, and leave everything else alone.

 

[SkipVought]

That is now what you get. It is a ListObject.

If you want just the data, you can convert to range (view the TOOLS Group in the DESIGN tab), but you loose the QueryTable.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]