Macro to save and separate multiple worksheets as individual files in .csv format

[Ay Sticky]

Please I have multiple worksheets in a workbook saved in xlsx. The task I need the vba for, is to separate these worksheets into individual workbooks in csv format with their names corresponding to the names as used in the worksheets. It would also be great if all separated sheets could all be saved in same directory.

 

[Marc L]

1. Maybe I've missed something but it seems you forgot to elaborate how are named these worksheets ?
   
   
2. You was stating about some calculations but I have read only about deleting / inserting / copying rows or am I wrong ?
   
   
3. I can create the new csv files directly without using any worksheet (faster) so do you really need your point #4 ?
   If yes indicate the location and the filename in order to save the workbook containing the 'multiple worksheets' …
   
   
4. Do you import always the same file (path & filename never change) or do you need to select the location & the file to import each time ?
   
   
5. The destination folder is the same as the source file for your point #5 ?
   
   
6. Far easier to give it a try with a sample of a source csv file on a files host website like Dropbox …

 

[Ay Sticky]

1. Maybe I've missed something but it seems you forgot to elaborate how are named these worksheets ?
   
   
2. You was stating about some calculations but I have read only about deleting / inserting / copying rows or am I wrong ?
   
   
3. I can create the new csv files directly without using any worksheet (faster) so do you really need your point #4 ?
   If yes indicate the location and the filename in order to save the workbook containing the 'multiple worksheets' …
   
   
4. Do you import always the same file (path & filename never change) or do you need to select the location & the file to import each time ?
   
   
5. The destination folder is the same as the source file for your point #5 ?
   
   
6. Far easier to give it a try with a sample of a source csv file on a files host website like Dropbox …

1. The other produced worksheets should have same name as the single worksheet from the source file, but indexed with numbers. For example, if the single worksheet from the source file is named Hill, then the first produced sheet should be Hill(2), next should be Hill(3) and so on.

2. Yes and sorry please you're not wrong. I didn't mean calculations in terms of mathematical formulas. The insertion, deletion, copying are what I meant by calculation. Perhaps I should have used some other words rather than calculation.

3. If it can be done directly then point #4 is not really needed, because having those individual csv files as end results can serve as record purpose too. Hence point #4 can be removed if there are better ways to get the work done faster.

4. Path doesn't change but filename will change because I have more than one source csv text file to perform these whole operation on. All these source csv text files are in same path but saved with different names. This is the path to the various source files.
C:\Users\PC\Desktop\study mst\coordinate extractions\coordinate CSVs

5. Inside the source folder path, I'd like to create a "New folder" to serve as destination folder path for the point #5. Like this
C:\Users\PC\Desktop\study mst\coordinate extractions\coordinate CSVs\New folder

6. Just as you said, I have uploaded 2 different source csv file to the Dropbox link below. We can just use one as an example, and if the process works fine I'll repeat the procedure for other source csv files, because I have more than two, in fact I have ten.

From the files in the link, Gra.csv has 204 rows of data in its worksheet. According to point #1; 202 extra worksheets will be created to make the all worksheets total to 203.

Hill.csv is also another source csv text file but with a row of 162 in its worksheet. Applying same procedure in #1, extra worksheets of 160 will be created totalling all worksheets to 161 including the original worksheet from the source file.

Since the single worksheet in the source csv text file has the name Gra, then the newly produced worksheets would have their names in order of Gra(2), Gra(3) and so on. Thank you for helping out and dishing out your time. Let's see if it can be done at a go as you expressed in option 2.

Dropbox

www.dropbox.com

 

[Marc L]

​

About point #3 : yes it is easier & faster to create directly the new csv text files rather than working with worksheets …​

1. Instead of treating a csv file alone all the csv files from the source directory can be processed at once ?
   
   
2. Whatever the source csv file you want to create the new csv files in the directory 'New folder' or
   create in the source directory a sub directory according to the source csv files like 'coordinate CSVsr\Gra\', 'coordinate CSVs\Hill\', … ?

 

[Marc L]

​

3. The End Of Line sequence of the source csv files is Line Feed which is not the Windows standard​

so do you want to keep this original EOL for the new csv files or to use the Windows EOL aka Carriage Return & Line Feed ?​

 

[Ay Sticky]

​

About point #3 : yes it is easier & faster to create directly the new csv text files rather than working with worksheets …​

1. Instead of treating a csv file alone all the csv files from the source directory can be processed at once ?
   
   
2. Whatever the source csv file you want to create the new csv files in the directory 'New folder' or
   create in the source directory a sub directory according to the source csv files like 'coordinate CSVsr\Gra\', 'coordinate CSVs\Hill\', … ?

1. To get all the source csv files processed at once? That would be a wow!!... I have now uploaded 8 source csv files. As I've mentioned 10 earlier, I've already done the remaining 2 manually and I must confessed it took me months to complete, that was before I realized VBA codes could go about these processes.

Dropbox

www.dropbox.com

2. Okay, creating a sub directory as in the second sentence would also do. In fact, the destination directory is not even a problem because irrespective of where they were saved to, I could cut or copy them to my desired folder later on.

 

[Ay Sticky]

​

3. The End Of Line sequence of the source csv files is Line Feed which is not the Windows standard​

so do you want to keep this original EOL for the new csv files or to use the Windows EOL aka Carriage Return & Line Feed ?​

I sincerely don't understand what these terms mean, please pardon me. And I am new to excel vba codes..... However, the end results (these produced csv files) are not meant for Windows use. They would be converted to kmz format and be used on mapping software; this is the next stage of the project.

 

[Ay Sticky]

1. To get all the source csv files processed at once? That would be a wow!!... I have now uploaded 8 source csv files. As I've mentioned 10 earlier, I've already done the remaining 2 manually and I must confessed it took me months to complete, that was before I realized VBA codes could go about these processes.

Dropbox

www.dropbox.com

2. Okay, creating a sub directory as in the second sentence would also do. In fact, the destination directory is not even a problem because irrespective of where they were saved to, I could cut or copy them to my desired folder later on.

Please I will repost the link. There somethings needed to be included.

 

[Ay Sticky]

Please I will repost the link. There somethings needed to be included.

The link is good now.

There is one more task I forgot to include please. For all csv files that would be produced, is there a way to include Headers for each 2 columns. This would be for proper identification of each columns by the mapping tool to be used for next stage of the project. The header can be simple as inputting a text into their first rows.

Column 1 Header is named Latitude
Column 2 Header is named Longitude

The reason is that if the respective columns of the coordinate numbers are not identified, the next phase of the project will not be feasible.

I'll stay online to answer any other questions. Thank you so much.

 

[Marc L]

​

For my point #3 the original EOL (the delimiter between text lines) will be kept and the necessary headers added…​

​

I'm very near to be ready but I'm not well sure of what you really prefer for my last point #2 :​

all new csv files in a single directory or one folder by source csv file ?​

 

[Ay Sticky]

​

For my point #3 the original EOL (the delimiter between text lines) will be kept and the necessary headers added…​

​

I'm very near to be ready but I'm not well sure of what you really prefer for my last point #2 :​

all new csv files in a single directory or one folder by source csv file ?​

Thank you. I now understand better with this latest explanation.

#3 The original EOL should be kept.

#2 One folder by source csv file is very preferable to using a single directory for all.