How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents of a text file into your database. This task may seem challenging at first because it involves an external file, but it doesn't have to be hard.

The OPEN Statement

There are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:

Open "C:\FolderName\FileName.txt" For Input As #1

The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:

Line Input #1, VariableName

After we are done reading its contents, we need to close the connection to the file with the following:

Close #1

Here's an example code snippet to read an entire text file into memory:

Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String

intFile = FreeFile()

strFile = "C:\FolderName\FileName.txt"

Open strFile For Input As #intFile

Do While Not EOF(#intFile)
     Line Input #intfile, strInput
     strText = strText & strInput
Loop

Close #intFile

Please notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.

The File System Object

My preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.


For instance, compare the following function, using FSO, to the one above, using the OPEN statement:

Public Function ReadTextFile(strFile As String) As String
'3/10/2016
'http://accessmvp.com/thedbguy

Dim fso As Object
Dim objFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then
     Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending
     ReadTextFile = objFile.ReadAll
End If

Set objFile = Nothing
Set fso = Nothing

End Function

The above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.


I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!