Automated Export from Excel to CSV: An Implementation Guide with VBA

Are you looking for an efficient way to export Excel data to a CSV file? This article introduces a program that uses Visual Basic for Applications (VBA) to save data from an Excel sheet as a CSV file. The program operates independently of specific sheets and can execute actions on any selected sheet. There is also an option for the user to specify the filename for the CSV, and the CSV output is completed after a simple confirmation procedure. Improve your workflow with this practical guide, which includes a code sample and its explanation.

Used Technologies
Features
Sauce Code
How to Use

Used Technologies

Variable declaration: Variables are declared with specified data types (such as String, Long, Variant, etc.).
ActiveSheet: Used to work with the currently active sheet in Excel.
Range object: Used to perform operations on cells or ranges of cells.
MsgBox function: Displays a confirmation message to the user and retrieves the selection result.
InputBox function: Used to receive text input from the user.
File I/O: Performs basic file operations (Open, Print, Close) to output CSV as a text file.
For Loop: Executes a loop to process each element of an array sequentially.
UBound function: Used to obtain the highest index of an array.
End method: Used to find the last row or column that contains data.
ThisWorkbook.Path: Retrieves the path to the location where the current workbook is saved.
VbMsgBoxResult: An enumeration to store the results returned from the MsgBox function.

Features

Variable declaration: The necessary variables are declared.
Retrieving the active sheet: The currently active Excel worksheet is stored in a variable named ws.
Inputting the CSV file name: The user is prompted to enter a filename for the CSV.
User confirmation: The user is asked to confirm whether to create a CSV from the selected sheet.
Setting the file path: Combines the selected filename with the current workbook’s path to create the save path for the CSV file.
Identifying the last row and column: Finds the last row and column with data in the active sheet.
Retrieving cell data: Stores the cell data from the active sheet in an array.
Creating the CSV file: Opens a file in output (create) mode at the generated file path for the CSV file.
Writing the data: Writes the cell data to the file in CSV format.
Closing the CSV file: Closes the CSV file once the data has been written.
Success message: Notifies the user with a message box that the CSV export was successful.
Cancellation process: The script exits if the user cancels the operation.
Reselecting the worksheet: Finally, reactivates the original worksheet.

This script uses Excel’s VBA (Visual Basic for Applications) to provide the functionality to export the contents of an Excel sheet to a CSV file.

Sauce Code

Sub ExportToCSV()

    ' Variable declaration
    Dim filePath As String
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim cellData As Variant
    Dim i As Long, j As Long
    Dim ws As Worksheet
    Dim inputDate As String
    Dim userConfirm As VbMsgBoxResult
    Dim fileName As String

    ' Initialization
    Set ws = ActiveSheet
    
    ' Prompt the user to enter the name of the CSV file
    fileName = InputBox("Please enter the name of the CSV file.")
    
    ' Confirm with the user
    userConfirm = MsgBox("Are you sure you want to create a CSV with the following sheet?" & vbCrLf & vbCrLf & ws.Name, vbYesNo)
    
    ' If the user confirmed
    If userConfirm = vbYes Then
    
        ' Set the file path and name
        filePath = ThisWorkbook.Path & "\" & fileName & ".csv"
        
        ' Find the last row and column with data
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        
        ' Load the cell range into a variable
        cellData = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn)).Value
        
        ' Open the file for output
        Open filePath For Output As #1
        
        ' Loop through each row in the array
        For i = 1 To UBound(cellData, 1)
            ' Write the first cell of the row
            Print #1, CStr(cellData(i, 1));
            
            ' Loop through each column in the row
            For j = 2 To UBound(cellData, 2)
                ' Write the cell data, separated by commas
                Print #1, "," & cellData(i, j);
            Next j
            
            ' Move to the next line in the CSV file
            Print #1,
        Next i
        
        ' Close the file
        Close #1
        
        ' Notify the user that the CSV output was successful
        MsgBox "CSV output was successful."
        
    Else
        ' If the user canceled the operation
        Exit Sub
    End If
    
    ' Reactivate the original worksheet
    ws.Activate

End Sub

' MIT License
' Copyright (c) 2023 [ANJI]
'
' Permission is hereby granted, free of charge, to any person obtaining a copy
' of this software and associated documentation files (the "Software"), to deal
' in the Software without restriction, including without limitation the rights
' to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
' copies of the Software, and to permit persons to whom the Software is
' furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all
' copies or substantial portions of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
' IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
' FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
' AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
' LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
' OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
' SOFTWARE.

How to Use

Open Excel File: Open the target Excel file.
Copy Macro: Copy the above VBA code and paste it into Excel’s VBA editor (opened with Alt + F11).
Verify Active Sheet: This program reads data from the active sheet (the currently selected sheet). Ensure the correct sheet is active.
Run Macro: Execute this macro (ExportToCSV) within the VBA editor or the Excel interface (using the F5 key or the ‘Run’ button).
Enter File Name: A prompt will appear during execution to enter the name of the CSV file.
Confirmation Dialog: Next, a dialog will confirm whether to create the CSV file from the selected sheet.
CSV Output: If you answer ‘Yes’, the CSV file will be created with the specified filename.
Completion Message: If the CSV file is successfully generated, a message box stating “CSV output was successful.” will appear.
Return to Original Sheet: After completion, the original sheet will become active again.
Using this program, you can easily export Excel sheets to a CSV file.

Note that this program only converts the data from the active sheet to CSV, so if you want to output multiple sheets, you need to activate each one and run the process individually.