Chunk rows of records into 64K blocks and create .xls files..
Hi!
I need some help in assembling the VBA Access code to do the following:
The SQL query will extract may be 150,000 rows from the Access database using a specific criteria
What I would like to do is chunk these records (may be .csv output) into smaller groups (64K) and create .xls file automatically. e.g sheet1.xls sheet2.xls sheet3.xls etc.....
I need to do all this within VBA Access code.
yaj
I need some help in assembling the VBA Access code to do the following:
The SQL query will extract may be 150,000 rows from the Access database using a specific criteria
What I would like to do is chunk these records (may be .csv output) into smaller groups (64K) and create .xls file automatically. e.g sheet1.xls sheet2.xls sheet3.xls etc.....
I need to do all this within VBA Access code.
yaj
Steve Bink
Shouldn't be too hard. How strict is your 64k cutoff? How long are your records? How many fields? Put up some table information, and I'm sure we can generate some code to do what you want.
ASKER
Hi! Routinet,
I believe that Excel Spreadsheet has limitation on the number of row/records. (64K).
The number of fields may vary depending the tbales I access.
What I would like is some sort of a generic VBA Access code to take possibly the records output from Access Database (generated by the SQL Query) and upon completing the SQL rotine. create multuple Excel Spreadsheets in an incremental order.
It is easy to write chunk program in C, but my user does not want to use C - Strictly VBA from Access.
yaj
I believe that Excel Spreadsheet has limitation on the number of row/records. (64K).
The number of fields may vary depending the tbales I access.
What I would like is some sort of a generic VBA Access code to take possibly the records output from Access Database (generated by the SQL Query) and upon completing the SQL rotine. create multuple Excel Spreadsheets in an incremental order.
It is easy to write chunk program in C, but my user does not want to use C - Strictly VBA from Access.
yaj
Give me an hour or so to code and test it, and I'll come back and post a working sub for you. This should not be too difficult. :)
Just so I'm clear, you want this to be able to export the results of ANY query/SQL you pass to it into an Excel spreadsheet, divided into individual, consecutively numbered worksheets of no more than, say, 60000 records each, yes?
Just so I'm clear, you want this to be able to export the results of ANY query/SQL you pass to it into an Excel spreadsheet, divided into individual, consecutively numbered worksheets of no more than, say, 60000 records each, yes?
ASKER
Correct!
Please note! The limittaion of number of records is merely due to the largest size of spreadsheet you can have. This is why we have to do this exercise.
yaj
Please note! The limittaion of number of records is merely due to the largest size of spreadsheet you can have. This is why we have to do this exercise.
yaj
Okay, got it. There are a couple things you should be aware of, though:
1) There are three constants at the beginning. These are module-level constants, and only provide a simple way to change the operating parameters. They should be relatively self-explanatory. You can create a sub to allow the user to pick a filename, and use that instead of the SaveAsFileName constant.
2) The error handling is on a VERY basic level. You may want to add more detail to it.
3) I'm not sure what the column limit is on Excel, but this does not check for it.
Let me know if you have any issues.
1) There are three constants at the beginning. These are module-level constants, and only provide a simple way to change the operating parameters. They should be relatively self-explanatory. You can create a sub to allow the user to pick a filename, and use that instead of the SaveAsFileName constant.
2) The error handling is on a VERY basic level. You may want to add more detail to it.
3) I'm not sure what the column limit is on Excel, but this does not check for it.
Let me know if you have any issues.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! routinet,
I will takeover from here.
I sincerely appreciate your immediate attention.
yaj
I will takeover from here.
I sincerely appreciate your immediate attention.
yaj
superb. i think the excel data max sizes has to do with binary numbers...
i think the max columns is 256 (2^8)
max rows is 65536 ( including header row) (2^16)
i think the max columns is 256 (2^8)
max rows is 65536 ( including header row) (2^16)
That's exactly why. You'll notice that many of the limits we hit as programmers, including hard drive size, it due to binary limits.
routinet:
I am very impressed with your expertise here . As such, I have tested it at my end and it works great for me.
I have opened a new thread at the link below with a view to enhancing your current code, therefore, I have introduced a new twist to the new question hoping you would kindly assist.
https://www.experts-exchange.com/questions/23255650/Export-Access-data-to-Excel-Template.html
Regards
I am very impressed with your expertise here . As such, I have tested it at my end and it works great for me.
I have opened a new thread at the link below with a view to enhancing your current code, therefore, I have introduced a new twist to the new question hoping you would kindly assist.
https://www.experts-exchange.com/questions/23255650/Export-Access-data-to-Excel-Template.html
Regards