Visual Basic for Applications:
Working with CSV

How to:

Visual Basic for Applications (VBA) simplifies working with CSV files through built-in functions and methods that seamlessly allow reading from and writing to these files. Below are examples illustrating basic operations with CSV files.

Reading a CSV File:

Sub ReadCSV()
    Dim filePath As String
    filePath = "C:\example.csv"
    
    Open filePath For Input As #1
    
    Do Until EOF(1)
        Dim line As String
        Line Input #1, line
        Dim dataFields() As String
        dataFields = Split(line, ",")
        
        'Process the dataFields array as needed
        Debug.Print Join(dataFields, ";") 'Example output showing conversion from commas to semicolons
    Loop
    
    Close #1
End Sub

Writing to a CSV File:

Sub WriteCSV()
    Dim filePath As String
    filePath = "C:\output.csv"
    Dim dataToWrite As String
    dataToWrite = "ID,Name,Age" & vbCrLf & "1,John Doe,30" & vbCrLf & "2,Jane Doe,29"
    
    Open filePath For Output As #1
    Print #1, dataToWrite
    Close #1
End Sub

Sample Output in output.csv:

ID,Name,Age
1,John Doe,30
2,Jane Doe,29

Deep Dive

Historically, CSV files have been a straightforward method to store tabular data in a text format. The simplicity of its structure, where each line corresponds to one data record and each field within a record is separated by a comma, is both CSV’s strength and its limitation. The format does not natively support data types, which means all data is stored as strings, and the burden of converting data to the correct type falls on the programmer.

In Visual Basic for Applications, dealing with CSV files is mostly done through basic file operations, as shown in the earlier examples. There is no direct CSV parsing support like in more modern languages (e.g., Python’s csv module), which provides more control and convenience when handling CSV data.

For more complex operations or when working with large CSV files, programmers might find better alternatives outside of pure VBA, such as leveraging external libraries or using other programming languages equipped with more sophisticated CSV handling capabilities. However, for simple tasks related to CSV files, VBA’s straightforward approach is often sufficient and easy to implement, offering a quick solution for Excel-based applications or other Microsoft Office software automation.