Output to Excel not working

  • Thread starter Thread starter Billy Rogers
  • Start date Start date
B

Billy Rogers

I'm trying to troubleshoot a database that another deptment uses where I work.

They have a button that runs a macro that exports a query result to excel
and saves the file. A message is returned saying there are too many rows,
but when i run the query manually it's only 16,000 rows and 10 columns so it
shouldn't be a problem.

I tried converting the macro to code

DoCmd.OutputTo acQuery, "*Christine-DailyList-Matched", acFormatXLS,
"C:\Matched_List.xls", False, ""

and get the same error.

I also tried using the menu commands

Tools, Office Links, Analyze it with MS Excel

and got the same message. I tried compacting and repairing and rebuilding
and identical macro. Strangly after the user contaced me with the problem i
ran the macro twice and it worked....and then it stopped working. I've
deleted the file where it saves it. I can't think of anything, I'm just
manually running and using copy and paste, but I'd like to get this fixed for
the user.

Thanks,


--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/
 
Try using TransferSpreadsheet

DoCmd.TransferSpreadsheet acExport, 8, "YourTblOrQry", "PathToExcelFile", True
 
I'm trying to troubleshoot a database that another deptment uses where I work.

They have a button that runs a macro that exports a query result to excel
and saves the file. A message is returned saying there are too many rows,
but when i run the query manually it's only 16,000 rows and 10 columns so it
shouldn't be a problem.

I tried converting the macro to code

DoCmd.OutputTo acQuery, "*Christine-DailyList-Matched", acFormatXLS,
"C:\Matched_List.xls", False, ""

and get the same error.

I also tried using the menu commands

Tools, Office Links, Analyze it with MS Excel

and got the same message. I tried compacting and repairing and rebuilding
and identical macro. Strangly after the user contaced me with the problem i
ran the macro twice and it worked....and then it stopped working. I've
deleted the file where it saves it. I can't think of anything, I'm just
manually running and using copy and paste, but I'd like to get this fixed for
the user.

Thanks,
Click to expand...

Is that really an asterisk (*) in the query name?
Using the asterisk in a test query, the OutputTo, using your exact
code, worked for me (I'll assume your code was all on one line).
However, when opening the spreadsheet, there was an error message that
Excel had to repair the worksheet name because of an illegal character
in the name (the *).

See what happens if you remove the asterisk from the query name.

If you still have a problem, try using the TransferSpreadsheet method.
 
Back
Top