How to Automatically Open and Format CSV Files with Excel VBA

CSV files (Comma-Separated Values) are a common format used for exchanging data between systems and web services. In this article, I will introduce a method to automatically open a CSV file in Excel and adjust the column widths using VBA.

Scenario Overview

• Assume there is a CSV file named “data.csv” in the same folder as your Excel file.
• When you run the macro, the CSV file is loaded into Excel, and the width of all columns is automatically adjusted.

VBA Code: Open CSV and Auto-Adjust Column Width

Sub OpenAndFormatCSV()
    Dim csvPath As String
    csvPath = ThisWorkbook.Path & "\data.csv"
    
    Workbooks.OpenText _
        Filename:=csvPath, _
        DataType:=xlDelimited, _
        Comma:=True

    ActiveSheet.Range("A1").CurrentRegion.EntireColumn.AutoFit
End Sub

Code Explanation

• ThisWorkbook.Path: This retrieves the folder path where the current Excel macro file is saved.
• Workbooks.OpenText: This method allows you to import the CSV file into Excel.
• DataType:=xlDelimited and Comma:=True: These settings ensure the data is correctly read as comma-separated values.
• AutoFit: Finally, this adjusts the width of all columns in the loaded sheet to fit the content.

Advanced Tips

• Different Delimiters: You can add Semicolon:=True to support semicolon-separated CSV files.
• Formatting: You can add more code after loading the file to change fonts, colors, or borders. This allows you to automatically create a formatted report.

Important Notes

• Character Encoding: If the CSV file uses a character encoding other than Shift-JIS (such as UTF-8), the text may not display correctly (this is called “Mojibake”).
• Window Management: The Workbooks.OpenText method opens the file in a new window. Please be careful when adding code to save or close the file.

Summary

By using the method introduced in this article, you can open CSV files in Excel without hassle and immediately have them ready for work. Please use this to improve the efficiency of your daily routine tasks.