VBA Excel Macro: Save spreadsheet as CSV file

VBA - Visual Basic for Application

VBA is excellently suited to automate certain recurring tasks in the area of Microsoft Office. For example, you may want to save the data of an Excel file or a spreadsheet as a CSV file or simply export it as a CSV for further processing. There are several options here, depending on your needs and application.

The code examples shown below must of course be adapted to the individual application. There may also be different requirements for formatting in the CSV file and the separator. If necessary, the values must also be placed in inverted commas if the separator, such as a “comma“, can also occur within the values.

Example Excel file

The following code examples are based on a small Excel file that contains various formatted data on products and their prices. This also shows what can happen with the formatting when exporting to a *.csv file.

Example Excel spreadsheet with product data to be saved to a CSV file
Example Excel spreadsheet with product data

In all code examples an error handling (Application.DisplayAlerts) is inserted to suppress error messages during saving. However, you can also omit the code.

The CSV file is saved under the same path as the Excel file (fileNameCSV = ThisWorkbook.Path). However, this can be adapted as desired.

Save worksheet as CSV file

The following VBA code saves an entire spreadsheet as a new CSV file. This is convenient and makes the VBA code relatively slim.

Code

In our example, the spreadsheet is called “ProductList“. Therefore, we activate it with this name before the export.

The main function in this case is fulfilled by the method SaveAs. With the parameter FileFormat:=xlCSV we specify that it should be saved as CSV. In this case, the parameter Local is also important. If you do not set this parameter, the default value is false and the language and country settings of the VBA editor are used, which are English/USA by default. This can lead to the fact that if the currency is formatted as Euro, the CSV file will contain dollars.

Sub exportWorksheetToCSV()
    
    Dim fileNameCSV As String.
    Dim myWorksheet As Workbook
    
    'use this to suppress error messages while running the macro.
    Application.DisplayAlerts = False.
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products.csv"

    'activate the regarding worksheet.
    ThisWorkbook.Sheets("ProductList").Activate
    ActiveSheet.Copy
    Set myWorksheet = ActiveWorkbook
 'use parameter Local to get local settings of excel, defaulf value is false
    '(VBA local setting is used that by default is English (USA)
    myWorksheet.SaveAs Filename:=fileNameCSV, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    myWorksheet.Close
    
errorhandling:
    Application.DisplayAlerts = True.
End Sub

Result

In the images below, we see that when the CSV data is saved, it tries to keep the formatting. However, in this case the Local parameter was set to False. This leads to the euro characters suddenly becoming dollar characters in the CSV file.

CSV file with Excel data: the whole spreadsheet was exported with parameter Local=False
spreadsheet exported to CSV file with parameter Local=False

If you set Local to True then you get the following output. You can see that the dollar sign has disappeared. Instead, <0x80> is now output, which according to the ASCII table corresponds to the symbol.

CSV file with Excel data: the whole spreadsheet has been exported
spreadsheet exported to CSV file with Local=True parameter

Export only certain range of a spreadsheet as CSV

This variant is very similar to the previous one except that instead of saving the complete spreadsheet, you only save a certain range (“Range“) as CSV. This allows you to determine more precisely what is exported and what is excluded.

Code

The range is defined via the variable myRange. Then copy the content of myRange into a new spreadsheet created by VBA. The rest is then as in the previous example simply saving the new spreadsheet.

Sub exportRangeToCSV()
    
    Dim fileNameCSV As String.
    Dim myWorkbook As Workbook
    Dim myRange As Range
    
    'use this to suppress error messages during running the macro
    Application.DisplayAlerts = False.
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products_range.csv"
    'define and copy range to new worksheet.
    Set myRange = Range("A1:E5")
    myRange.Copy
    
    Set myWorkbook = Application.Workbooks.Add(1)
    myWorkbook.Sheets(1).Range("A1").PasteSpecial xlPasteValues
 'use parameter Local to get local settings of excel, defaulf value is false
    '(VBA local setting is used that by default is English (USA)
    myWorkbook.SaveAs Filename:=fileNameCSV, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    myWorkbook.Close
    
errorhandling:
    Application.DisplayAlerts = True.
End Sub

Result

However, the result looks a little different. By copying the range into a new spreadsheet, the formatting is lost. That’s why you don’t see any currency symbols or percent signs as output here, but only the raw values.

Range of a spreadsheet was exported to CSV
CSV data of an Excel range

Compile and output CSV data manually

In the following code variant, the CSV data is first compiled manually in a string variable using For loops and then saved to a file. This method offers the greatest flexibility because, on the one hand, you can select exactly the Excel fields that are to be exported and, on the other hand, you can format the values exactly as you need them later in the CSV by inserting additional code. For example, you can decide yourself whether you want to export a currency sign or a percentage sign or whether you only want to export raw values.

In this variant, you can also define the separation sign yourself.

Code

Sub exportRangeManuallyToCSV()
    Dim FSO As New FileSystemObject ' need to set a reference to the VB "Microsoft Scripting runtime" script run-time library.
    Dim fileNameCSV As String
    Dim myWorkbook As Workbook
    Dim myRange As Range
    Dim rowNum As Integer
    Dim colNum As Integer
    Dim csvText As String.
    
    
    'use this to suppress error messages while running the macro.
    Application.DisplayAlerts = False.
    On Error GoTo errorhandling
    
    'define path and filename of csv file here
    fileNameCSV = ThisWorkbook.Path & "\" & "products_range_manuelly.csv"
    'define and copy range to new worksheet.
    Set myRange = Range("A1:E5")
    rowNum = myRange.Rows.Count
    colNum = myRange.Columns.Count
    csvText = ""
    
    For i = 1 To rowNum
        For j = 1 To colNum
            csvText = csvText & Chr(34) & myRange(i, j).Value & Chr(34) & Chr(59)
        Next.
        'remove last separator sign.
        csvText = Left(csvText, Len(csvText) - 1)
        csvText = csvText & vbCrLf
    Next
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToCreate = FSO.CreateTextFile(fileNameCSV)
    FileToCreate.Write csvText
    FileToCreate.Close
    
errorhandling:
    Application.DisplayAlerts = True.
End Sub

With this code, one goes through the desired Excel range (variable myRange) column by column and row by row. Via Chr(34) the Excel values of a cell are put in inverted commas. Chr(59) is the separation character, in this case a semicolon.

Putting them in quotes can be important if the values contain the same character as the separation character.

Result

If you then run this VBA macro, you get the following CSV data.

Values of the Excel fields were written manually into a CSV file
Manually created CSV data

Since you only access the value property of the respective Excel cell, you only have the raw values here, i.e. without currency or percentage formatting. However, you have the option of specifying in the code which formatting is to be output by adjusting the string csvText accordingly.

Leave a Reply

Your email address will not be published. Required fields are marked *