To open a text file with VBA in Excel for Microsoft 365 on Windows, use Workbooks.OpenText and specify how the file is separated into columns. For a comma-delimited UTF-8 file, for example, set DataType:=xlDelimited, Comma:=True, and an appropriate text encoding; the macro below adds a file picker and handles cancellation.
What VBA does when it opens a text file
A text file usually contains values separated by commas, tabs, semicolons, or another delimiter, with each line representing a row. Unlike an Excel workbook, it does not store worksheet formatting, formulas, or multiple sheets. When Excel opens one, it has to interpret the characters and decide where columns begin and what type of value each field represents.
VBA can ask Excel to perform that import with Workbooks.OpenText. The method opens the file as a new workbook, using options you supply for separators, encoding, quoted fields, and column formats. The result is an Excel workbook in memory; the source text file is not converted or overwritten just by importing it. Save the imported workbook separately as an Excel file if you need to retain the data in workbook format.
The main benefit of specifying the import settings in code is consistency. A manually opened file may be interpreted differently depending on Excel’s import choices, locale, or the file’s contents. Explicit settings make a repeatable import more predictable, especially for recurring reports.
Choose the right way to open the file
Use Workbooks.OpenText when you want to control how a plain-text file is split into columns and how its values are interpreted. Use Workbooks.Open for an existing Excel workbook such as XLSX or XLSM. For a simple CSV, Workbooks.Open can also open the file, but it provides less explicit control over parsing than an OpenText call configured for the file.
Before writing the macro, check what separates the fields and whether the first line contains headings. A file named TXT is not necessarily tab-delimited, and a CSV is not always comma-separated in every regional setup. Inspect a few lines in a plain-text editor or spreadsheet application. For example, these rows are tab-delimited:
Item Code Quantity
Notebook 00127 12
By contrast, this is comma-delimited:
Item,Code,Quantity
Notebook,00127,12
Quoted values can contain delimiters. In a CSV row such as Notebook,"A, blue",12, the comma inside the quoted description should not start another column. Setting the text qualifier to a double quote tells Excel how to handle that case.
Open a selected CSV with a VBA macro
The following macro prompts for a TXT or CSV file, imports it as comma-delimited UTF-8 text, and reports whether the import succeeded. Change Comma:=True to another delimiter setting if the selected file uses tabs or semicolons. The Origin:=65001 value specifies UTF-8; if the source uses a different encoding, choose the matching code page or Excel may display some characters incorrectly.
Save a copy of the workbook before running any macro. VBA changes cannot be undone with Ctrl+Z. In the VBA editor, insert a standard module with Insert > Module, paste the code, and run the macro with the cursor inside it by pressing F5. On Mac, the VBA editor and available shortcuts differ; Excel for the web does not run VBA macros.
Sub ImportCsvTextFile()
Dim filePath As Variant
Dim importedWorkbook As Workbook
' Ask the user to choose a text or CSV file.
filePath = Application.GetOpenFilename( _
FileFilter:="Text and CSV Files (*.txt;*.csv),*.txt;*.csv", _
Title:="Choose a text file")
' GetOpenFilename returns False when the user cancels.
If VarType(filePath) = vbBoolean Then
If filePath = False Then Exit Sub
End If
On Error GoTo ImportError
' Import UTF-8 data, splitting fields at commas.
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 importedWorkbook = ActiveWorkbook
MsgBox "Imported into " & importedWorkbook.Name, vbInformation
Exit Sub
ImportError:
MsgBox "Excel could not import the file: " & Err.Description, vbExclamation
End Sub
After Workbooks.OpenText runs, Excel activates the newly opened workbook, so ActiveWorkbook refers to the import rather than necessarily to the workbook containing the macro. Keep that distinction in mind if you add steps that copy the imported data into another workbook.
Set delimiters for TXT and CSV files
In the example, only commas are enabled. For a tab-separated file, set Tab:=True and Comma:=False. For semicolon-separated data, set Semicolon:=True and turn off the other delimiters. Enable more than one delimiter only when the file actually uses more than one separator; otherwise Excel may split a field unexpectedly. ConsecutiveDelimiter:=False treats adjacent delimiters as separate empty fields, which is usually important when empty columns carry meaning.
For a tab-delimited inventory file whose first column contains identifiers that must remain text, this shorter example shows the relevant options. Replace the sample path with the real file path. It uses a Windows-style path; select a suitable path for the computer running the macro.
Save a copy of the workbook before running this macro as well, because its actions cannot be reversed with Ctrl+Z.
Sub ImportTabFile()
Dim filePath As String
' Replace this sample path with the source file location.
filePath = "C:\Data\inventory.txt"
' Split on tabs and keep the first imported column as text.
Workbooks.OpenText Filename:=filePath, _
Origin:=65001, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlTextQualifierDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, xlTextFormat)), _
Local:=False
End Sub
The FieldInfo setting prevents Excel from converting the first column to a number. That matters for values such as postal codes or item codes: a value like 00127 can otherwise become 127. If you need particular formats for several columns, provide a corresponding entry for each column that requires a defined import type. Check the imported result, since an incorrect column number or type can change how data is displayed.
Control encoding, dates, and numbers
Encoding determines how bytes in a file map to characters. UTF-8 is common, but files may use another encoding, especially if they come from older software. If accented letters or symbols appear as strange characters, check the source encoding instead of trying to fix the text after import. A UTF-8 byte-order mark can help some applications identify UTF-8, but explicitly setting the origin makes the intended encoding clearer in a macro.
Dates and numeric values also depend on interpretation. A date written as 03/04/2026 can mean March 4 or April 3 depending on regional conventions. The Local option affects whether Excel uses local settings for number and date parsing. If the text file has ambiguous dates or codes that must be preserved exactly, import those columns as text and convert them only after confirming the intended meaning. Do not assume that Excel’s automatic conversion is correct just because the values look plausible.
For repeatable imports, specify column formats with FieldInfo where needed and test representative rows that include leading zeros, negative values, blank fields, and non-English characters. A successful macro run only means Excel opened the file; it does not guarantee every field was interpreted as intended.
Common problems and practical fixes
- Everything appears in one column: The delimiter setting does not match the file. Inspect the source and switch the appropriate option, such as
Tab:=Truefor tab-separated data. - Values split into too many columns: A delimiter may have been enabled even though it appears inside unquoted text. Turn off unused separators and confirm that quoted fields use the expected text qualifier.
- Leading zeros disappear: Excel treated an identifier as a number. Set that imported column to text using
FieldInfo, or otherwise preserve it as text during import. - Characters look corrupted: Confirm the file’s encoding and adjust
Origin. UTF-8 is represented by code page 65001, but not every text file is UTF-8. - The wrong workbook receives later commands:
OpenTextactivates the newly imported workbook. Store a reference to the intended workbook before importing, or explicitly qualify later worksheet references. - The macro reports a path or access error: Check that the file exists, is not blocked by permissions, and is not being moved or deleted while the macro runs. The file picker example reduces typing errors but cannot grant access to a restricted location.
When diagnosing an import, first test with a small copy of the source file and compare several rows against the original text. This separates parsing problems from later worksheet processing, and helps avoid silently accepting shifted columns or altered identifiers.
When to use Power Query or manual import
VBA is useful when an import is one step in a larger automated process, such as opening a daily report and then copying selected columns into a template. If the main goal is to clean, combine, or refresh external text data without writing code, Power Query is often a better fit in Microsoft 365 for Windows. The ribbon path is Data > Get Data > From File > From Text/CSV. Review the preview and its detected delimiter and data types before loading. Mac feature availability and menu details can vary by Excel version; Excel for the web does not run VBA.
For a one-time import, use Excel’s built-in open or text import process rather than creating a macro you will not reuse. In Excel for Windows, Ctrl+O opens the file picker, and you can browse to a text file. On Mac, the equivalent is Command+O. The available import prompts and parsing controls can differ by file type and Excel version, so inspect the preview or resulting columns rather than assuming the defaults match your data.
One useful distinction: importing a text file does not mean Excel has created a live connection to it. The OpenText examples create a workbook from the file contents. If you need a refreshable query, choose a suitable data connection or Power Query workflow instead.
Macro safety and saving the imported data
Only run macros from sources you trust. Excel may block macros from files downloaded from the internet or display a security warning; do not enable content simply to get an unfamiliar file to open. A macro-enabled workbook uses the XLSM format if you need to retain its VBA code. The text file imported by the macro remains a separate source file unless your code explicitly changes or saves it.
After checking the imported workbook, use File > Save As to save it in an appropriate format, such as XLSX for a workbook without macros or XLSM if it must contain VBA. Saving the imported data as a workbook preserves worksheet structure and Excel-supported formatting, unlike a plain text file, which stores text values separated by delimiters.
Frequently Asked Questions
How do I open a TXT file in Excel using VBA?
Use Workbooks.OpenText with the file name and the correct delimiter options. Set DataType:=xlDelimited, then enable the separator the file actually uses, such as Tab:=True for tab-delimited text.
How do I open a CSV file in Excel with VBA?
Call Workbooks.OpenText with DataType:=xlDelimited and Comma:=True for a comma-separated file. Set a suitable encoding with Origin and use a text qualifier when quoted fields may contain commas.
Can VBA open a text file without splitting it into columns?
Yes, but OpenText is intended to parse delimited text into worksheet columns. If you need to read the file as raw lines for custom processing, VBA file-reading statements such as Open, Line Input, and Close are a different approach; they read text into VBA rather than importing it directly as a worksheet.
Why does Excel remove zeros at the start of a field?
Excel may infer that a field contains a number and convert it, dropping leading zeros. Import the relevant column as text with FieldInfo, then verify that the displayed values match the source.
Can I run an Excel VBA text-import macro in Excel for the web?
No. Excel for the web can open and edit many workbooks, but it cannot run VBA macros. Use the desktop Excel application for VBA; Excel for Mac supports VBA, although menus, file paths, and some platform-specific behavior can differ from Windows.
Related
- Excel Insert Picture Shortcut: Windows, Mac, and Web
- SharePoint Excel: How to Group Dates in a PivotTable
- Searchable Data Validation List in Excel: How to Set Up
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Pivot Charts: How to Create and Refresh ThemYes—VBA can refresh, filter, and manage Excel PivotCharts, and you can create one by selecting a PivotTable and…
- 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 Int Function: What It Does and How to Use ItIn Excel VBA, Int(number) returns the greatest whole-number value less than or equal to the number: for example,…
- VBA Functions: What They Are and How to Use ThemA function in VBA is a named procedure that performs a task and returns a value. You can…
- VBA Activate Workbook: Methods, Examples, and FixesTo activate an open workbook in Excel VBA, retrieve it from the Workbooks collection by its exact name…
- VBA Range Object: How to Work with CellsThe Excel VBA Range object represents one or more cells on a worksheet, letting code read or change…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.