MS Access :Export Table to Multiple Excel Files.

[zaaimanm]

Hi, New to VB. but Learning fast.

I have a database with 2mil+ records in it, about 8 columns.

I need to create a Module that I can link to a button that will export the records into excel, when the max records for the spreadsheet is reached it needs to overflow into a new spreadsheet. It needs to keep looping until all data have been exported to excel.

I am using MS Access 2007.

Please assist, Thank you.

 

[DCrake]

Why open a new workbook when you can start a new worksheet?

 

[hbrems]

into a new spreadsheet

That's what he said no?

 

[DCrake]

No, what he said was spreadsheet, a more common name for a workbook. A worksheet is a tabbed page in a workbook/spreadsheet.

 

[zaaimanm]

Thanks for the response.

The reason I need it separately is because of the amount of records, The database is currently at 3 million records but will be growing to about 6 million in the next month or so.

I am really open to options, if it will keep populating into a new sheet in the workbook and automatically create a new workbook when the current one is full, then all is good.

Basically I just need a way to export the 3-6 million records to excel.


Thanks a million.

 

[zaaimanm]

IN total I have 4 million records I need to export, what do you think will work the best and what code do I need.

 

[DCrake]

Are you going to be doing anything with the records when you have exported them?

 

[zaaimanm]

Thanks for the reply,

Once the records are exported they will be given to different data processors. I am unclear as to what they will be doing with the data, at the moment it’s just a headache exporting over 3 million records.
I have a file in the C Drive called Access In Port, I have the code to compile them all into the table. Once in the Table I have different queries to run and adjust the data. When done with the data I need to export them again so I created a folder called Access Export in the C drive. This is where I am stuck.

So in sort, I receive a load of excel files all in the same format, I then in port and work with the data, when done I need to export it and give it to different data processors.


Thank you for the help so far.

 

[DCrake]

What version of Access\Excel are you using?

 

[zaaimanm]

Sorry, I am using access and excel 2010.