To load a CSV file into an Excel worksheet with VBA, use Workbooks.OpenText to parse the file, then copy its cells into a new sheet in the workbook. The macro below lets you choose a CSV, treats commas as delimiters, and imports UTF-8 text; review the notes on data types and save a backup before running it.
How the VBA import works
A CSV file is plain text: each line generally represents a row, and a delimiter separates fields. Excel must interpret that text before it can place the records into cells. Workbooks.OpenText opens and parses a delimited text file as a workbook. The macro then copies the parsed cells into a worksheet in the workbook containing the macro and closes the temporary CSV workbook without saving changes.
This approach is useful for a one-off import or a repeatable task that needs a small amount of automation. It also makes the main choices visible in the code: the file encoding, delimiter, and text qualifier. A comma is specified here, but a CSV exported from some regional settings may use a semicolon instead.
VBA macro to choose and import a CSV
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this code. On a Mac, use Option+F11 (on some keyboards, Fn+Option+F11); if that shortcut is unavailable, open the Visual Basic Editor from the Developer tab. The macro is intended for desktop Excel, not Excel for the web.
Option Explicit
Sub LoadCsvIntoNewWorksheet()
Dim csvPath As Variant
Dim targetBook As Workbook
Dim csvBook As Workbook
Dim sourceSheet As Worksheet
Dim destinationSheet As Worksheet
Dim sourceRange As Range
Dim errorMessage As String
' The macro is expected to be stored in the workbook receiving the data.
Set targetBook = ThisWorkbook
csvPath = Application.GetOpenFilename( _
"CSV files (*.csv),*.csv", , "Select a CSV file")
If VarType(csvPath) = vbBoolean Then Exit Sub
On Error GoTo ImportFailed
Application.ScreenUpdating = False
' Read UTF-8 text and split fields at commas; quotes enclose text fields.
Workbooks.OpenText Filename:=CStr(csvPath), Origin:=65001, _
DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
Comma:=True, Tab:=False, Semicolon:=False, Space:=False, _
Other:=False, Local:=True
Set csvBook = ActiveWorkbook
Set sourceSheet = csvBook.Worksheets(1)
Set sourceRange = sourceSheet.UsedRange
' Excel supplies a default sheet name, avoiding a naming conflict.
Set destinationSheet = targetBook.Worksheets.Add( _
After:=targetBook.Worksheets(targetBook.Worksheets.Count))
sourceRange.Copy Destination:=destinationSheet.Range("A1")
csvBook.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "CSV data was copied to " & destinationSheet.Name & ".", vbInformation
Exit Sub
ImportFailed:
errorMessage = Err.Description
On Error Resume Next
If Not csvBook Is Nothing Then csvBook.Close SaveChanges:=False
Application.ScreenUpdating = True
MsgBox "The CSV could not be imported: " & errorMessage, vbExclamation
End Sub
The code deliberately creates a new destination worksheet rather than clearing an existing one. Excel assigns that sheet a default name such as Sheet2; rename it after import if useful. The macro assumes it is stored in the workbook that should receive the data, because ThisWorkbook refers to the workbook containing the VBA code. If the code is in PERSONAL.XLSB or an add-in, ThisWorkbook is not the workbook you are viewing; change the target-book assignment to refer to the intended workbook.
Run the macro and preserve the workbook
Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z, and this macro adds a worksheet each time it runs. If the macro is stored in the workbook, save that workbook as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained. A normal .xlsx file cannot store VBA macros.
- In desktop Excel, enable the Developer tab if needed through File > Options > Customize Ribbon on Windows, then select Developer.
- Open the VBA editor, insert a standard module, and paste the code.
- Save the workbook as an
.xlsmfile, then return to Excel. - Choose Developer > Macros, select
LoadCsvIntoNewWorksheet, and choose Run. You can also run it from the Visual Basic Editor. - Select the CSV file in the file picker and inspect the resulting worksheet, especially dates, identifiers, and the first and last rows.
On a Mac, the ribbon and security prompts differ, but the macro runs in desktop Excel for Mac when macros are permitted. Excel for the web can open CSV and Excel files, but it does not run VBA macros. An organization may also restrict macros through its security settings; do not enable code from an untrusted workbook just to import a file.
Choose the delimiter and encoding carefully
The example explicitly selects comma separation. A comma-delimited file with a quoted field such as "Seattle, WA" should keep that comma inside one field because the macro sets the text qualifier to a double quote. If the source uses semicolons between fields, change Comma:=True to Comma:=False and Semicolon:=False to Semicolon:=True. For a tab-delimited text file, set Tab:=True and disable the other delimiters as appropriate.
The code uses Origin:=65001, the code page commonly used for UTF-8. That is helpful when the CSV includes characters such as accented letters, but the right encoding depends on how the file was saved. If characters appear garbled, check the source file’s encoding and use Excel’s text import options or another import method that lets you select the correct encoding. Do not assume that changing the delimiter will fix an encoding problem.
Local:=True asks Excel to use local settings when interpreting certain values. Regional settings can affect how numbers and dates are recognized. If a file uses a different convention—for example, day-month-year dates while Excel expects month-day-year—inspect the results and set data types deliberately instead of trusting automatic interpretation.
Prevent unwanted changes to identifiers and dates
CSV files do not store Excel cell types or formatting. A value such as 00127 is just text in the file, but Excel may recognize it as the number 127 and remove its leading zeros when opening the CSV. Similarly, a string that resembles a date may become a date value, and long digit sequences may be rounded if interpreted as numbers. Copying the parsed range into another sheet does not restore the original text once Excel has converted it.
For postal codes, product IDs, account numbers, and other identifiers, preserving the characters is often more important than doing arithmetic. Use Excel’s import interface to assign the relevant columns the Text type before loading, or use Power Query and set those columns to Text. In VBA, a more controlled import can define column data types before loading, but the simple OpenText example above does not do that. Test with a small representative file when values must remain exact, especially when it contains leading zeros or long numeric-looking fields.
Also check whether the source contains a header row. The macro copies all parsed rows unchanged; it does not create headers, remove duplicates, or validate column names. If the file has no header, the first record remains in row 1. If downstream formulas or reports expect headers, add a separate, explicit step rather than assuming Excel will infer them.
Common problems and practical checks
- Everything appears in one column: The delimiter setting may not match the file. Check whether the source uses commas, semicolons, tabs, or another separator.
- Fields are shifted or split unexpectedly: Inspect the CSV’s quoting. Delimiters inside properly quoted fields should not separate columns, but malformed quotes can produce confusing results.
- Accented characters look wrong: Confirm the file’s encoding. The sample specifies UTF-8; older or differently encoded exports may need a different import setting.
- Leading zeros disappeared or dates changed: Excel inferred a type during opening. Import sensitive columns as text before Excel converts them.
- The macro reports an error or does not run: Confirm that macros are allowed, the file is a desktop workbook, and the workbook is writable. A protected or shared environment may impose additional restrictions.
- The output is incomplete: An Excel worksheet supports at most 1,048,576 rows and 16,384 columns. A source exceeding those worksheet limits cannot fit in one sheet.
After import, compare the worksheet’s row and column counts with the source, check several records near the top and bottom, and verify any fields that Excel might interpret automatically. For important operational data, keep the original CSV unchanged so you can re-import it if parsing choices need adjustment.
When Power Query or another method is a better fit
For a recurring import that needs refreshes, transformations, or type control, Power Query is often easier to maintain than a short VBA macro. In Excel for Microsoft 365 on Windows, use Data > Get Data > From File > From Text/CSV, select the file, review the preview and delimiter, then choose Load or Transform Data. In the Power Query editor, you can change column types and other steps before loading the result to a worksheet. The exact available options can vary by Excel platform and build.
For a quick manual import, opening the CSV directly in Excel may be enough, but automatic type detection still matters. The VBA method is useful when you specifically need a button or repeatable procedure inside a desktop workbook. Power Query is usually a better choice when you want a saved sequence of import and cleanup steps that can be refreshed. Excel for the web does not execute the VBA macro, so use a supported web workflow or desktop Excel when automation is required.
Frequently Asked Questions
Can VBA import a CSV into the active sheet instead of a new sheet?
Yes. Change the destination range to a specific worksheet and cell, such as a named worksheet’s Range("A1"). Decide first whether existing contents should be replaced, appended, or left intact; the example uses a new sheet to avoid silently overwriting data.
Why does Excel remove leading zeros from a CSV?
CSV stores text values without Excel formatting or column types. When Excel opens a file, it may interpret digit-only fields as numbers, which removes leading zeros. Import that column as Text before loading if the zeros are significant.
Can the macro import a semicolon-separated CSV?
Yes. In the OpenText call, turn off the comma delimiter and turn on the semicolon delimiter. Check the file itself rather than relying on its extension or the regional settings of the computer that created it.
Can I run this VBA macro in Excel for the web?
No. Excel for the web can display and edit workbook data but does not run VBA. Use desktop Excel for the macro, or consider Power Query where the required import features are available in your Excel version.
Does the import macro overwrite existing data?
The example adds a new worksheet and copies the CSV there, so it does not intentionally clear an existing sheet. It adds another sheet on every run, however. Review the workbook before running it again, and save a backup because macro actions cannot be reversed with Ctrl+Z.
Related
- Excel Split Names in One Cell Into Two Cells
- Excel Split One Cell Into Multiple Rows: How It Works
- Excel Split Print Area Into Two Pages: Page Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Array Trim: Remove Blank ItemsIf you mean trimming an array in Excel VBA, VBA has no built-in function that removes blank elements…
- How to Activate a SteelSeries MacroTo activate a SteelSeries macro, open SteelSeries GG, select your supported device under Engine, assign a saved macro…
- VBA: How to Find an Accredited VA PractitionerIf by VBA you mean the U.S. Department of Veterans Affairs’ Veterans Benefits Administration, use VA’s official accreditation…
- VBA InputBox vs MsgBox: OK or Cancel ExplainedYes: VBA’s standard InputBox already displays OK and Cancel buttons, while MsgBox can display them when you specify…
- Excel VBA Find Exact Match: Find Whole-Cell ValuesIn Excel VBA, set the LookAt argument of Range.Find to xlWhole to match an entire cell rather than…
- Excel VBA Error 75: Fix Path/File AccessExcel VBA run-time error 75, “Path/File access error,” means a file operation could not access the path or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.