Automatically split large CSV files to 2nd, 3rd etc sheets if row count exceeds a limit

Hi All

I'm hoping someone can help me with some VBA to overcome the row limitation issue I'm having in excel.

I am using a web-based system that exports data by automatically by opening a CSV file in Excel. There is no option to save the file first. The files I export are quite often too large and do not fully load due to the row limitation in Excel and need to remain in an Excel format.

Would anyone know if there's a way to have Excel split a file as it's loading to the a 2nd sheet, 3rd sheet and so on, if the row count exceeds say 1,000,000?

Any advice or help will be really appreciated.

Many thanks

Hi !

Originally Posted by darls15

I am using a web-based system that exports data by automatically by opening a CSV file in Excel. There is no option to save the file first. The files I export are quite often too large and do not fully load due to the row limitation in Excel and need to remain in an Excel format.

Would anyone know if there's a way to have Excel split a file as it's loading to the a 2nd sheet, 3rd sheet and so on, if the row count exceeds say 1,000,000?

Nope, there is no option as Excel is very not a database software …

But to be opened in Excel even from Web the file is stored locally on the hard disk
- maybe in the Temp folder of current user, just check it out -
so a beginner level short VBA procedure can read this text file and split it if necessary …

Awesome, thanks Keebellah! I'll try this code out today

Marc, thank you. As suggested, I found all the files I've been attempting to open in temp folder on my hard drive.

Hi Keebellah, well I tried to make this work however Excel just kept on freezing. I won't give up trying to get it to work just yet, however think it's time to move to the database environment. Thanks again, I appreciate your help.