TransferSpreadsheet - Output column order not matching query order (1 Viewer)

[Richard Horne]

I'm using the TransferSpreadsheet function to output a query to a spreadsheet. The output works great except the ordering of the columns isn't matching the order specified in the saved query. Does anyone know of a way to maintain the correct order?

To give you an example, I expect a query to be ordered 1,2,3,4,5,6,7,8,9 whereas the outputted spreadsheed is something like 1,2,3,8,5,7,6,9,4.

'Build our query
str_sql = "SELECT tbl_purchase_orders.purchase_order_id AS PO, tbl_purchase_orders.sales_order_ref AS SO, tbl_orders.customers_ref AS [Cust Ref], tbl_companies.company_name AS Supplier, " _
& " tbl_purchase_orders.order_date AS [Date], tbl_purchase_order_details.delivery_required_by AS [Due Date], tbl_purchase_order_details.item_number AS [Line Item], " _
& " tbl_purchase_order_details.customer_item_reference AS [Item No], tbl_purchase_order_details.product_code AS [Product Code], tbl_products.tl_number AS [TL Number], " _
& " tbl_item_types.item_type_name AS [Item Type], tbl_secondary_item_type.secondary_item_type_name AS [Secondary Type], tbl_products.design AS Design, tbl_products.sort_colour AS Colour, " _
& " Nz([price_sterling],0) AS Price, tbl_purchase_order_details.quantity AS Qty, Nz([SumOfqty_approved],0) AS Appr, [quantity]-[Appr] AS Rem, [rem]*[price] AS [Value]" _
& " FROM tbl_orders INNER JOIN (purchase_orders_marked_off_totalled RIGHT JOIN (tbl_staff INNER JOIN (((((tbl_purchase_orders INNER JOIN tbl_purchase_order_details ON " _
& " tbl_purchase_orders.purchase_order_id = tbl_purchase_order_details.purchase_order_id) INNER JOIN tbl_companies ON tbl_purchase_orders.company_id = tbl_companies.company_id) " _
& " INNER JOIN tbl_products ON tbl_purchase_order_details.product_code = tbl_products.product_code) INNER JOIN tbl_item_types ON tbl_products.item_type_id = tbl_item_types.item_type_id) " _
& " INNER JOIN tbl_secondary_item_type ON tbl_products.secondary_item_type_id = tbl_secondary_item_type.secondary_item_type_id) ON tbl_staff.staff_id = tbl_purchase_orders.ccp_id) " _
& " ON purchase_orders_marked_off_totalled.item_id = tbl_purchase_order_details.detail_id) ON tbl_orders.sales_order_id = tbl_purchase_orders.sales_order_ref " _
& " WHERE (((tbl_purchase_orders.complete) = 0) And ((tbl_purchase_order_details.item_complete) = 0) And ((tbl_purchase_orders.purchase_order_cancelled) = 0) " _
& " AND (tbl_purchase_orders.ccp_id = " & dbl_current_employee & "))" _
& " ORDER BY tbl_purchase_orders.purchase_order_id, tbl_purchase_order_details.item_number;"

'Save the query to our temp saved query
CurrentDb.QueryDefs("purchase_orders_live_temp").SQL = str_sql

'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True

 

[arnelgp]

what if you Re-Create the query:

Dim qd As DAO.Querydef
'Delete the Query
On Error Resume Next
DoCmd.DeleteObject acQuery, "purchase_orders_live_temp"

'Save the query to our temp saved query
        With CurrentDb
            Set qd = .CreateQueryDef(""purchase_orders_live_temp"", str_sql)
            .QueryDefs.Append qd
            Set qd = Nothing
            .QueryDefs.Refresh
        End With
        Application.RefreshDatabaseWindow
On Error Goto 0
'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True

 

[jdraw]

What do your 1,2,3,4,5,6,7,8,9 represent?
You are ordering the data based on

ORDER BY tbl_purchase_orders.purchase_order_id, tbl_purchase_order_details.item_number

 

[Richard Horne]

What do your 1,2,3,4,5,6,7,8,9 represent?
You are ordering the data based on

ORDER BY tbl_purchase_orders.purchase_order_id, tbl_purchase_order_details.item_number

The numbers 1-9 represent the columns. I was just keeping it short and simple.

So if the query is "SELECT col1, col2, col3 FROM TABLEa" you would get col1, col2, col3 in that order in the resulting spreadsheet.
What I'm saying is when I use TransferSpreadsheet the resulting spreadsheet is not ordered correctly.

 

