[Excel VBA] How to Import CSV Data to a Specific Location Using QueryTable

2025年12月29日

When importing CSV files into Excel, simply opening the file and copying the data can be tedious. Using the VBA QueryTable feature allows you to automate this process and gives you flexible control over how the data is loaded.

This article explains how to use Excel VBA to import a UTF-8 CSV file into a specific cell and specify the data type for each column.

Overview of the Process

Target CSV: import_data.csv (UTF-8 encoding)
Import Location: Specific cell on the sheet (Cell D3 in this example)
Delimiter: Comma
Column Formatting: Define specific types (Text, Date, Number, etc.) for each column
Cleanup: Delete the QueryTable definition after loading to keep the sheet simple

VBA Code: Importing CSV using QueryTable

Sub ImportCSVWithQueryTable()
    With ActiveSheet.QueryTables.Add( _
        Connection:="TEXT;" & ThisWorkbook.Path & "\import_data.csv", _
        Destination:=Range("D3") _
    )
        .AdjustColumnWidth = True
        .TextFilePlatform = 65001 ' Specify UTF-8
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        ' Array defines format for 5 columns: 1=General, 5=Date, 2=Text, etc.
        .TextFileColumnDataTypes = Array(1, 5, 2, 1, 2) 
        .Refresh BackgroundQuery:=False
        .Delete ' Delete the query definition to leave only the data
    End With
End Sub

Details of Each Parameter

Parameter	Description
Connection	The path of the CSV file to import.
Destination	The starting cell for writing data (e.g., `Range("D3")`).
TextFilePlatform	Specifies the character encoding. Use `65001` for UTF-8 to prevent garbled text.
TextFileCommaDelimiter	Set to `True` to import as comma-separated values.
TextFileColumnDataTypes	Defines the format for each column (see table below).
Refresh	Executes the data import.
Delete	Removes the QueryTable object after importing, leaving only the static data.

Formatting Codes for TextFileColumnDataTypes

Value	Format Type
1	General (Automatic detection)
2	Text
5	Date (YMD)
9	Skip column (Do not import)

Application Tips

Tab Delimiter: If your file uses tabs instead of commas, change the code to .TextFileTabDelimiter = True.
Change Destination: Change Range("D3") to Range("A1") or any other cell to match your desired layout.
Keep the Connection: If you remove the .Delete line, the connection remains active. This allows you to update the data later by clicking “Refresh” in Excel.

Important Notes

Overwriting: Be careful, as existing data in the destination range will be overwritten.
Column Count: If the number of elements in the TextFileColumnDataTypes array is fewer than the actual columns in the CSV, the remaining columns will be imported as “General.”
Connection Cleanup: If you omit .Delete, the external connection remains in the workbook. Use .Delete if you only need the static data.

Summary

By using Excel VBA’s QueryTables, you can fully control CSV imports and automate formatting tasks.

This method is ideal for loading routine data because it allows you to precisely set column formats, character encoding, and the exact paste location. If you frequently open and process the same CSV files, this technique will significantly improve your efficiency.

よかったらシェアしてね！