Safest Way to Export CSV from Excel Using VBA
This method is recommended when data integrity is critical (databases, ETL pipelines, forensic analysis).
This approach explicitly handles comma vs semicolon delimiters, safely escapes quoted text, independent of cell formatting and is not affected by Excel regional or version differences. Unlike Excel’s built-in Save As CSV, this approach gives you full control over how data is written.
Key Advantages
Works reliably with very large datasets
Handles commas, quotes, and line breaks correctly
Prevents data corruption during import
Ideal for PostgreSQL, MySQL, ETL pipelines, and forensic data analysis
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | Sub WorksheetToCSV() Dim ws As Worksheet Dim fNum As Integer Dim filePath As String Dim r As Long, c As Long Dim lastRow As Long, lastCol As Long Dim lineText As String Dim cellValue As String Set ws = ActiveWorkbook.ActiveSheet filePath = "C:\Temp\export.csv" fNum = FreeFile lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column Open filePath For Output As #fNum For r = 1 To lastRow lineText = "" For c = 1 To lastCol cellValue = ws.Cells(r, c).Text ' CSV escape rules cellValue = Replace(cellValue, """", """""""") If InStr(cellValue, ",") > 0 Or InStr(cellValue, """") > 0 Then cellValue = """" & cellValue & """" End If lineText = lineText & cellValue & IIf(c < lastCol, ",", "") Next c Print #fNum, lineText Next r Close #fNum MsgBox "CSV file created: " & filePath End Sub |
This approach trades a small amount of performance for maximum correctness, which is usually the right decision in professional and forensic contexts. Especially useful:
If the CSV will be consumed by databases
If the data contains free text, names, notes, or logs
If you need deterministic, reproducible output
If Excel’s locale-dependent CSV export is not acceptable