Excel imports SQL Data, but changes the order of 1 column once imported..

Question

I do an import data from a query I created in MS Query. Lets say the columns are in order A,B,C,D,E,F in the Query Editor. Once the I save the query, return data to Excel, the imported data has a new column order

A,B,C,F,D,E -- note the F column was moved where D was.

Any ideas on how to solve this issue?

Thanks guys. Assume variables are defined correctly and disregard what the code is trying to do if you want, the preserving part is not working

For Each wks In ThisWorkbook.Worksheets

  Set qt = wks.QueryTables(1)
  qt.PreserveColumnInfo = True
  qt.PreserveFormatting = True


        If wks.Name <> "Master" And wks.Name <> "Parameters" Then


        wks.Range("A2:A1000").EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)




        End If
    Next wks

Answer 1

There are two properties of the QueryTable object called PreserveColumnInfo and PreserveFormatting, which should help you out. (There's also AdjustColumnWidth, but I'm not sure if you need to worry about that one). You should be able to use code similar to the following to help preserve the column information:

Sub PreserveQueryTable()
    Dim ws As Worksheet
    Dim qt As QueryTable

    Set ws = ActiveSheet

    Set qt = ws.QueryTables(0) ' <== I am not sure if this should be a     '
                               '     0 or a 1. I think it is a 0.          '

    qt.PreserveColumnInfo = True
    qt.PreserveFormatting = True

End Sub