TransferSpreadsheet - Output column order not matching query order

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

 

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

 

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

 

[Richard Horne]

Technically, no. You wrote code to perform a one-time simple fix. I didn't test the code. I'm assuming it works. But there was no need to write code to solve the problem.

The code would be very useful if there were a bunch of queries that needed to be fixed up or if the same query needed to be fixed up each time you used it - which is what the example showed.

I know that the solution in #12 is just too simple to impress anyone but the fact is, it works.

@Richard Horne The problem with the original solution is that you were using the wrong type for the spreadsheet. Not obvious but the correct value is acSpreadsheetTypeExcel12Xml. The version without the xml at the end creates a binary spreadsheet with a different extension than .xlsx. I think you'll find that the Transferspreadsheet method works if you change the type. Please try it and confirm.

Sorry for the delay in coming back to this I've been busy on other projects.

Pat - I had been using the Xml version successfully for most of the day until it stopped working. It was only in my troubleshooting that I switched to the non-Xml version to see if that was making the difference. I tried both variants multiple times until I eventually switched to the DoCmd.OutputTo acOutputQuery method which was the only way I could get it to work.

While Googling this issue, I came up with a few posts from other people who had had the exact same problem and resorted to the same solution.

 

[Richard Horne]

I agree with you in that things don't just stop working, but the export code was working fine and I had used it many times in my tests and moved onto troubleshooting other things because it was so reliable. I did multiple compact and repairs and rebooted numerous times, too. It's definitely a strange one as I took that line in isolation and applied it to a new button on a new form and the spreadsheets that were output were still corrupted/couldn't be open. It makes me wonder if something went awry with Excel as opposed to the code.

 

[Gasman]

Are you using 365, as that appears to be having strange problems with files. Rolling back cures the problem.