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

[Pat Hartman]

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

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.

 

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

 

[Pat Hartman]

Things don't just stop working. Something happened. Did you try a C&R? Did you try to reboot? Memory leaks can cause strange issues.

If you're happy, then don't bother with trying to figure out what happened.

 

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

 

[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

 

[Pat Hartman]

For your own sake, you might want to do the roll back to see if the new version of Access caused the problems but since you found a work-around - I would just document the code change and list the version that caused the problem. Then leave the working code alone. Otherwise, you have to roll back all the user versions also.

 

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