To import a CSV file into an Excel worksheet with VBA, use Application.GetOpenFilename to choose the file, open it with Workbooks.OpenText, and copy its cells into the destination sheet. The example below loads comma-delimited UTF-8 data into a worksheet named Data, replacing its existing cell contents.
VBA code to import a CSV
Put this macro in the workbook that should receive the CSV. Create a worksheet named Data first, or change the sheet name in the code. Before running any macro that replaces data, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.
Sub ImportCsvToDataSheet()
Dim csvPath As Variant
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim csvWb As Workbook
Dim sourceWs As Worksheet
Dim sourceRange As Range
Dim errorText As String
On Error GoTo ImportError
Set targetWb = ThisWorkbook
Set targetWs = targetWb.Worksheets("Data")
' Let the user choose a CSV file.
csvPath = Application.GetOpenFilename("CSV Files (*.csv),*.csv")
If csvPath = False Then Exit Sub
Application.ScreenUpdating = False
' Open UTF-8 text as comma-delimited data in a temporary workbook.
Workbooks.OpenText Filename:=CStr(csvPath), Origin:=65001, _
DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, Local:=True
Set csvWb = ActiveWorkbook
Set sourceWs = csvWb.Worksheets(1)
Set sourceRange = sourceWs.UsedRange
' Replace existing values and formulas on the destination sheet.
targetWs.Cells.ClearContents
targetWs.Range("A1").Resize(sourceRange.Rows.Count, _
sourceRange.Columns.Count).Value2 = sourceRange.Value2
csvWb.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "CSV imported into the Data worksheet.", vbInformation
Exit Sub
ImportError:
errorText = Err.Description
On Error Resume Next
If Not csvWb Is Nothing Then csvWb.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "Import failed: " & errorText, vbExclamation
End Sub
To add it in Excel for Microsoft 365 on Windows, open the workbook, choose Developer > Visual Basic, and insert a standard module with Insert > Module. Paste the code there and save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On Mac, open the VBA editor from Developer > Visual Basic; the exact keyboard shortcut can vary with the keyboard and function-key settings.
What the macro does
The file picker returns the path to the selected CSV, or False if you cancel. The macro exits on cancellation, so it does not open a file or alter the worksheet. OpenText then parses the text into a separate temporary workbook. Setting Comma to True and the other delimiter options to False makes the intended separator explicit instead of relying on Excel to guess it.
The code uses ThisWorkbook to identify the workbook containing the macro, rather than whichever workbook happens to be active. That distinction matters because opening a CSV makes the CSV workbook active. It then copies the first worksheet’s used range as values to cell A1 on Data and closes the temporary workbook without saving it. The destination is cleared first, which prevents leftover rows or columns from a previous, larger import. ClearContents removes values and formulas but leaves existing cell formatting in place.
Preparing the workbook and choosing a destination
Use a dedicated destination sheet if the macro should replace all current data. In this example, Data is required by name; a misspelled or missing sheet causes an error. Rename the sheet or update Worksheets("Data") to match your workbook. If the imported data belongs below existing records or on a different sheet, change the destination logic rather than clearing the whole sheet.
Before running the macro, check the CSV’s structure. A typical file has one record per line, fields separated by commas, and an optional header row. Fields containing commas are normally enclosed in double quotes, as in "Seattle, WA". OpenText recognizes double-quoted text here, so that comma stays inside one field instead of starting a new column. A CSV can also use a different delimiter, such as a semicolon; change the corresponding OpenText argument if that is how the source file is actually organized.
- Save the receiving workbook as .xlsm so it can store VBA.
- Make a backup or work on a copy before the first run.
- Confirm the destination sheet name and whether replacing its contents is acceptable.
- Check the source encoding and delimiter, especially when files come from another system.
CSV parsing, dates, and leading zeros
A CSV stores text fields, not Excel formatting or reliable column-type instructions. When Excel opens it, values that look like dates or numbers may be interpreted as dates or numbers. For example, an identifier written as 00127 may be displayed as 127 if Excel treats it as a number. Date strings can also be interpreted differently under different regional settings. The macro transfers the values Excel parsed in the temporary workbook; copying Value2 does not restore leading zeros or the original display format.
Origin:=65001 tells OpenText to read UTF-8 text, which is common for files that may contain accented letters or other non-ASCII characters. If the source uses a different encoding, use the appropriate code page or import option instead; the wrong encoding can produce garbled characters. Local:=True asks Excel to use local settings when interpreting data. That can affect numbers and dates, so verify representative values after import if the file crosses regional settings.
For columns that must remain text, use Excel’s import controls to specify text data types, or adapt the VBA to provide field information for the known columns. A generic automatic import cannot safely infer that every number-like string is an identifier. If exact preservation of every character matters, test the import with sample values such as 00127, 3/4/2026, and a long account number before processing the full file.
Common problems and fixes
- The macro says the subscript is out of range. The workbook does not contain a worksheet named Data. Add it, or change the name in the code to the exact sheet tab name.
- Everything appears in one column. Check the actual delimiter. For a semicolon-separated file, set Semicolon:=True and Comma:=False. Do not turn on multiple delimiters unless the file really uses them.
- Columns shift unexpectedly. Fields with commas must be quoted correctly in the CSV. Inspect the source around the affected row; malformed quoting can make any parser assign fields to the wrong columns.
- Old values remain below the new data. The example clears the entire destination sheet’s contents before copying. If you removed that line to preserve other data, explicitly clear the previous import area or stale rows can remain.
- The import fails because macros are disabled. Enable macros only for workbooks and files you trust, using your organization’s security guidance. Saving as .xlsx removes VBA code, so keep the macro workbook in .xlsm format.
If an error occurs after the CSV opens, the error handler attempts to close the temporary workbook and restore screen updating. If you modify the macro to disable other Excel settings, such as events or calculation, restore each setting to its original value on both success and failure.
Importing repeatedly and handling larger files
For a recurring task, keep the macro in a controlled .xlsm workbook and use a consistent destination sheet and CSV layout. You can replace the file picker with a fixed path or a cell containing the path, but validate that the file exists and is the expected one before importing. A file picker is safer for occasional manual use because it makes the selected source visible to the person running the macro.
This example copies the entire used range through a two-dimensional value assignment, rather than copying and pasting cell by cell. That is generally a straightforward way to transfer a rectangular dataset and avoids bringing over source formatting. UsedRange can sometimes include cells Excel considers used beyond the visibly populated data, particularly in workbooks that have been edited or formatted extensively. A plain CSV opened by Excel is usually simple, but inspect the copied boundaries if unexpected blank rows or columns appear.
For very large files, memory use and import duration can become relevant because Excel opens the CSV as a workbook before copying it. If the file is too large for a worksheet, split or process it with a tool designed for larger datasets. If the same transformation must be repeated on many files, Power Query may be easier to maintain: in Excel for Windows, use Data > Get Data > From File > From Text/CSV, review the preview and data types, then load the result. Power Query is also available in current Excel for Mac releases, though available connectors and interface details can differ by version.
Safety and alternatives
CSV files can contain untrusted content. Some spreadsheet programs may interpret a field beginning with a formula marker as a formula when opening a file. Do not treat a CSV as harmless merely because it is plain text: check files from unknown sources, and avoid opening suspicious content in a workbook with sensitive data or enabled macros. The sample macro imports by opening the CSV in Excel, so use it only with files you trust and review the resulting data.
For a one-time import, you may not need VBA at all. Choose Data > From Text/CSV to preview the delimiter, encoding, and data types before loading. This is a useful alternative when preserving text identifiers or checking a file’s structure matters more than automation. For repeated refreshes, Power Query can save the import steps for reuse. VBA is most useful when the import is part of a larger button-driven workflow, must run under specific workbook rules, or needs to be combined with other automation.
Frequently Asked Questions
Can VBA import a CSV into the active worksheet?
Yes. Change the destination assignment to use ActiveSheet, but be aware that opening the CSV changes the active workbook and worksheet. Store a reference to the intended destination worksheet before opening the CSV, or use ThisWorkbook.Worksheets(“Data”) as in the example.
Does this macro overwrite the worksheet?
It clears all cell contents on Data before importing, so existing values and formulas there are removed. Cell formatting remains. Save a copy first, or remove the clearing line and define a safe destination area if existing content must be retained.
How do I import a semicolon-delimited CSV?
In the OpenText arguments, use Semicolon:=True and Comma:=False. Confirm the file’s actual delimiter rather than changing this based only on regional settings; CSV files do not all use the same separator.
Why did Excel remove zeros at the start of a value?
Excel likely interpreted that field as a number. CSV text does not preserve Excel number formats, and copying the parsed value cannot restore removed zeros. Import the relevant column as text, or transform it using a known identifier format after confirming the required width.
Can I run this macro on Excel for Mac?
Excel for Mac supports VBA and the core methods used here, but macro permissions and some interface details vary. Enable macros only for trusted files, and test the import on a copy, particularly if the CSV uses unusual encoding or regional date and number formats.
Related
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro to Clear Cell Contents: VBA Steps and SafetyTo clear cell contents with an Excel macro, use VBA’s ClearContents method on the cells you want to…
- VBA Add Worksheet After the Last SheetTo add a worksheet after the last sheet tab in Excel, use Worksheets.Add with the last item in…
- Excel VBA: How to Check Whether a Workbook Is OpenTo check whether a workbook is open in Excel VBA, loop through Application.Workbooks and compare each open workbook’s…
- Excel VBA: Add a New Worksheet With a Specific NameIn Excel VBA, add a worksheet with Worksheets.Add, then set its Name property. For example, the key steps…
- Excel VBA Evaluate: How It Works and When to Use ItIn Excel VBA, Application.Evaluate interprets a text expression using Excel’s formula language and returns the result as a…
- VBA Find a Row With a Specific Value in an Excel SheetTo find a row with a specific value in Excel VBA, use the Find method on the relevant…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.