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

[Manerlao]

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:

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

 

[C Moore]

    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]

Great thanks C Moore, let me try this!