Export to Excel Limitation Error

[R2D2]

In a database I have created, I have set up a button that does the same thing as the Tools -> Office Links -> Analyze It With MS Excel menu option. It simply exports the data to excel. 99% of the time, this works great. However, I've run into a problem recently - when I try to do this on a very large table, it gives me an error that says "There are too many rows to output, based on the limitation specified by the output format or by Finance Queries" (Finance Queries is the name of the database). I haven't done too much testing to figure out at what point this problem first starts to occur, but it's occuring with a table of mine that has 25,000 records.

So, is this an unfixable error, or is there some way I can change what the limit is specified as?

 

[fmm]

Build a form. Put a button on it. Put the following code behind the button:


DoCmd.TransferSpreadsheet acExport, , QueryName, PathNameFileName, True


Where
QueryName is the query (or table) you want to export and PathNameFileName where you want it to go.

For example, to export the query qryMyQuery to the file C:\MyDirectory\MySpreadsheet.xls you would use the following code:

DoCme.TransferSpreadsheet acExport, , "qryMyQuery", "C:\MyDirectory\MySpreadsheet.xls", True

 

[R2D2]

Thank you, that works great. If I could, I'd like to make it a little more user-friendly and make it like the built in "analyze with MS Excel" menu item so that it

1. Opens the file in Excel automatically
2. Highlights the first row (the column headings) in grey.

Any ideas how to do this?

 

[dave7273]

Highlighting the column headers is what is normally done with the OutputTo command (similar to what you are doing) You won't be able to do that with the transfer spreadsheet command. However, to automatically open the spreadhsheet. One of the ways to launch you document is to do a shell command to launch Excel with the spreadsheet that you want. This can be done through VBA (or

Shell(path to executable <space> path/filename)
example:
Shell("c:\program files\microsoft office\office10\excel.exe c:\myspread.xls")

Or from within a Macro, you could probably do the same thing with the RunApp action.

Access online help explains both of these ways of doing this pretty well.

 

[ghudson]

You can easily format the Excel file from Access. Check this thread out on how I do it.

hiding excel manipulations and showing a progress bar

Searching the forum is a great way to discover and learn the answers to your Access programming questions.