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.
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.
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
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.
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.
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.
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
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.
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.
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.
If you then run this VBA macro, you get the following 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.