Sure thing! The fix is super straightforward—you just need to tweak the file format parameter when saving the split files. Here's exactly what to do:

Key Parameter Change #

In your VBA script, locate the line where you save the new split workbook (it’ll look something like wb.SaveAs ...). Replace the FileFormat argument with a CSV-specific constant, and update the file extension to .csv.

Option 1: Standard CSV (ANSI encoding)

Use this for basic cases without special characters:

wb.SaveAs Filename:=yourOutputPath & "SplitFile_" & WorkbookCounter & ".csv", FileFormat:=xlCSV

Option 2: UTF-8 Encoded CSV (best for special characters)

Use this if your data includes accents, non-Latin characters, or emojis:

wb.SaveAs Filename:=yourOutputPath & "SplitFile_" & WorkbookCounter & ".csv", FileFormat:=xlCSVUTF8, Local:=False

Full Example Snippet #

If your original save logic looked like this (for XLS):

wb.SaveAs Filename:=savePath & "SplitFile_" & WorkbookCounter & ".xls", FileFormat:=xlExcel8

Replace it with:

Dim savePath As String
savePath = "C:\Your\Target\Folder\" ' Update this to your actual output path
WorkbookCounter = WorkbookCounter + 1

' Save as UTF-8 CSV
wb.SaveAs Filename:=savePath & "SplitFile_" & WorkbookCounter & ".csv", FileFormat:=xlCSVUTF8, Local:=False
wb.Close SaveChanges:=False

Quick Notes #

• File Extension: Always use .csv instead of .xls in the filename so your OS recognizes the file type correctly.
• Delimiter Behavior: If you need to use your system’s default delimiter (like semicolon instead of comma for European locales), set Local:=True in the SaveAs call.
• Cell Content: Excel will automatically wrap cells with commas (or your delimiter) in quotes when saving to CSV—this is standard and safe for most use cases.

Content of the question originates from Stack Exchange, question author Raul Ribeiro