Analyze It with MS Excel

Topic

New Reply

I’m trying to export data from Access 2000 into Excel 2000. After opening the table (which has 64,000 records), I’m selecting Tools | Office Links | Analyze It with MS Excel, but I get an error message telling me that there are too many records. It’s evidently creating an Excel 95 format workbook, but I don’t know why. Does anybody know how to change it to create and Excel 97/2000 format file? Thanks.

Reply | Quote

Replies

It should be creating an Excel 2000 worksheet. Is the error message an Access error or an Excel error? What exactly is the message and the error number?

You may be hitting some other limitation, or combination of limitations, besides the simple number of rows and columns allowed in Excel. For example, do you have multiple memo fields in the table? Do you have any memo fields that exceed 32K characters (allowable in Access 2000, but not in Excel 2000)? Is every field 255 characters in width (that happens with make-table queries fairly often)? In that case, even single-table Access queries may fail.

Reply | Quote

The message is an Access error message. No message number, just a dialog box stating:
“There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access.”
I’ve noticed before that the Excel file that gets created (when I’ve extracted < 16K rows) is an Excel 95 file, so I'm curious how my installation of Access is somehow set to extract in the older format. Plus how would I change it…

Not a big deal, though. I took the extreme path of actually looking at a book last night (Woody's Office 2000 book), and in there he talks about using File | Extract rather than Office Links. If I do that, I do get to choose the file type.
Thanks for your help.

Reply | Quote

I should have said File | Export, not File | Extract.

Reply | Quote

Have you tried using the Get External Data command in Excel? It will set up an ODBC-type of connection to Access (and other stuff) and has a lot of details on what columns to import, etc. I have done this to import tables from Access into Excel when they are apparently too large to do via File|Import.

A warning, tho: the table you get in Excel will be ‘married’ to the Access table (it will be connected for refreshing data, etc.) — if you don’t want that functionality, you will have to delete the Name Reference in Excel that is created when you Import External Data.

Reply | Quote