Export All Sheets to CSV

Attribute VB_Name = "ExportSheetsToCSV"
'===============================================================
' ExportSheetsToCSV.bas
'
' Excel VBA module that exports every worksheet in the active
' workbook to its own CSV file, named after the sheet, saved
' next to the workbook.
'
' Usage:
'   1. Open your workbook, press Alt+F11 to open the VBA editor.
'   2. File > Import File... and select this .bas module
'      (or paste its contents into a new module).
'   3. Run ExportAllSheetsToCSV (Alt+F8 > select macro > Run).
'===============================================================
Option Explicit

Public Sub ExportAllSheetsToCSV()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim outFolder As String
    Dim exportedCount As Long

    Set wb = ActiveWorkbook
    If wb Is Nothing Then
        MsgBox "No active workbook found.", vbExclamation
        Exit Sub
    End If

    outFolder = wb.Path
    If outFolder = "" Then
        MsgBox "Please save the workbook before exporting.", vbExclamation
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    exportedCount = 0
    For Each ws In wb.Worksheets
        If ExportWorksheetToCSV(ws, outFolder) Then
            exportedCount = exportedCount + 1
        End If
    Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox exportedCount & " sheet(s) exported to:" & vbCrLf & outFolder, vbInformation
End Sub

Private Function ExportWorksheetToCSV(ws As Worksheet, outFolder As String) As Boolean
    Dim tempWb As Workbook
    Dim safeName As String
    Dim csvPath As String

    On Error GoTo Fail

    safeName = SanitizeFileName(ws.Name)
    csvPath = outFolder & Application.PathSeparator & safeName & ".csv"

    ws.Copy ' creates a new workbook with just this sheet
    Set tempWb = ActiveWorkbook
    tempWb.SaveAs Filename:=csvPath, FileFormat:=xlCSVUTF8, CreateBackup:=False
    tempWb.Close SaveChanges:=False

    ExportWorksheetToCSV = True
    Exit Function

Fail:
    ExportWorksheetToCSV = False
End Function

Private Function SanitizeFileName(ByVal name As String) As String
    Dim invalidChars As Variant
    Dim ch As Variant

    invalidChars = Array("\", "/", ":", "*", "?", Chr(34), "<", ">", "|")
    For Each ch In invalidChars
        name = Replace(name, ch, "_")
    Next ch

    SanitizeFileName = name
End Function