Access 97 Export Query to Excel

I am trying to export a query called "2002" via Excel.

This works fine if I am actually in Access and select File->Save As/Export and choose Excel as my format.

However, when I am in Access, if I click the "Analyze with Excel button" I get an error saying "There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access."

I know that this is not true since #1 I already accomplished this task through the "Save As/Export" I mentioned above, and #2 There are only 20,000 rows or so (far less than the maximum 65K that Excel can handle).

I figured once I can do this automatically from Access, I'll be able to call the same routine from Excel...but I haven't gotten there yet!

Okay, I have been working on this for quite some time now and have tried several solutions.

I have tried within a Module in Access (actually I tried this in Excel too--but with slightly modified code):

    DoCmd.OpenQuery "2002 & 2003"
    DoCmd.SelectObject acQuery, "2002 & 2003", True
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acQuery, "2002 & 2003", acSaveNo

I also tried to run the canned Macro code through Access where it follows the same steps as above.

I got the same error in both cases.

I even tried to "Shell" the command through the command line and a batch query:
"C:\Program Files\Microsoft Office\Office\MSAccess.exe" "C:\database.mdb" /x Export2002 /nostartup;

This produced the same error.

I tried:
DoCmd.OutputTo acQuery, "2002", "MicrosoftExcel(*.xls)", "C:\Test2002.xls", False, ""

...got the same error.

I was able to apply the same code to other queries and got results, but I can't seem to get this one to work.

Please help!

Thanks in advance,

DrewK
Start Free Trial