ODBC Connection Wrong Field Order

[bbranco]

I am working on a simple project, so I thought, to pull data from and ODBC data source into Excel.

I have been a programmer for too many years to mention, but new with VBA and Excel.

I have this query, and the columns are not in this order

SELECT product.num As titem, product.description, SPACE(1) as description1, product.price, SPACE(1) AS filler1 FROM `fishbowl_demo`.`product`

[TABLE="width: 615"]
<tbody>[TR]
[TD="class: xl64, width: 123"]description[/TD]
[TD="class: xl64, width: 123"]description1[/TD]
[TD="class: xl64, width: 123"]price[/TD]
[TD="class: xl64, width: 123"]filler1[/TD]
[TD="class: xl64, width: 123"]titem[/TD]
[/TR]
</tbody>[/TABLE]

That is the order they are appearing in.

I also have this in the VBA, to save the sheet and it does work, but that sheet also has the wrong order.

Option Explicit


Sub ProcessData()
Dim MainWBook As String
Dim NewWB As Workbook
Dim Ash As Worksheet
Dim attachment1 As String
Dim LastRowInSheet As Long


With Application
.EnableEvents = False
.ScreenUpdating = False
.PreserveColumnInfo = True
.PreserveFormatting = True
End With

Application.EnableCancelKey = xlDisabled
MainWBook = ThisWorkbook.Name


Workbooks(MainWBook).Sheets("Data").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False

Set NewWB = Workbooks.Add(xlWBATWorksheet)


Workbooks(MainWBook).Sheets("Data").Copy After:=Workbooks(NewWB.Name).Sheets(1)

Workbooks(NewWB.Name).Sheets("Data").ListObjects("Table_Query_from_SQL_TEMP").Unlink

attachment1 = "C:\Users\bbranco\Documents\Parfum1-Fishbowl\CA-Items\items-" & Format(Date, "MM-DD-YYYY") & ".xlsx"

Application.DisplayAlerts = False
Workbooks(NewWB.Name).SaveAs attachment1, xlOpenXMLWorkbook
Workbooks(NewWB.Name).Close
End Sub



Any help would be greatly appreciated!!!

 

[Fazza]

if you move the fields into the order you want, on the worksheet, that might solve it

IIRC, once the query is in the worksheet, the field order in the SQL makes no difference.
The field order in the SQL should be reflected in the worksheet when creating the query table.