Parsing into a text file

[Steve]

Can any one help me with the VB or VBA commands to go into
a text file and pull out or check for specific text?
Say I want to go into a predefined line of text and look
for certain charcters or string of characters or count so
X number of characters into a line. . . that kind of
stuff. If you have a reference for such commands, that
would do as well.

 

[Dick Kusleika]

Steve

Here's an example that uses some of the keywords that you'll need. Check
them out in help and look at the See Also parts too.

Sub FindStrings()

Dim FName As String
Dim Fnum As Long
Dim sLine As String
Dim i As Long

FName = "C:\Dick\Test.txt"
Fnum = FreeFile

Open FName For Input As Fnum
i = 1

Do While Not EOF(Fnum)
Line Input #Fnum, sLine
If InStr(1, sLine, "text", vbTextCompare) > 0 Then
Cells(i, 1).Value = sLine
i = i + 1
End If
Loop

Close Fnum

End Sub

 

[Steve]

Dick,
First, thanks a lot for the reply to my post. . .
I just have a question about where you said to look for
stuff, you wrote. . .

Here's an example that uses some of the keywords that
you'll need. Check
them out in help and look at the See Also parts too.


You wrote to check in 'help' and 'See Also'. Is this in
Excel or someplace else and are the statements used in
your script explained there? I would really like to know
what statements like. . .

#Fnum, sLine
If InStr(1, sLine, "text", vbTextCompare) > 0 Then
Cells(i, 1).Value = sLine
i = i + 1
End If

actually do and why you use them. A command reference for
these as well as other statments would be a great help. If
you could point me to a place where I could get a
explaination of what these statements do and what their
associated parameters and switches are that would be what
I am looking for. I just want to be able to know what
statements to use to accomplish what I want to do.

Thanks again for your help with the above script.

 

[Dick Kusleika]

Steve

I was referring to VBA help. There are a few commands that you can use for
dealing with text files. Some that I use are

Open
Close
Input
Print
Write
Line Input

From Excel, open the VBE (alt-F11), then open VBA help (F1). Go to the
index tab and look for Line Input. On the help page, near the top, there
will be a See Also link which shows related commands. For example, under
Line Input #, there is the Input #. Under Input #'s See Also are commands
like Open and Close.

I could explain these commands to you, but I'd just have to look in help
(like I do every time I use them because I can't remember the syntax) so I
figured you could go straight to the source.

Here's some comments on the code example I provided to help you get started.

Here I'm putting the file name in a variable

FreeFile is a function that gets the next available file number. The file
number is just a way for VBA to keep track of open text files. You could
also write this as

Fnum = 1

If you know there are no other files open and 1 won't conflict with
anything. I always use FreeFile, but not everyone does.

This is the Open statement. I'm opening the file I specified above for
Input and I'm telling VBA to use Fnum as the identifier. I can then refer
to Fnum later and it will know what I'm talking about. You can open text
files for Input, Output, Append and a few other things. I chose Input,
because I knew that I would only be reading in from the file, not writing to
the file.

This is a counter that I use to identify how many lines of the text file I
found that matched.

The EOF function returns true when the pointer is at the end of the text
file. I want to run everything between Do and Loop until I get to the end.

Line Input inputs the current line of the text file to a variable. I set up
a variable called sLine to hold the text that it reads in. Input # would
read the next field, not the next whole line (although in some cases they
are the same). If you had a comma-separated text file, Input # would read
to the next comma. That can be useful depending on what you're trying to
do. In either case, the pointer gets moved to the end of whats read. So
after it reads the first line, the pointer is at the start of the second
line. After it reads the last line, EOF becomes true and the loop stops.

I have the current line in a variable calles sLine. Here I use the Instr
function to determine if the word "text" exists in that line.

If the word "text" exists in that line, I write it to a cell
then increment the counter so if it finds another line with "text" it will
right to the next cell below - and not overwrite the first one.

This closes the text file. You always want a close statement whenever you
open a file. Otherwise the file will remain open.

Hopefully that gives you a good start. There's not a ton of commands
available for working with text files, so it shouldn't take you long to
learn them. However, they can be tricky (like the difference between Write
and Print) so it takes some experimentation to see what works for your
particular situation.

Good luck, and be sure to post back if you need further clarification.

 

[Steve]

Dick,

You Rock!!! Thanks for spending the time to update this
post and for the info on looking up the commands. That is
exactly what I was hoping for.

Thanks much

Steve

 

[Tom Ogilvy]

You might find this useful as well:

http://support.microsoft.com/support/excel/content/fileio/fileio.asp
File Access with Visual Basic® for Applications