How do I export data from Access to Excel????

[nisgore]

I'm trying to export large groups of data to Excel to work with, then
save as a csv file. The file sizes range from 4K records all the way to
the 65K limit for Excel.

-- I've tried cut and paste, but when pasting to excel, Excel freezes
for anything over about 5K records
-- Tried saving a query as Excel, but says there are too many records
to export, even if it is only say 10K!!! which is nowhere near Excel's
65K limit.
-- Tried saving as XML, but when opening file in Excel, Excel seems to
freeze when opening the file.
-- Same results with RTF
-- Text is a worthless joke.
-- Don't know much about the other formats, perhaps one of you gurus
can recommend a format thats easily interchangeable.??
-- Also, if you can recommend a good book for working with the
particulars of Access that might have this sort of detailed information
in it that would be great too.

Does anyone know how to make this work?? I don't mind even doing data
in chuncks, but of course it would be easier to save files in one shot
of course adhering to Excel's 65K limit.

I have approx 400K of data in Access that I need to get back into Excel
in small chuncks so any help with this would be greatly appreciated.

Thanks
Mike

 

[Van T. Dinh]

Check Access VB Help on the TransferSpreadsheet Method.

 

[Van T. Dinh]

I have just noted the 400K ...

The more effcient way is to manipulate the data IN Access (of course, it
depends on what kinds of manipulation you need) and then export the data to
csv format in ONE go using the TransferText Method.

Check Access VBA for the TransferText Method.

 

[nisgore]

so this has to be done programatically?? I'd prefer to avoid using VBA
for this matter. Is it possible to do this without using VBA???

 

[Tom Wickerath]

Hi nisgore,

If you really must, you can use a *cough* macro to export to Excel. Check
out the TransferSpreadsheet action in macros. Change the transfer type to
Export, and set the other options appropriately.

One of the main problems with using macros (Access 2003 and all prior
versions) is that you cannot trap for errors and handle them gracefully. A
user will see a big ugly macro error dialog if your macro encounters an
error. Here is a quote that I will share with you (valid for Access 2003 and
all prior versions; the new Access 2007 has a lot more power available in
macros, and one can trap for errors in A2007).

From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151)

"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."

"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________