To import a CSV into an Excel worksheet with VBA, open the file with Workbooks.OpenText, then copy its parsed cells into the destination sheet. The macro below prompts you to choose a CSV, imports comma-separated fields, and places the results in a worksheet named Data; save a copy of your workbook before running it because macro actions cannot be undone with Ctrl+Z.
Choose an Import Method
The right VBA method depends on whether you need a one-time transfer, a repeatable refresh, or precise control over how columns are interpreted. CSV files contain text separated by delimiters; they do not store Excel formatting, formulas, or reliable column-type metadata. Excel must decide whether each field represents text, a number, a date, or something else when it reads the file.
- Use Workbooks.OpenText when you want VBA to open and parse a CSV using familiar import options, then copy the results into another workbook or sheet. This is a practical choice for general-purpose macros.
- Use a QueryTable or Power Query when you need a refreshable connection to a file, repeatable transformation steps, or more control over column types. Power Query is available through Excel’s Data tab, but its exact features can vary by Excel version.
- Read the file as text in VBA when the CSV has unusual rules or every character must be preserved exactly. This requires code to parse quoted fields correctly; splitting each line at commas is not sufficient for a standards-compliant CSV.
For an ordinary CSV with a header row, OpenText is often the clearest starting point. It applies delimiter and text-qualifier rules during import, rather than treating every comma as a field boundary regardless of quotation marks.
VBA Macro to Import a CSV
In Excel for Microsoft 365 on Windows, save the destination workbook as an Excel Macro-Enabled Workbook (.xlsm). Press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste this procedure into the module. Create a worksheet named Data before running it. On Mac, use Option+F11 to open the editor; depending on the keyboard settings, you may also need to hold Fn. The code uses standard Excel workbook and worksheet objects.
Option Explicit
Sub ImportCsvToData()
Dim csvPath As Variant
Dim destinationWb As Workbook
Dim targetWs As Worksheet
Dim csvWb As Workbook
Dim csvWs As Worksheet
Dim sourceRange As Range
Dim errorMessage As String
On Error GoTo ImportError
Set destinationWb = ThisWorkbook
Set targetWs = destinationWb.Worksheets("Data")
' Ask the user to choose the CSV file.
csvPath = Application.GetOpenFilename( _
"CSV Files (*.csv),*.csv", , "Select a CSV file")
If VarType(csvPath) = vbBoolean Then Exit Sub
' Open and parse the comma-delimited file as a new workbook.
Workbooks.OpenText Filename:=CStr(csvPath), Origin:=65001, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, Local:=True
Set csvWb = ActiveWorkbook
Set csvWs = csvWb.Worksheets(1)
Set sourceRange = csvWs.UsedRange
' Copy values only, beginning at cell A1 on the destination sheet.
targetWs.Range("A1").Resize( _
sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value
csvWb.Close SaveChanges:=False
MsgBox "CSV data imported to the Data worksheet.", vbInformation
Exit Sub
ImportError:
errorMessage = Err.Description
If Not csvWb Is Nothing Then csvWb.Close SaveChanges:=False
MsgBox "CSV import failed: " & errorMessage, vbExclamation
End Sub
The macro uses Origin:=65001 to request UTF-8 decoding, which helps preserve characters such as accented letters when the file is actually UTF-8. If the source uses another encoding, the appropriate origin may differ. After opening the file, it copies the imported cells’ values into Data starting at A1. It does not copy source formatting or formulas, which CSV files do not contain.
What the Macro Does
GetOpenFilename displays a file picker and returns the selected path. If the user cancels, the returned value is Boolean False and the procedure exits without importing. Workbooks.OpenText then opens the chosen file as a workbook and tells Excel how to divide its contents.
The options specify a delimited file with commas as separators, double quotes as text qualifiers, and no tab, semicolon, space, or additional delimiter. With a double-quote text qualifier, a field such as "North, West" is treated as one value rather than two fields. The imported data is read from the first worksheet in the temporary CSV workbook. UsedRange supplies the block to copy, and Resize makes the destination range the same size.
ThisWorkbook means the workbook containing the macro, not whichever workbook happens to be active. That distinction matters because opening the CSV makes it the active workbook. The code keeps a separate reference to the destination and closes the temporary CSV without saving it after the values have been copied.
Preserve IDs, Dates, and Other Sensitive Fields
Automatic type detection is the most important limitation to consider. A value such as 000742 may be interpreted as the number 742, losing its leading zeros. A long account number may be converted to scientific notation or rounded if Excel treats it as a number. A string such as 03/04/2026 can be interpreted as a date, and the month and day may be ambiguous under different regional settings.
If a column is an identifier rather than a quantity, import it as text. One way is to define column types through the import settings or use a refreshable Power Query process with the relevant columns explicitly set to Text. For a fixed layout, OpenText also has a FieldInfo argument that can assign column formats, but its array must match the file’s columns. Do not apply a one-column example blindly to a file whose layout changes.
Formatting the destination cells as Text after the import does not restore zeros or digits that Excel has already discarded. Choose the data type at import time, or retain the original field as text before converting only the columns that truly need numeric or date values. Check the source system’s rules before deciding how an identifier should be stored.
Importing Into an Existing Sheet Safely
The sample writes from A1 but does not clear the destination first. That is deliberate: clearing a worksheet automatically could delete formulas, notes, or unrelated data. However, if a new CSV has fewer rows or columns than the previous one, old values outside the new imported block will remain. Decide whether to append, replace, or refresh data before adapting the macro.
- Replace a dedicated import area: clear only the known data range, or clear the worksheet’s contents if it is reserved exclusively for the import. Keep headers, formulas, and other required cells out of the cleared area.
- Append records: find the next available row in the destination and copy the new records there. Decide whether the CSV header should be skipped so it is not repeated between data batches.
- Refresh a managed dataset: use Power Query or a QueryTable when users need a repeatable refresh and documented transformations rather than a one-time copy.
Also check whether the destination is an Excel table. Writing beyond a table’s current size may not automatically give the added cells the structure or formulas you expect. If the table is part of a reporting process, resize or update it intentionally and verify that calculated columns and downstream summaries include the new records.
Encoding, Delimiters, and Regional Settings
Not every CSV uses commas. Some systems export semicolon-separated data, often because commas are used as decimal marks in the local number format. If the file uses semicolons, change the delimiter options so Semicolon:=True and Comma:=False. For a tab-delimited text file, enable the tab option instead. Selecting more than one delimiter can split a field unexpectedly.
CSV is a family of text-file conventions, not a guarantee that every producer follows identical rules. Quotes can surround fields containing commas, line breaks, or quote characters, and an embedded quote is commonly represented by two quote characters. The text qualifier setting handles standard quoted fields during Excel’s import. If the file has nonstandard escaping or inconsistent rows, inspect a copy of the source and consider Power Query or a dedicated parser rather than assuming each line can be divided with a simple comma split.
Excel’s interpretation of dates and decimal values can depend on regional settings. In the macro, Local:=True asks Excel to use local settings when interpreting the file. That may be appropriate for files produced in the same locale, but it does not make an ambiguous date unambiguous. Confirm how the exporting system defines dates and decimals, and compare a few imported rows against the original text.
Run Macros With Appropriate Safeguards
Before running any import macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so the Undo command is not a recovery plan. The sample copies values into Data, and rerunning it can overwrite values in the same range. Review the selected file and target sheet before relying on the result.
Keep the macro in a trusted workbook and enable macros only for files and sources you trust. Excel may block macros downloaded from the internet or display a security warning; do not lower global security settings merely to run an unknown file. On Windows, the Developer tab can be shown through File > Options > Customize Ribbon, then checking Developer. Excel for the web can open a macro-enabled workbook but cannot run VBA macros; use the desktop Excel application. Excel for Mac supports VBA, though some Windows-only components and automation features are unavailable. The sample avoids Windows-specific file-system objects.
Common Problems and Fixes
- “Subscript out of range”: the destination workbook does not contain a sheet named Data, or the name is spelled differently. Rename the sheet or change the worksheet name in the code.
- Columns appear in one cell: the file may use a different delimiter, or the import options may not match the file. Check a few raw lines in a text editor, then set the corresponding delimiter option.
- Leading zeros disappear: Excel inferred a numeric type. Import that column as text rather than trying to add zeros back after the value has changed.
- Characters look wrong: the file’s encoding may not be UTF-8, or it may have been exported with a different code page. Confirm the source encoding and adjust the import method or origin setting.
- Old values remain below the new data: the new file is shorter, and the sample intentionally does not clear the destination. Clear only the designated import area before copying if replacement is intended.
Frequently Asked Questions
Can VBA import a CSV without opening it on screen?
Yes. VBA can read a text file or use a query connection without displaying a workbook window, but the parsing and type-control code is more involved. OpenText is easy to understand because Excel applies its import rules, while a query connection is useful for refreshable data.
How do I import a CSV into a specific worksheet?
Set a worksheet variable to the destination, as the example does with ThisWorkbook.Worksheets("Data"), then assign the imported range to the desired starting cell. Change A1 to another cell if the import should begin elsewhere.
Does a CSV preserve Excel formatting or formulas?
No. A CSV stores plain text fields and delimiters, not worksheet formatting, formulas, multiple sheets, or Excel tables. The macro transfers the values Excel reads from the file; any formatting or formulas must be applied separately in the workbook.
Can I import a CSV automatically when the workbook opens?
Yes, an import procedure can be called from a workbook event such as Workbook_Open, but unattended imports need a reliable file path, error handling, and safeguards against processing the wrong or incomplete file. Start with a manual run and add automation only after the workflow is dependable.
Can I run this macro in Excel for the web?
No. Excel for the web does not execute VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro; for browser-based automation, consider Office Scripts where the required workflow is supported.
Related
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
- Excel Text to Columns: How to Undo and Restore Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Compile Error: Common Causes and How to Fix ThemAn Excel VBA compile error means the Visual Basic for Applications editor cannot compile some code in the…
- Excel Macro Disabled? How to Enable Macros SafelyIf an Excel macro is disabled, first confirm the workbook is from a source you trust, then check…
- Columns in Excel VBA: Select, Count, and Use ThemIn Excel VBA, use a worksheet’s Columns property to refer to one or more entire worksheet columns, such…
- Clear Function in Excel VBA: Add a Button to Clear CellsTo clear cells with a button in Excel VBA, use the ClearContents method in a macro, then assign…
- Excel Macro Filter: How to Automate Data FilteringTo filter a worksheet with a macro, use VBA’s AutoFilter method on a range that includes its header…
- VBA Excel Reverse String: Code, Examples, and TipsTo reverse text in Excel VBA, use the built-in StrReverse function: StrReverse("Excel") returns "lecxE". For a reusable worksheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.