Access export to excel produces "unreadable data"

  • Thread starter Thread starter Andrew Shelley
  • Start date Start date
A

Andrew Shelley

I have a spreadsheet saved as an Excel 97-2003 workbook and am trying to
export the contents of a query from Access 2007. The exported data is
referenced in about 400 sumif statements in several calculation sheets.

I've tried

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, stDocName,
"Output Comparison.xls"

This produces a workbook that produces the error message "Excel found
unreadable content in 'Output Comparison.xls'. If I repair and open the file
the first sheet of formulas has been completely toasted - it's just
unformatted numbers. I've tried specifying several different
acSpreadsheetTypes, all the way from 5 to 8, and even leaving that parameter
blank - but nothing seems to work.

So I also tried

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, "Output
Comparison.xls", True

And this deletes the original workbook and creates a new file containing
just the data that I exported.

This was oh so easy in Access 2003... am I missing something in 2007?
 
Back
Top