Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reading Text Files (XP: SP3)

    I have a text file that contains information regarding business units. Field headings being: BUnit, Account, Descr and Amt. Each business unit in the text file has a different number of lines.

    What I am trying to achieve is to run different code when the business unit changes. I have attempted to write different lines of code to account for the business unit change but have not come up with a sound approach.

    Example:
    Sub GetFile()
    Close #1
    Dim LineofText As String

    FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    Open FileToOpen For Input As #1
    Do While Not EOF(1)
    Line Input #1, LineofText
    Sheets("Sheet1").Range("TextLine") = LineofText

    oVar = Split(Sheets("Sheet1").Range("TextLine"), ",")
    oBUnitNew = oVar(0)
    If oBUnitNew<> oBUnitPrior Then RunDifferentCode Keep on reading text file until the business unit changes; then run some code

    oBUnitPrior = oBunitNew
    Loop
    Close #1
    End Sub


    Any assistance would be appreciated.
    John

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Text Files (XP: SP3)

    Can you upload a sample of the text file that can be used to test the code?
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reading Text Files (XP: SP3)

    Perhaps you could tell us what you want to accomplish. Something like the following should work, but it's not clear what you want to do.

    Sub GetFile()
    Dim strLineOfText As String
    Dim varFileToOpen As Variant
    Dim arrParts() As String
    Dim strUnitNew As String
    Dim strUnitPrior As String

    varFileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If varFileToOpen = False Then Exit Sub

    Open varFileToOpen For Input As #1
    Do While Not EOF(1)
    Line Input #1, strLineOfText
    arrParts = Split(strLineOfText, ",")
    strUnitNew = arrParts(0)
    If strUnitNew <> strUnitPrior Then
    ' some code
    Else
    ' other code
    End If
    strUnitPrior = strUnitNew
    Loop
    Close #1
    End Sub

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Text Files (XP: SP3)

    Legare,

    I finally came up with this:

    Sub GetFile()
    Close #1
    Dim LineofText As String

    FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    Open FileToOpen For Input As #1
    Do While Not EOF(1)
    Line Input #1, LineofText
    Sheets("Sheet1").Range("TextLine") = LineofText

    oVar = Split(Sheets("Sheet1").Range("TextLine"), ",")

    If oBUnitNew <> oBUnitPrior Then
    oBUnitPrior = oBUnitNew
    End If

    If oBUnitNew = oBUnitPrior Then
    Run Some Code
    End If

    oBUnitPrior = oBunitNew
    Loop
    Close #1
    End Sub


    It may be written eloquently but it works.

    Thanks,
    John

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reading Text Files (XP: SP3)

    Without your file to test on I can't tell for sure, but it looks like "Run some code" is going to run for every line in the input file whether the Unit changes or not. Your two If statements do not seem to do anything useful and could be removed.
    Legare Coleman

Posting Permissions

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