How to Read External Files Using VBA in Word

You can automate Word to read data from external files such as text files, CSV files, or other Word documents without manually opening each one. This is useful when you need to import large amounts of data, generate reports, or process batch information. The VBA Open statement and the FileSystemObject provide two reliable methods for reading external content. This article explains both approaches and shows you how to write the VBA code step by step.

Key Takeaways: Reading External Files With VBA in Word

Open statement with Input mode: Read text or CSV files line by line using the Line Input # command inside a Do While loop.
FileSystemObject (FSO): Use CreateObject("Scripting.FileSystemObject") to open, read, and close files with more object-oriented control.
File path and error handling: Always provide the full file path and use On Error GoTo to catch missing files or permission issues.

Overview of Reading External Files With VBA

VBA in Word can read external files using two primary methods: the built-in Open statement and the FileSystemObject (FSO) from the Scripting Runtime library. The Open statement is native to VBA and does not require any additional references. It works well for text files, CSV files, and log files. The FileSystemObject offers more flexibility, such as checking if a file exists before reading, and it supports reading entire files at once or line by line.

Before writing the code, you must know the full path to the external file. For example, C:\Data\Report.txt or \\Server\Share\Data.csv. You also need to decide whether to read the file line by line or as a single string. Line-by-line reading is useful for structured data like CSV files where each line represents a record. Reading the entire file at once is faster for smaller files that you want to insert directly into a Word document.

Both methods require you to close the file after reading to free system resources. Failing to close a file can cause memory leaks or file-locking issues. The examples in this article include proper file closing and basic error handling.

Steps to Read a Text File Using the Open Statement

The Open statement is the simplest way to read a text file in VBA. You specify the file path, the mode (Input, Output, Append, or Binary), and a file number. Use the FreeFile function to get the next available file number automatically.

Declare variables and get a free file number
Open the VBA editor in Word by pressing Alt+F11. Insert a new module from Insert > Module. Write Dim fileNum As Integer: fileNum = FreeFile to reserve a file number.
Open the file in Input mode
Use Open "C:\Data\Report.txt" For Input As #fileNum. This opens the file for reading only. If the file does not exist, VBA raises a runtime error.
Read the file line by line
Use a Do While Not EOF(fileNum) loop. Inside the loop, use Line Input #fileNum, lineText to read one line into a string variable. Process each line as needed.
Close the file
After the loop ends, use Close #fileNum to release the file. Always close the file even if an error occurs by placing the close statement in the error handler.
Add error handling
Wrap the code in On Error GoTo ErrHandler. In the ErrHandler label, close the file if it is open and display a message.

Example code for the complete procedure:

Sub ReadTextFile()
    Dim fileNum As Integer
    Dim lineText As String
    Dim filePath As String
    filePath = "C:\Data\Report.txt"
    On Error GoTo ErrHandler
    fileNum = FreeFile
    Open filePath For Input As #fileNum
    Do While Not EOF(fileNum)
        Line Input #fileNum, lineText
        ' Process lineText here
        Debug.Print lineText
    Loop
    Close #fileNum
    Exit Sub
ErrHandler:
    If fileNum > 0 Then Close #fileNum
    MsgBox "Error reading file: " & Err.Description
End Sub

Steps to Read a File Using FileSystemObject

The FileSystemObject provides an object-oriented approach. You create an instance of the object, open a text stream, and read the content. This method allows you to check if the file exists before attempting to read it.

Create the FileSystemObject
Use Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject"). Do not add a reference to the Scripting Runtime library unless you prefer early binding.
Check if the file exists
Use If fso.FileExists(filePath) Then to avoid runtime errors. If the file does not exist, exit the sub or display a message.
Open the file as a text stream
Use Set ts = fso.OpenTextFile(filePath, ForReading). The ForReading constant has a value of 1. You can also specify the format: ForReading, False, TristateTrue for Unicode.
Read the content
Use ts.ReadAll to read the entire file into a string, or use ts.ReadLine inside a loop with Do While Not ts.AtEndOfStream for line-by-line reading.
Close the text stream and release the object
Use ts.Close and then Set ts = Nothing: Set fso = Nothing to free memory.

Example code for reading an entire file into a Word document:

Sub ReadFileWithFSO()
    Dim fso As Object
    Dim ts As Object
    Dim filePath As String
    Dim fileContent As String
    filePath = "C:\Data\Report.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FileExists(filePath) Then
        MsgBox "File not found: " & filePath
        Exit Sub
    End If
    Set ts = fso.OpenTextFile(filePath, 1, False, 0) ' 1 = ForReading, 0 = ASCII
    fileContent = ts.ReadAll
    ts.Close
    ' Insert content into the active document
    ActiveDocument.Content.InsertAfter fileContent
    Set ts = Nothing
    Set fso = Nothing
End Sub

Common Mistakes When Reading External Files

File not found or incorrect path

The most frequent error is a wrong file path. Always use the full absolute path. If the path contains spaces, enclose it in quotes inside the VBA string. Use Dir(filePath) to verify the file exists before opening it.

Forgetting to close the file

If you do not close a file opened with the Open statement, the file remains locked until you close Word. This can prevent other programs from accessing the file. Always close the file in the error handler as shown in the examples.

Reading a binary file as text

The methods described above work only for plain text files. If you try to read a binary file such as a .docx or .xlsx, the result will be garbled. For binary files, use the Binary mode with the Open statement and read byte arrays.

Permission denied errors

If the external file is open in another program or is marked as read-only on a network share, VBA may return a permission error. Check file permissions and ensure no other process has the file locked.

Open Statement vs FileSystemObject: Key Differences

Item	Open Statement	FileSystemObject
Requires reference	No	No (late binding) or Scripting Runtime (early binding)
File existence check	Not built-in; use `Dir` function	Built-in `FileExists` method
Read entire file at once	Possible with `Input` function but limited to 65535 characters	Yes, with `ReadAll`
Line-by-line reading	Yes, with `Line Input`	Yes, with `ReadLine`
Binary file support	Yes, using `Binary` mode	Not directly; requires additional methods
Performance on large files	Slower for line-by-line	Faster with `ReadAll` for files under 100 MB

You can now read external text files from Word using VBA with either the Open statement or the FileSystemObject. Choose the FileSystemObject when you need to check file existence or read the entire content quickly. Use the Open statement for simpler scripts or when working with binary files. As a next step, try processing CSV data by splitting each line with the Split function and inserting values into a Word table. An advanced tip: use GetOpenFilename to let the user select the file at runtime instead of hardcoding the path.

← Back to WiseChecker Home More in Word Solutions