Help with VBA (2002/SP3)

Topic

New Reply

I have a list of items in a file (data.txt) with titles as shown here in this example:

BASE
1. Cost
2. Weight
3. etc…

SLAB
1. Volume
2. Mass
3. etc…

MT_FRAME
1. Cost
2. etc…

I have another file (specifics.txt) that contains the titles of the only items that I’m interested in, for example:

SLAB
BASE
MT_FRAME

The titles in “specifics.txt” are not arranged in any particular order. I want to get the information from “data.txt” based on the order that is listed in “specifics.txt”. I know how to do this using VBA. The problem is that I don’t want to do multiple sweeps through “data.txt” for each title printed in “specifics.txt”. Rather, I’m looking for a solution using a single sweep through the file “data.txt”. The main reason for this is because “data.txt” can be thousands of records long, ie, with hundred of titles, and I will be looking for probably 10 specific titles.

Right now I have the following code that does multiple sweeps searching for the title “SLAB”:

R = 1
C = 1
f = FreeFile
Do While Not EOF(f)
Line Input #f, strLine
If blnFoundName Then
‘ Get category value
intPos2 = InStr(strLine, “1.”)
If intPos2 > 0 Then
ActiveCell.Cells(R, C + 1) = strLine
C = C + 1
blnFoundName = False
End If
Else
‘ Check for the occurance of the title “SLAB”
intPos1 = InStr(strLine, “SLAB”)
If intPos1 > 0 Then
blnFoundName = True
R = R + 1
End If
End If
Loop

Reply | Quote

Replies

I would assume that the Titles in data.txt always appear in the same field of each record. If this is the case, I would be inclined to bring the entire text file into a new worksheet and filter on the Title of Interest or use the Match function to locate it.

Reply | Quote

I thought about bringing the entire text file (data.txt) into Excel, but the text file contains close to million lines so that would be an over-kill if I’d import the file directly into Excel.

Reply | Quote

You may want to import into an access file which can handle filesizes this large and then search for the various items.

If you must use the 2 text files, I think you will have to read once through the category list, but will have to go from the top until the end of each item from the category list and hope they all occur near the top so that you won’t have to read too much.

I would also suggest not looping just until the EOF, but also stop after you find it the first time (unless you expect multiple occurances of an item)

Steve

Reply | Quote

In that case I would be inclined to test each record for each Title of Interest (TOI), until all TOIs have been located and their data extracted,
Does this snippet help?

Found_Title_Count = 0
Do
intPos1 = InStr(strLine, "SLAB")
intPos2 = InStr(strLine, "BASE")
intPos3 = InStr(strLine, "MT_FRAME")
If intPos1 > 0 Then
Found_Title_Count = Found_Title_Count + 1
' process the SLAB record

ElseIf intPos2 > 0 Then
Found_Title_Count = Found_Title_Count + 1
' process the BASE record

ElseIf intPos3 > 0 Then
Found_Title_Count = Found_Title_Count + 1
' process the MT_FRAME record

End If
Loop While Found_Title_Count < 3

End Sub

Reply | Quote

Thanks Don. You game me an idea on how to handle this issue.

Reply | Quote