I use the line:
DoCmd.OutputTo acOutputQuery, "qryReportDataForExcelOutput", acFormatXLS, strOutputFileEXCEL, False
which works fine.... except when I have more than 16K records to export. When I have too many records Access is unable to successfully create the file. I can step into the above line and see the Excel file in Windows Explorer while Access is exporting, but after a delay of a minute or so the file disappears!
If I manually export the query to Excel it works fine, even with more than 16K records.
I have reproduced this behavior on two PCs running XP Pro and Office XP.
So, how can I successfully export this query to Excel with more than 16K records, IN CODE rather than manually?