Export Data to CSV with Excel VBA

Objective:

You want to export data from an Excel sheet to a CSV file using VBA. The process involves:

1. Selecting a range of data.
2. Saving the range as a CSV file.
3. Handling errors and managing file naming dynamically.

Let’s break this down step by step, starting with a detailed explanation and then presenting the VBA code.

Step-by-Step Explanation

1. Selecting the Data Range:
   • The first thing we need is to identify the range of data to export. This can either be a specific range (e.g., A1:C10), or it can be the entire used range of the sheet, which can be dynamic depending on how much data is in the sheet.
2. Creating the CSV File:
   • The next step is to define the path and file name for the CSV. We will ask the user for a location or set a default path. This is important because CSV files are plain text files, and each value in the range is separated by a comma (,), while each row ends with a newline character.
3. Handling File Overwrite/Name Duplication:
   • We need to check if a CSV file with the same name already exists in the destination folder. If it exists, we’ll prompt the user to either overwrite or choose a new file name.
4. Exporting the Data:
   • We’ll convert the range into text format and write it into the CSV file. Each cell in the selected range will be separated by a comma, and each row will end with a newline.
5. Error Handling:
   • We need to handle possible errors, such as if the file path is invalid, or the user cancels the file save dialog.

VBA Code to Export Data to CSV

Sub ExportDataToCSV()
    ' Declare variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim fileName As String
    Dim folderPath As String
    Dim filePath As String
    Dim csvContent As String
    Dim result As Integer   
    ' Reference to the active worksheet
    Set ws = ActiveSheet   
    ' Select the range to export - You can customize the range as needed
    ' Here, we are selecting the used range of the worksheet
    Set rng = ws.UsedRange   
    ' Ask the user where to save the CSV file and what name to give it
    ' You can also set a default directory or filename if preferred
    folderPath = Application.GetSaveAsFilename( _
        InitialFileName:=ws.Name & ".csv", _
        FileFilter:="CSV Files (*.csv), *.csv", _
        Title:="Save As CSV File")
    ' If the user cancels the Save As dialog, exit the sub
    If folderPath = "False" Then Exit Sub   
    ' Check if the file already exists
    If Dir(folderPath) <> "" Then
        ' Ask if they want to overwrite the file
        result = MsgBox("The file already exists. Do you want to overwrite it?", vbYesNo + vbExclamation, "File Exists")
        If result = vbNo Then Exit Sub
    End If   
    ' Build the CSV content from the range
    csvContent = ""
    For Each row In rng.Rows
        For Each cell In row.Cells
            ' Add the cell value to the CSV string, with quotes around text values
            If IsNumeric(cell.Value) Or IsDate(cell.Value) Then
                csvContent = csvContent & cell.Value
            Else
                csvContent = csvContent & """" & cell.Value & """"
            End If
            ' Add a comma if it's not the last column in the row
            If cell.Column < row.Cells.Count Then
                csvContent = csvContent & ","
            End If
        Next cell       
        ' Add a line break after each row (except the last row)
        csvContent = csvContent & vbCrLf
    Next row   
    ' Open the file for output and write the CSV content
    Open folderPath For Output As #1
    Print #1, csvContent
    Close #1   
    ' Notify the user the export was successful
    MsgBox "Data exported successfully to " & folderPath, vbInformation, "Export Completed"
End Sub

Breakdown of the Code:

1. Worksheet Reference:
   • Set ws = ActiveSheet assigns the currently active worksheet to the variable ws.
2. Range to Export:
   • Set rng = ws.UsedRange defines the range of data to export. In this case, it uses the UsedRange, which automatically selects all the cells that contain data.
3. Get Save Location:
   • folderPath = Application.GetSaveAsFilename(…) opens a Save As dialog, allowing the user to specify the file name and location. The file filter ensures that the user can only select .csv files.
4. Check if the File Exists:
   • The Dir(folderPath) function checks if a file with the same name already exists at the given path. If it does, a message box appears, asking the user if they want to overwrite the file.
5. Build the CSV Content:
   • A loop is used to iterate through each row and each cell within the row. For each cell, the value is added to the csvContent string.
   • Text values are enclosed in double quotes («  »), and the cell values are separated by commas. After each row, a newline character (vbCrLf) is added.
6. Write to File:
   • Open folderPath For Output As #1 opens the selected CSV file for writing. The Print #1, csvContent writes the constructed CSV content into the file. After writing, Close #1 closes the file.
7. Confirmation Message:
   • Once the export is complete, a message box notifies the user of the successful export.

Customization & Additional Features:

• Selecting a Different Range:
  Instead of UsedRange, you could define a custom range. For example, if you want to export from A1 to C10, use Set rng = ws.Range(« A1:C10 »).
• Text Qualifier:
  In the code, text values are enclosed in double quotes. This is useful to handle values that contain commas, which is important in CSV files.
• Error Handling:
  You can add error handling (e.g., On Error GoTo ErrorHandler) to manage potential errors, such as invalid file paths or permission issues.

Conclusion:

This VBA script provides a robust method for exporting data from Excel to a CSV file. It includes user interaction through the Save As dialog, file overwrite prevention, and properly formatted CSV output. You can customize it further depending on your needs, such as selecting specific ranges, adding headers, or formatting data.