Export Excel Sheets to CSV with an appended file name to a user selected folder

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
Dear Excel Community,

I hope everyone is keeping well.

I have been trying to establish a code in order to: export all my Excel data sheets to "CSV (Comma Delimited)" with an appended filename to a specific folder but I haven't had much luck.

My goal:

1. Export all (30-40) sheets in XLS file to CSV with an appended filename, saved to a specific folder directory, and don't save any changes to the existing XLS workbook.

I have sheets (Sheet 1, Sheet 2, Sheet 3, ..., Sheet 40) and I would like them to be saved in the specific file format "CSV (Comma Delimited)" with an appended filename.
For example, sheets are saved from "Sheet1" to "Sheet1_2020New.csv" for all sheets. So, "Sheet2" becomes "Sheet2_2020New.csv".

I would like the code to ask the user to "Select a save folder", instead of hardcoding the SaveToDirectory into the macro.
So this allows the user to select a specific folder to save all the CSV's to.

So far my code looks like this:

VBA Code:
Public Sub SaveWorkbookToCSV()

Dim ws As Excel.Worksheet
Dim SaveToDirectory As String

Dim CurrentWorkbook As String
Dim CurrentFormat As Long

CurrentWorkbook = ThisWorkbook.FullName
CurrentFormat = ThisWorkbook.FileFormat
' This stores the current details for the XLS workbook

      SaveToDirectory = "C:\Users\admin\Documents"

      For Each ws In ThisWorkbook.Worksheets
      If ws.Name <> "ScratchSheet" Then
         ws.SaveAs SaveToDirectory & ws.Name, xlCSV
      Next
      End if 

 Application.DisplayAlerts = False
 ThisWorkbook.SaveAs Filename:=CurrentWorkbook, FileFormat:=CurrentFormat
 Application.DisplayAlerts = True
' Temporarily turn alerts off to prevent the user being prompted
'  about overwriting the original file.

End Sub

Please let me know if anyone can assist, thank you very much all!
Have a nice week and I look forward to hearing from the community!

Best regards and thank you,
Manerlao
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
    Dim bFileSaveAs As Boolean
    bFileSaveAs = Application.Dialogs(xlDialogSaveAs).Show(, 6)
    If Not bFileSaveAs Then MsgBox "User cancelled", vbCritical

I got this from somewhere that I can't remember or I'd give credit. This saves a single file for me. I think it will 'assign' the directory after you choose the first one though.
 

Manerlao

Board Regular
Joined
Apr 14, 2020
Messages
50
Office Version
  1. 2019
Platform
  1. Windows
Great thanks C Moore, let me try this!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,198
Messages
5,600,280
Members
414,374
Latest member
akbir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top