How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Techie_Ben Your Own Question
Techie_Ben
Techie_Ben, Software Engineer
Category: Microsoft Office
Satisfied Customers: 1773
Experience:  15+ years experience in troubleshooting software.
8287049
Type Your Microsoft Office Question Here...
Techie_Ben is online now
A new question is answered every 9 seconds

I have an access 2000 database with 3 tables that Id like

This answer was rated:

I have an access 2000 database with 3 tables that I'd like to export into sheets in a excel 2000 spreadsheet. The record count in the 3 access database tables are over 1 million. How do I export this into multiple excel workbook sheets without going over the 65000 record limit in each excel sheet?
Thank you for the question. There is already an existing macro to handle this scenario, as you are not the first person to encounter this. Microsoft has written a macro that can be executed from within Excel. It just asks you where the import file is, and will add 65,000 records at a time to a new worksheet until the file is done importing. Here is the link that has the code in it.

To run the macro, you can just open the Visual Basic editor from Excel and paste in the code, then click the play button to run it. If you are using Office 2000 or 2003, that is done by clicking Tools -> Macros -> Edit.

Please let me know if you have any questions and I will be happy to assist further. Thanks again!
Customer: replied 4 years ago.

Ben,


I did a test to see if the code would run/create the multi-sheet excel file. Yes it did.....but I first had to save my "access query" as a txt file. Is this the proper way to do this and/or only way to import to excel in multiple sheets?


Is there not a code that can be run from Access to create an excel spreadsheet? Either way looks to require several steps to process all the data I have. The data is line item transactions that occurred over several years and many customers. My goal is to get a sales total for each customer by year. Any further comments on the most efficient way to accomplish this ?

Yes, the code expects that you have exported the data from Access first, so you can't just generate the worksheets directly in a single step from Access.

As for the question on generating a total, I'm not sure I can comment on that as I'm not at all familiar with your data. If you want to open a new question with the information or maybe a sample file I'll be happy to have a look. Thanks again!
Techie_Ben and 5 other Microsoft Office Specialists are ready to help you