Merge several worksheets into the same .csv file

[NiceLittleRabbit]

Hello,

I have the code which allows me to save a worksheet as a .csv file.

However, the reason I am converting to .cvs is that each worksheet has about 50,000 rows

How can I parse through all the worskheets and save them, one after the other, in the same .csv file?

Here comes the code so far. This subroutine, as far as I understand it (even though I am the author :-) ) will only keep the last worksheet in the .csv file

Please Login or Register  to view this content.

And by the way, if anyone knows how to save a .csv with semi-colon separator I will be infinitely grateful, too

Thank you in advance

[NiceLittleRabbit]

Hello,

Is it impossible? No one to help?

[pike]

Hi NiceLittleRabbit

you could adapt this

Please Login or Register  to view this content.

[NiceLittleRabbit]

Hello Pike,

Thank you for your answer. I actually had a code where I copied the data in the .csv file myself, as I think you are proposing to do here.

The issue I had was that .csv has a lot of different rules and by simply copying the data I always missed one rule or the other (for instance the number of quotes before and after a cell which already has a weird number of quotes itself), so that the .csv reader (which is a specific program, not Excel and which is purely based on the .csv rules) always complained and could not read the full file. So this is why I would like to use the Excel built-in saveas .csv

The code I am talking about (which may actually have been adapted from yours ) is:

Please Login or Register  to view this content.

Any idea?

[pike]

hi NiceLittleRabbit,
if you place all the data in one worksheet you could then save that one sheet as a csv file
you cant save more that one sheet as one csv file by saveas

[NiceLittleRabbit]

Thank you Pike

Given that each sheet has over 50,000 rows, I then conclude that it is not possible to save several large worksheets into one CSV file using the saveas function in Excel.

I will thus certainly have to create several .csv files, use either shell + Dos command or come back to Excel's VBA to merge them into one and delete the additional .csv files. Complicating simple things ... :-(

Thank you Pike for your help