Excel users will often need to open a text file and convert the information into a spreadsheet format. For example data from another software application, such as a PDF text file might not be structured correctly to be read directly into Excel.
This article will show you how to write VBA code to open the text file and read the contents into a text string. If necessary you can then restructure the data so that it can be read in a spreadsheet format.
Using The VBA File System Object
Before accessing the text file you’ll need to set up a reference enabling access to the file system.You do this by clicking the tools tab in the VBA code window and selecting the Microsoft Scripting Runtime library.
Our code will open the file, and read the contents line by line into a text string. What you do with the file contents is up to you, and this article will just focus on opening and reading the file.
We’ll assume you have a file in a folder called „files“ which is a sub-folder beneath the directory holding your main Excel file.
First, we’ll set up variables to hold the file name, the output from the file and an object which will open the file for us.
Dim myFile, tmpText
Dim myFso As New FileSystemObject
Next, we define the file name and open it.
myFile = ActiveWorkbook.Path "filesopenFile.txt"
Set fso = myFso.openTextFile(myFile)
With the file open, we loop through it line by line, adding the contents to a text string before closing the file.
Do Until fso.AtEndOfStream
tmpText = tmpText & fso.ReadLine & "|"
We’ve added the pipe delimiter so that we can convert the text string into an array. You could just as easily write each line to an active sheet – it all depends on what you’re trying to do.
Now we can convert the text string to an array and write each item to an active sheet. The advantage in using an array is that you can work with the contents of each line before adding it to the sheet.
tmpText = Split(tmpText, "|")
For x = 0 To UBound(tmpText)
ActiveCell.Offset(x, 0).Value = tmpText(x)
The difficulty is sometimes working out how to manipulate the text into a usable Excel format, but at least now you’re able to access the data.
Opening and reading text files is a relatively straight-forward process but a good habit to get into is to build your own VBA code library so you don’t need to remember the code and can concentrate instead on solving your own particular business problem.