How to read text from a non-delimited text file?

[Phil]

I would like to use a macro to read in text from a text file that is
not delimited and store the data in variables. For example, from line
5 of the text file I want to read from column 7 to 17 and store that
value in a variable. After reading in values from various places in
the file, I will do some calculations using these numbers, then create
a new Excel file and write the calculated numbers into the new file.

Is there a way to do this in VBA?

 

[JLatham]

That's what's known as fixed-length field file. Excel can actually import
them directly to a spreadsheet using the data import | Text type feature.

But you can work with them in VBA easily enough. I just open the text file
and start reading it line by line and parse each line as it's read in using
MID$() function.

Goes something like this (shows hard coded filename, but you can get it
other ways)
Dim textFile As String
Dim rawData As String
Dim filenum As Integer

textFile = "C:\My Documents\MyData\newData.txt"
filenum = Freefile()
Open textFile For Input As #filenum
Do While Not EOF(Filenum)
Line Input #filenum, rawData
myVar = Mid(rawData, 7,11)
....code continues
Loop
Close #filenum

The Mid function grabs characters starting at the first # position for the
second # number of characters, so start at 7th characters for 11 characters =
columns 7 through 17, inclusive.

 

[JLatham]

As for putting the information into a new workbook, that's easily done also.
Look for information in Help on
Workbooks and the .Add, .Save and .SaveAs methods. Look around for
information on selecting sheets and putting values into sheet cells here in
the forums - there are tons of discussions about it.

I'll give you a heads up, when you use the Workbooks.Add command, at that
point you are "in" the new workbook - it becomes the active one. You still
have reference to all the variables and objects in the original workbook, it
still exists, and is still open.

 

[Joel]

This is another example using VBA objects rather than file numbers. the
example below reads a text files and writes a txt file.


Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text1.txt"
WriteFileName = "text2.txt"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine
tswrite.WriteLine InputLine

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub

 

[Tom Ogilvy]

Joel,

the below code worked fine for me without using two instances of the
filescripting object from the scripting runtime library. Was there a
specific reason you used two instances? Just curious if you were avoiding
some known problem.

Sub Gettext()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\data\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fs = CreateObject("Scripting.FileSystemObject")

ReadFileName = "text1.txt"
WriteFileName = "text2.txt"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fs.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fs.CreateTextFile WritePathName
Set fwrite = fs.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


Do While tsread.atendofstream = False

InputLine = tsread.ReadLine
tswrite.WriteLine InputLine

Loop

tswrite.Close
tsread.Close

Exit Sub
End Sub

 

[Joel]

I didn't know filescripting objects supported multiple files being opened
simulataneously.

 

[JLatham]

Joel, Thanks for putting up the FSO example - I was hoping someone would.
Being the dinosaur that I am, I don't use it much and go with what I'm most
familiar with.

 

[NickHK]

Personally, I would stick with VBA's built-in methods, as the FSO would only
add an extra overhead and not improve performance.
Some systems it may be disabled also.
As I see it, its designed purpose is for scripting where there is no native
support for accessing the file system, something VBA does not suffer from.

NickHK

 

[JLatham]

Thanks for that info - sounds like good news for the dinosaurs