TransferSpreadsheet is creating 3 sheets 1

[AlanTylman]

Hi

I am trying to export a query from Access 2003 to Excel 2003 that has over 17000 lines and I am trying to use the TransferSpreadsheet command as below.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "{query name}", "{file path}"

The spreadsheet is created but has 3 sheets, 2 of which only contain the header row.
While I can probably code something to delete the unwanted ones in Excel I'd prefer not to (being a bit lazy as I am!)
Any help most appreciated!

 

[kjv1611]

Are you sure this is correct? acSpreadsheetTypeExcel9

What about removing that or changing it, and see if it works better?

 

[AlanTylman]

Yes, I've even tried acSpreadsheetTypeExcel8 and acSpreadsheetTypeExcel10 but to no avail.
On a side point I've still be unable to ascertain exactly what the differences are between these even from the MS site.

 

[kjv1611]

Have you tried just leaving out that part altogether? Just do back to back commas at that option?

 

[AlanTylman]

Gave that a try and while it reduced the sheets to 2 they both had data on them and some data errors (#VALUE). Thought I'd give it another run just in case and voila!, 1 sheet, no errors.
Thanks for your assistance!

 

[kjv1611]

Glad could help!