Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with VBA (2002/SP3)

    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

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with VBA (2002/SP3)

    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.
    Regards
    Don

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VBA (2002/SP3)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with VBA (2002/SP3)

    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

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with VBA (2002/SP3)

    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?
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black>
    Found_Title_Count = 0
    <font color=blue>Do</font color=blue>
    intPos1 = InStr(strLine, "SLAB")
    intPos2 = InStr(strLine, "BASE")
    intPos3 = InStr(strLine, "MT_FRAME")
    <font color=blue>If</font color=blue> intPos1 > 0 <font color=blue>Then</font color=blue>
    Found_Title_Count = Found_Title_Count + 1
    <font color=448800>' process the SLAB record</font color=448800>

    <font color=blue>ElseIf</font color=blue> intPos2 > 0 <font color=blue>Then</font color=blue>
    Found_Title_Count = Found_Title_Count + 1
    <font color=448800>' process the BASE record</font color=448800>

    <font color=blue>ElseIf</font color=blue> intPos3 > 0 <font color=blue>Then</font color=blue>
    Found_Title_Count = Found_Title_Count + 1
    <font color=448800>' process the MT_FRAME record</font color=448800>

    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Loop</font color=blue> <font color=blue>While</font color=blue> Found_Title_Count < 3

    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  6. #6
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with VBA (2002/SP3)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •