Export Code

[andydarly]

Hi, I am trying to export a query from Access to a binary Excel file. I have about 12 set up, all very similar (just names and locations change).



With this one though it runs in Access when I run it but on the export Module it always crashes Access if I want it as a binary Excel file using the below:

DoCmd.TransferSpreadsheet acExport, 9, "qry_ATTA_ABR_1617_Q3", "DriveLocation\qry_ATTA_ABR_1617_Q3.xlsb"

If I run it as part of the module as:

DoCmd.TransferSpreadsheet acExport, 10, "qry_ATTA_ABR_1617_Q3", "DriveLocation\qry_ATTA_ABR_1617_Q3.xlsx"

it works absolutely fine.

All my exports (like I say around 12 of them, all work fine as binary excel documents). Any idea why this one doesn't work as Binary? For the period I want there are some 9000 rows so not a lot at all.



Please help!!

Thanks in advance.

Andy

[andydarly]

Hi. Anyone any ideas at all?

Cheers

[CJ_London]

exportexcel type 10 is xml. 9 should work for .xlsb. If it works for other queries, perhaps there is some data which is not converting successfully to binary. What happens if you use 9 to .xlsx? does that work?

and if it works what happens if you open the .xlsx and save as binary?