To import a CSV file with VBA, use Excel’s Workbooks.OpenText method to parse it with the correct delimiter and encoding. The macro below prompts for a file, reads it as UTF-8 comma-delimited data, and copies the results to a new worksheet in the workbook that contains the macro.
How CSV importing works in Excel VBA
A CSV file stores rows of data as text, with values separated by a delimiter such as a comma or semicolon. It does not carry Excel worksheet features such as formulas, formatting, multiple tabs, or column data types. When Excel opens a CSV, it interprets the text and turns it into cells. VBA can automate that process, but the import settings matter: the wrong delimiter can put an entire row in one cell, while automatic type detection can change an identifier or date.
For a straightforward import, Workbooks.OpenText is useful because it lets the macro specify how Excel should split and interpret the file. In the example below, the CSV is opened as a temporary workbook, then its used range is copied as values to a newly added sheet in ThisWorkbook. The temporary workbook is closed without saving. Put the macro in the workbook that should receive the data; if the macro is stored elsewhere, ThisWorkbook refers to that other workbook instead.
Import a CSV to a new worksheet
Before running any macro, save a separate copy of the destination workbook. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover from an unintended import or overwrite. Save the working file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain the macro.
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste in this procedure. On Mac, use Option+F11 (the Fn key may also be needed on some keyboards) or the Visual Basic command on the Developer tab. Excel for the web does not run VBA macros; open the workbook in the desktop application.
Sub ImportCsvToNewSheet()
Dim filePath As Variant
Dim targetBook As Workbook
Dim csvBook As Workbook
Dim targetSheet As Worksheet
Dim sourceRange As Range
' Keep a reference to the workbook that will receive the data.
Set targetBook = ThisWorkbook
' Let the user choose a CSV file; False means the dialog was canceled.
filePath = Application.GetOpenFilename("CSV Files (*.csv),*.csv", , "Choose a CSV file")
If VarType(filePath) = vbBoolean Then Exit Sub
On Error GoTo ImportError
Application.ScreenUpdating = False
' Open as UTF-8 text separated by commas, with quoted text handled.
Workbooks.OpenText Filename:=CStr(filePath), Origin:=65001, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, Local:=False
Set csvBook = ActiveWorkbook
Set sourceRange = csvBook.Worksheets(1).UsedRange
Set targetSheet = targetBook.Worksheets.Add( _
After:=targetBook.Worksheets(targetBook.Worksheets.Count))
' Transfer cell values without copying the temporary workbook's formatting.
targetSheet.Range("A1").Resize(sourceRange.Rows.Count, _
sourceRange.Columns.Count).Value = sourceRange.Value
csvBook.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "CSV data imported to " & targetSheet.Name, vbInformation
Exit Sub
ImportError:
Application.ScreenUpdating = True
MsgBox "Import failed: " & Err.Description, vbExclamation
End Sub
After selecting a file, the macro adds a worksheet at the end of the destination workbook. Excel assigns its default sheet name; you can rename it afterward. The first CSV row is imported as data, so if that row contains column headings, those headings appear in row 1. The macro does not delete or replace existing sheets.
The code copies values rather than the temporary sheet’s formatting. That keeps the transfer simple, but it also means the new sheet uses its own default formatting. If formatting is important, copy the range with Excel’s copy methods instead, then review the destination for unwanted number formats or styles.
Choose the right delimiter and text qualifier
The example sets comma as the delimiter. That matches common CSV files, but the file’s actual separator depends on how it was created and, sometimes, the computer’s regional settings. A file exported in a region that uses commas for decimal marks may use semicolons between fields. If the imported row appears in one column, check the file in a plain-text editor to see which character separates its values, then adjust the delimiter options in the macro.
CSV fields may be enclosed in double quotation marks. This allows a value to contain a delimiter without being split into extra columns. For example, a quoted address containing a comma should remain one field when the text qualifier is set to double quotes. Quoted values can also contain quotation marks represented by doubled quotes. Excel’s text import handles common quoted CSV structures, but unusual exports or malformed quotes can still produce unexpected results.
- Use the comma option for comma-separated files.
- Use the semicolon option instead when semicolons separate the fields.
- Set the tab option for tab-delimited text, even if the file has a .txt extension.
- Do not enable multiple separators simply to make a file split; that can break legitimate values containing those characters.
Handle UTF-8 and other encodings
The example specifies origin 65001, the code page commonly used for UTF-8. This is helpful when a CSV contains accented names, non-Latin characters, or symbols that otherwise appear as question marks or garbled text. The correct setting still depends on the file’s actual encoding. A file exported in a different legacy encoding may need a different origin value; changing the setting blindly can make characters less readable rather than fixing them.
The Local option also affects how Excel interprets text according to regional settings. In particular, dates and decimal separators may be interpreted differently on computers with different locales. If the file will be imported by people in multiple regions, decide whether to preserve sensitive fields as text or standardize them after import. Test with representative files before relying on a macro for recurring reports.
Protect IDs, dates, and other sensitive columns
Excel tries to infer data types when it opens text. That can be convenient for ordinary numbers, but risky for account codes, postal codes, product IDs, and other values where every character matters. A value such as 00127 may become 127 if Excel treats it as a number. Long digit strings can also lose precision when converted to Excel numbers, and date-like text can be reinterpreted according to local date conventions.
For columns that must remain exactly as written, configure the import’s field information so those columns are treated as text. OpenText supports a FieldInfo argument for assigning column formats. Column numbers are counted from the first imported field. This is especially useful when a file has a known, stable layout; if columns can move or be added, validate the header row before applying fixed column numbers.
Another option is to import everything as text, then convert only the columns that should be numbers or dates using explicit rules. That is more work, but it avoids relying on Excel’s guesses. Remember that changing a cell’s number format to Text after Excel has already removed leading zeros does not restore them. Preserve the value during import, or reconstruct it only when the correct fixed length is known.
Import into an existing sheet instead
The sample creates a new sheet so it does not overwrite a destination range. If the data belongs in an existing worksheet, set the destination worksheet explicitly and choose the starting cell instead of adding a sheet. Before writing, decide what should happen to old rows: append below existing data, replace a defined range, or clear and reload the entire import area. Each choice has different risks.
For a replacement import, clear only the intended data range, not the whole worksheet, because the sheet may contain formulas, notes, or formatting outside the imported table. For an append operation, find the next available row using a column that is reliably filled in every record. Checking a mostly blank column can put new records in the middle of the existing data. If imports may contain headers, avoid appending the CSV’s header row a second time.
When the file is large, transferring a range’s values in one operation, as the example does, is generally preferable to looping through every cell in VBA. Cell-by-cell loops make many individual calls to Excel and can be much slower. For very large or recurring files, consider whether a Power Query connection would be easier to refresh and maintain than a custom macro.
Common import problems and fixes
- Everything lands in one column: The selected delimiter does not match the file. Inspect the source text and change the delimiter option.
- Characters look corrupted: Check the file encoding and select the matching origin, such as UTF-8 where appropriate.
- Leading zeros disappear: Import that field as text rather than allowing Excel to infer a number.
- Dates or decimals change: Review the locale and type-detection behavior; retain the original text if it must not be reinterpreted.
- The macro is blocked: Confirm that the workbook is saved as .xlsm and that the file is from a source you trust. Follow your organization’s macro security policy rather than enabling all macros broadly.
- The wrong workbook receives the data: Check where the macro is stored.
ThisWorkbookmeans the workbook containing the VBA code, not necessarily the workbook currently selected on screen.
A CSV file cannot contain VBA macros, but its text may still include values that Excel interprets as formulas. Treat files from unknown sources cautiously, especially if they contain fields beginning with formula characters. Importing data is not the same as verifying that the data is safe or trustworthy.
When to use another import method
VBA is a good fit when a button or scheduled procedure needs to import files using repeatable rules, such as the same delimiter, encoding, and destination layout. For a one-time import, Excel’s Data tab offers a point-and-click route through Get Data and the text/CSV import experience. That approach makes it easier to preview columns and adjust data types before loading.
Power Query is often preferable when the same source needs repeatable cleanup, column selection, type changes, or periodic refreshes. It stores transformation steps that can be reviewed and refreshed without writing VBA. The choice is not simply about which method can read a CSV: consider who will maintain the process, whether the file layout changes, and how carefully data types must be controlled.
Frequently Asked Questions
How do I import a CSV into Excel using VBA?
Use Workbooks.OpenText to open the file with the correct delimiter and text qualifier. Then copy the parsed range to the desired worksheet. The sample above prompts for a file and imports its values to a new sheet.
Can VBA import a CSV without opening it on screen?
Excel still has to read and parse the file, but VBA can suppress screen updating while it works. The example does this during the import. Suppressing screen updates does not make the process invisible in every respect, and it should be restored after errors as the sample’s error handler attempts to do.
How can I keep leading zeros in a CSV import?
Tell Excel to treat the relevant field as text during import, using the field-format settings supported by OpenText. Do this before Excel converts the value to a number; applying a Text number format afterward cannot recover zeros that have already been dropped.
Why does my CSV open in one column?
The delimiter specified by the import does not match the file’s separator. Check whether the file uses commas, semicolons, or tabs, then enable the matching delimiter and disable the others. Also check that the file is genuinely delimited rather than fixed-width text.
Does this VBA work in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA. Use the desktop version of Excel to run the macro. The VBA editor and some keyboard shortcuts differ on Mac, but the general import approach is similar.
Related
- Conditional Formatting in Microsoft Project: What Works
- Data Validation and Verification in Excel Explained
- Power Query Replace Text: Steps, Options, and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Do Excel Macros Work in Google Sheets?No. Excel macros written in VBA do not run in Google Sheets, and converting an Excel workbook does…
- Excel UserForm Combo Box: Add, Populate, and Use ItAn Excel UserForm combo box is a VBA control that lets someone choose an item from a drop-down…
- Replace Excel VBA: Best Alternatives and When to SwitchYes, you can replace Excel VBA, but there is no single substitute that fits every macro. For repeatable…
- VBA Jump to Next Loop: How to Skip an IterationVBA has no built-in Continue For or Continue Do statement. To jump to the next loop iteration, put…
- Excel VBA Filter Range: AutoFilter Examples and FixesTo filter a range with Excel VBA, use the range’s AutoFilter method and specify the field number and…
- Best Macro Recorder for Gaming: Tools for Every SetupFor most players who already use compatible Logitech gear, Logitech G HUB is the best starting pick: it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.