A VBA code saves multiple sheets to CSV in a currently opened workbook Can the CSV files include the name of the workbook & worksheet?

[reuben_rambo]

I have a current VBA code that works and is written below.

To run the VBA code, you must open the XLSM file and then run the VBA code, where it then saves the worksheets as CSV files into a particular folder with the CSV file names are associated with the exact worksheet names.

Question 1
Can the below VBA code be improved, where the names of the CSV files include the workbook name and worksheet name?

The EXAMPLE

I have a ONE XLSM file named:
test1.xlsm

The XLSM file has 2 worksheets named:
1_WSUS1
1_WSUS2

The below VBA code will convert the sheets to CSV files, which I want. The CSV files will be named:
1_WSUS1.csv
1_WSUS2.csv

My specific question related to the example is:
Can the below VBA code be improved, where the names of the CSV files include the workbook name (i.e., test1) .... so, the names of the newly created CSV files are:

test1_1_WSUS1.csv
test1_1_WSUS2.csv

Any help here would be greatly appreciated.

The VBA code

Public Sub SaveWorksheetsAsCsv()
Dim xWs As Worksheet
Dim xDir As String
Dim folder As FileDialog
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.SaveAs xDir & "\" & xWs.Name, xlCSV
Next
End Sub

Thanks,
Reuben

 

[Micron]

Perhaps

xWs.SaveAs xDir & "\" & ThisWorkbook.Name & "_" & xWs.Name, xlCSV

 

[reuben_rambo]

Hi Micron

Unfortunately, that solution didn’t work

I saved the VBA code in the PERSONAL.XLSB Module 1 (see 1.jpeg)



The example i used to run your suggested code is as follows;

I have a XLSM file called: JAN.xlsm
Worksheets in the xlsm file are: 1, 2, 3, 4, 5, etc

Ideally the CSV files, I want to create are:

• JAN_1.csv
• JAN_2.csv
• JAN_3.csv
• Etc

What I get though is:

• PERSONAL.XLSB_1
• PERSONAL.XLSB_2
• PERSONAL.XLSB_3
• PERSONAL.XLSB_4
• PERSONAL.XLSB_5

Two problems

• The prefix on the filename is not JAN but instead it’s PERSONAL.XLSB
• There is no CSV extension on all the new CSV file

MY QUESTION

Is there perhaps a workaround, where I want to store my VBA code in the PERSONAL.XLSB Module 1?

Any help would be greatly appreciated.
Best,
Reuben

 

[reuben_rambo]

Perhaps

xWs.SaveAs xDir & "\" & ThisWorkbook.Name & "_" & xWs.Name, xlCSV

Hii Micron

Unfortunately, that solution didn’t work

I saved the VBA code in the PERSONAL.XLSB Module 1 (see 1.jpeg)



The example i used to run your suggested code is as follows;

I have a XLSM file called: JAN.xlsm
Worksheets in the xlsm file are: 1, 2, 3, 4, 5, etc

Ideally the CSV files, I want to create are:

• JAN_1.csv
• JAN_2.csv
• JAN_3.csv
• Etc

What I get though is:

• PERSONAL.XLSB_1
• PERSONAL.XLSB_2
• PERSONAL.XLSB_3
• PERSONAL.XLSB_4
• PERSONAL.XLSB_5

Two problems

• The prefix on the filename is not JAN but instead it’s PERSONAL.XLSB
• There is no CSV extension on all the new CSV file

MY QUESTION

Is there perhaps a workaround, where I want to store my VBA code in the PERSONAL.XLSB Module 1?

Any help would be greatly appreciated.
Best,
Reuben