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!
