[VBA] Automate CSV Imports to Excel: No More Copy-Pasting

2025年12月29日

Background

I used to manually copy and paste the contents of CSV files into Excel. This process was repetitive and time-consuming, so I created a VBA macro to import CSV files directly.

This is particularly efficient when handling database files exported in CSV format, allowing for a fully automated workflow.

The VBA Code

Below is the macro that allows you to select a CSV file and automatically load its contents into the active sheet. I have also included a separate macro to clear existing data from the sheet.

CSV Import Macro

Sub ImportCSVFile()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim csvFilePath As String
    Dim targetSheet As Worksheet

    With fd
        .Title = "Select a CSV File"
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .AllowMultiSelect = False
        If .Show = True Then
            csvFilePath = .SelectedItems(1)
        Else
            MsgBox "No file selected."
            Exit Sub
        End If
    End With

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set targetSheet = ActiveSheet

    Dim fileNum As Integer
    Dim lineContent As String
    Dim lineArray() As String
    Dim rowNum As Long
    Dim colNum As Long

    fileNum = FreeFile
    Open csvFilePath For Input As #fileNum

    rowNum = 1
    Do Until EOF(fileNum)
        Line Input #fileNum, lineContent
        lineArray = Split(lineContent, ",")
        For colNum = LBound(lineArray) To UBound(lineArray)
            targetSheet.Cells(rowNum, colNum + 1).Value = lineArray(colNum)
        Next colNum
        rowNum = rowNum + 1
    Loop

    Close #fileNum

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    MsgBox "File has been imported successfully."
End Sub

Macro to Clear Active Sheet

Sub ClearActiveSheet()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    ws.Cells.ClearContents

    Application.ScreenUpdating = True
    Application.EnableEvents = True

    MsgBox "All data has been cleared from the active sheet."
End Sub

How It Works

When you run the macro, a file dialog appears, allowing you to select a CSV file.
The contents of the selected file are read line by line.
Each line is split by commas and entered into the cells of the active sheet.
Screen updating and events are temporarily disabled during the process to improve performance.

Notes on Character Encoding and Speed

I initially tried using the standard Excel import feature, but I encountered issues with garbled text (character encoding errors). To fix this, I switched to the method of reading the file line-by-line and splitting it using the Split function.

This method is effective for preventing text encoding issues, but please be aware that it may take a little longer to process if the CSV file has a very large number of rows.

Summary

Automation: Eliminates the need for manual copy-pasting.
Flexibility: Uses a file dialog to choose any file easily.
Reliability: Reading line-by-line helps avoid character encoding errors.
Efficiency: Great for routine tasks, especially when combined with the clear sheet macro.

If you frequently work with CSV files, this macro can significantly speed up your daily workflow.