Reading Text Files (XP: SP3)

[jstevens]

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

[LegareColeman]

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

[HansV]

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

[jstevens]

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

[LegareColeman]

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.