To import a CSV file with Excel VBA, use Workbooks.OpenText to parse it, then copy its values into a worksheet in your workbook. The example below opens a file picker, reads a UTF-8 comma-delimited file, and replaces the contents of a worksheet named Data.
What a VBA CSV import does
A CSV, or comma-separated values file, is plain text arranged in rows and columns. It does not retain Excel features such as formulas, cell formatting, charts, or multiple worksheets. Importing one into Excel means parsing the text into fields and placing those fields in worksheet cells.
That parsing step matters. A CSV can contain commas inside quoted fields, such as "Smith, Jordan", and quotation marks can be escaped by doubling them. A suitable text-import method recognizes those conventions; splitting each line at every comma with a basic VBA string operation does not. That shortcut can break valid records and should be avoided unless the file format is exceptionally simple and controlled.
The macro below uses Excel’s text-file importer, with comma as the delimiter and double quotes as the text qualifier. It opens the CSV as a separate workbook, transfers the imported values to the destination sheet, and closes the temporary workbook. It is intended for Excel for Microsoft 365 on Windows.
VBA macro to import a CSV file
Before running any macro that clears or replaces worksheet data, save a copy of your workbook. Macro actions generally cannot be reversed with Ctrl+Z. Create a worksheet named Data in the workbook containing this macro, then paste the code into a standard VBA module.
Sub ImportCsvToDataSheet()
Dim csvPath As Variant
Dim targetWb As Workbook
Dim sourceWb As Workbook
Dim targetWs As Worksheet
Dim sourceWs As Worksheet
Dim sourceRange As Range
Dim errorText As String
On Error GoTo ImportError
' Choose the CSV file; cancel exits without changing the sheet.
csvPath = Application.GetOpenFilename( _
"CSV Files (*.csv), *.csv", , "Select a CSV file")
If VarType(csvPath) = vbBoolean Then Exit Sub
Set targetWb = ThisWorkbook
Set targetWs = targetWb.Worksheets("Data")
Application.ScreenUpdating = False
' Open as UTF-8 text, using commas and double quotes for parsing.
Workbooks.OpenText Filename:=CStr(csvPath), Origin:=65001, _
DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
Comma:=True, Local:=True
Set sourceWb = ActiveWorkbook
Set sourceWs = sourceWb.Worksheets(1)
Set sourceRange = sourceWs.UsedRange
' Replace existing cell values while keeping the sheet's formatting.
targetWs.Cells.ClearContents
targetWs.Range("A1").Resize(sourceRange.Rows.Count, _
sourceRange.Columns.Count).Value = sourceRange.Value
sourceWb.Close SaveChanges:=False
Set sourceWb = Nothing
Application.ScreenUpdating = True
MsgBox "CSV import complete.", vbInformation
Exit Sub
ImportError:
errorText = Err.Description
If Not sourceWb Is Nothing Then sourceWb.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "Import failed: " & errorText, vbExclamation
End Sub
The macro assumes the code is stored in the workbook that should receive the data: ThisWorkbook means the workbook containing the code, not necessarily whichever workbook is currently selected. The destination worksheet must be named exactly Data. The import starts at A1 and replaces existing cell values on that sheet, but leaves its formatting in place. If your sheet has a different name, change the worksheet name in the code.
How to add and run the macro
- Save the receiving workbook as an Excel Macro-Enabled Workbook (
.xlsm) so it can store VBA code. - On Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module and paste the macro into the module window.
- Return to Excel, confirm that the workbook contains a sheet named Data, and save the workbook.
- Run the macro from the Visual Basic Editor with the cursor inside the procedure and press F5, or return to Excel and use Developer > Macros to select and run
ImportCsvToDataSheet. - Choose a CSV file in the file picker. If you cancel, the macro exits without clearing the destination sheet.
If the Developer tab is hidden, enable it through File > Options > Customize Ribbon on Windows. On Excel for Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; keyboard shortcuts can depend on macOS and keyboard settings. Excel for the web does not run VBA macros, so open the workbook in the desktop application.
Delimiters, quotes, and character encoding
The sample sets Comma:=True and uses a double-quote text qualifier. This fits a conventional comma-delimited CSV, including fields such as "Seattle, WA". If the file is separated by semicolons or tabs instead, select the matching delimiter in the import settings or adapt the OpenText arguments. Regional settings and the program that created the file can affect which delimiter it uses, so check the source rather than assuming every file ending in .csv has the same layout.
Origin:=65001 requests UTF-8 decoding, useful for names and symbols beyond basic English characters. If the file uses another encoding, the correct origin may differ; a wrong choice can produce garbled characters even when the columns are parsed properly. Local:=True tells Excel to use local settings when interpreting text as numbers and dates. That can help with files created for the same regional conventions, but it can also make date interpretation dependent on the computer’s locale.
For an unusual file, use Excel’s import interface first to inspect its delimiter, encoding, and preview. On Windows in Microsoft 365, the usual route is Data > Get Data > From File > From Text/CSV. The preview makes it easier to spot a delimiter or encoding mismatch before any data is loaded.
Data types: dates, numbers, and leading zeros
CSV stores text, not Excel’s underlying cell types. During import, Excel may interpret text that looks like a date or number and convert it. A value such as 03/04/2025 is ambiguous: depending on regional settings, it may mean March 4 or April 3. A product code such as 00127 may become the number 127, losing its leading zeros.
The example uses Excel’s normal text-import interpretation and then copies the resulting cell values. It does not force every column to text or assign special types to selected columns. If exact preservation matters, define the intended types before importing. In the Text/CSV preview, review the detected data types and set identifier columns to text where appropriate. For repeatable VBA imports with strict column rules, supply field information through the text-import method or import the data as text and convert only well-understood columns afterward.
Keeping an identifier as text is not merely cosmetic: Excel may discard leading zeros or change long digit strings into scientific notation when it treats them as numbers. Conversely, leaving every field as text can make dates and quantities inconvenient for calculations. Decide column by column based on how the data will be used.
Replacing data versus appending rows
The sample is a replacement import: Cells.ClearContents removes values and formulas from the destination sheet before the newly imported values are written at A1. It preserves formatting, but it also clears any unrelated notes, formulas, or information on that sheet. Use a dedicated import worksheet, or change the clearing step if the sheet contains other content. Save a copy before running the macro, especially when the current data is important.
Appending means placing each new CSV beneath existing records instead of replacing them. That requires finding the next available row in the destination and writing the imported range there; it also calls for a policy on headers. For example, if every CSV includes a header row, skip that row when appending after the first import or the destination will accumulate duplicate headers. Consider checking column names and order before combining files, since data in mismatched columns can look plausible while being wrong.
For repeated imports, add validation before writing: confirm that the expected headers exist, the source has records, and the selected file is the correct one. The example is deliberately a basic single-file replacement, not a complete data pipeline.
VBA compared with Power Query
VBA is useful when a workbook needs a button-driven process, custom checks, or integration with other macro steps. The file picker makes the example usable with different CSV paths without editing a hard-coded filename. VBA also gives you control over what happens before and after the import, but it requires macro-enabled files and users must be permitted to run macros.
Power Query is often a better fit for repeatable data cleaning and refreshes without writing VBA. In Windows Excel for Microsoft 365, use Data > Get Data > From File > From Text/CSV, review the preview, and choose Load or Transform Data. Transform Data opens the Power Query Editor, where you can set column types and apply repeatable cleanup steps; a later refresh can reload the source. Mac Power Query availability and features vary by Excel version, so check the commands offered in that installation.
For a one-time import, Excel’s text import interface may be simplest. For a recurring, transparent cleanup process, Power Query is often easier to maintain. Choose VBA when you need automation beyond loading and shaping data, or when a macro is already part of a controlled workbook workflow.
Common import problems and safe handling
- The macro says Data was not found: Add a worksheet named Data or update the name in
Worksheets("Data"). Worksheet names must match, including spaces. - Text appears in one column: Check the actual delimiter and the import preview. The source may use semicolons or tabs rather than commas.
- Characters look incorrect: Check the file’s encoding. The example requests UTF-8; a file saved in another encoding may require a different origin.
- Dates or codes change: Excel may infer types during import. Inspect the type settings and keep identifiers as text when their exact characters matter.
- The macro is blocked: Use only macros from sources you trust. On Windows, macro security settings are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Do not enable all macros globally to make one file run.
The macro uses error handling to restore screen updating and close the temporary CSV workbook if a later step fails. If an error occurs after the destination’s contents have been cleared, however, the previous values may already be gone. That is why a backup is important; error handling is not a substitute for one.
Frequently Asked Questions
Can Excel VBA import a CSV without opening it on screen?
Yes. VBA can use methods such as Workbooks.OpenText to parse a CSV in a workbook without requiring you to open and format it manually. The example opens a temporary workbook in Excel, transfers the values, and closes it.
How do I import a CSV into a specific worksheet?
Set a worksheet variable to the intended destination, as the example does with Worksheets("Data"), then write the imported range starting at the desired cell. The worksheet must exist in the receiving workbook.
Why does Excel remove zeros at the start of a CSV value?
Excel may interpret a value such as 00127 as a number, which has no leading zeros. Import that column as text when the zeros are part of an ID, account code, or other exact identifier.
Can this macro import a semicolon-delimited file?
Yes, but change the delimiter settings to match the file. The sample explicitly enables commas; importing with the wrong delimiter can place an entire record in one column or split it incorrectly.
Will the VBA macro 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. Run the macro in desktop Excel for Windows or Mac, taking account of the version’s available features.
Related
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Create a Pivot Table Using VBA in Excel 365To create a PivotTable with VBA in Excel for Microsoft 365 on Windows, put your source data on…
- VBA Left Function: How to Return Text from the StartIn VBA, use Left(string, length) to return a specified number of characters from the beginning of a string.…
- VBA For Next Loop: Syntax, Examples, and Common ErrorsA VBA For...Next loop repeats a block of code for a counter that moves through a specified range.…
- VBA Function vs Sub: Differences and When to Use EachA VBA Function calculates and returns a value; a Sub carries out actions and does not return a…
- Add Controls to a VBA UserForm: Steps and CodeTo add controls to a VBA UserForm, open the form in the Visual Basic Editor and drag a…
- VBA Range Sum: Methods, Examples, and Common ErrorsTo sum a range in Excel VBA, use Application.WorksheetFunction.Sum and assign its result to a variable or cell.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.