TransferSpreadsheet - Output column order not matching query order

[Richard Horne]

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

Yes I am indeed, though can't roll back as our whole company runs 365 and we have been for 18 months, now.

 

[Gasman]

No, you roll back to a previous version of 365. You are still on 365, and only your computer needs to do it, to see if that is the issue.
See post 27 & 28 here https://www.utteraccess.com/topics/2063554/posts/2801702

 

[Isaac]

If you like, you can also see whether Excel VBA's Range.CopyFromRecordset method works for you. I actually prefer this and use it more often than TransferSpreadsheet. I cannot now remember why and I think it had to do with a mixture of things that had to be done eventually with the Excel file anyway and as long as I had to open and manipulate it (to ANY extent), the CopyFromRecordset was kind of a natural fit. I think it solves a truncation problem too if I remember right. And is more straightforward as to the specific cells destination.

Only thing with that is you have to add the column fields, but that's as simple as:

dim x as long
'assumes you have "rs" declared and open as your recordset
for x = 0 to rs.fields.count-1
    worksheetvariable.cells(1,x+1).value=rs.fields(x).name
next x

Many people start with TransferSpreadsheet but as time goes by wish to perform other actions on the resulting Excel file anyway.