[Richard Horne]

what if you Re-Create the query:

Dim qd As DAO.Querydef
'Delete the Query
On Error Resume Next
DoCmd.DeleteObject acQuery, "purchase_orders_live_temp"

'Save the query to our temp saved query
        With CurrentDb
            Set qd = .CreateQueryDef(""purchase_orders_live_temp"", str_sql)
            .QueryDefs.Append qd
            Set qd = Nothing
            .QueryDefs.Refresh
        End With
        Application.RefreshDatabaseWindow
On Error Goto 0
'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True

This makes sense, though I'm getting an invalid operation error on the line .QueryDefs.Append.qd

 

[arnelgp]

remove the dot (.) after the Append, it should be a space:

.QueryDefs.Append qd

 

[Richard Horne]

remove the dot (.) after the Append, it should be a space:

.QueryDefs.Append qd

Sorry that was a typo at my end. I get that error with a space.

 

[Richard Horne]

Fixed that error and got all of your code working but the column ordering is still incorrect. :\

 

[arnelgp]

maybe Reset the Column Order of the query using VBA.

Dim qd As DAO.QueryDef
Dim fld As DAO.Field
'Delete the Query
On Error Resume Next
DoCmd.DeleteObject acQuery, "purchase_orders_live_temp"

'Save the query to our temp saved query
With CurrentDb
    Set qd = .CreateQueryDef("purchase_orders_live_temp", str_sql)
    
    'reset column orders
    For Each fld In qd.Fields
        fld.Properties("ColumnOrder") = 0
    Next
    .QueryDefs.Append qd
    Set qd = Nothing
    .QueryDefs.Refresh
End With
Application.RefreshDatabaseWindow
On Error GoTo 0
'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True

 

[Richard Horne]

I tried resetting the ColumnOrder earlier a different way and had no joy. Tried your method and it didn't work either, unfortunately. Thanks for trying, though, I really appreciate it.

 

[arnelgp]

maybe you can "dump" the records of the Query to a Temp table and use the Temp table for your Export.

 

[Pat Hartman]

At some point, you manually reordered the columns and Access is remembering that. Just copy the SQL string and paste it into a NEW querydef and delete the original. The TransferSpreadsheet will then work as you expect.

 

[Pat Hartman]

Thanks for the like richard but if my suggestion solved the problem, it is better to use your words. That is much more helpful to others who find this thread.

 

[Richard Horne]

Thanks for the like richard but if my suggestion solved the problem, it is better to use your words. That is much more helpful to others who find this thread.

Hi Pat,

I'm just in the middle of testing and will be reporting back when I've confirmed either way. My Like was because your explanation made perfect sense - I definitely did re-order the columns. Thanks.

 

[Richard Horne]

Me testing is being made all the more difficult because now Excel is giving me the error:

Excel cannot open the file because the file format or file extension is not valid.

Even though this has been working fine all day. Trying to resolve this first. I've followed your instructions and deleted and recreated the original query. As soon as I can get my spreadsheets opening again I'll be able to confirm if it worked.

 

[Richard Horne]

I can confirm that Pat's suggestion of deleting and recreating the queries did indeed correct the ordering. Thanks so much Pat.

What is strange, however, is that after working many times throughout the day, the DoCmdTransferSpreadsheet function has now stopped producing spreadsheets that will actually open in Excel and I've had to change methods to:

DoCmd.OutputTo acOutputQuery, "YourTableName", acFormatXLSX, exportFileName

DoCmd.TransferSpreadsheet still produces spreadsheets that appear to be the correct file size but will no longer actually open in Excel. Seems to be a common issue, too, as I've found other threads from people with the same problem. Strange one.

 

[Pat Hartman]

What was your original expression?

 

[Richard Horne]

'Export query to spreadsheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "purchase_orders_live_temp", "C:\DEAN\Exported Files\live_pos.xlsx", True

This had been working just fine for most of the day. Towards the end of the day .xlsx files were being created of similar file sizes but would no longer open in Excel on multiple machines.

I tried with type acSpreadsheetTypeExcel12xml, too.

 

[arnelgp]

I can confirm that Pat's suggestion of deleting and recreating the queries did indeed correct the ordering. Thanks so much Pat.

did i not also suggest it in post #2 and #9?

 

[Richard Horne]

did i not also suggest it in post #2 and #9?

Yeah you did, though inexplicably that didn't seem to work. I don't know why doing it manually instead of through VBA made a difference, but that was the only method that seemed to work.