Start of file problem.

[hunter1978]

Hi,

I wonder if somebody could help me. My VB knowledge is limited and I need some help with a VB script an ex member of staff has written.

The script looks at a text file and pulls the infomation into Excel. As far as I can tell the scripts looks for === as the beginning point and then pulls the folling information until it gets to the end of line.

Example:-

=== 22601331 NC MANORD 1624.0 812.00 748.29 0.00 0.00 0.00 0.00 1560.29 0.00 060130 22601622 NC MANORD 1150.0 575.00 530.25 0.00 0.00 0.00 0.00 1105.25 0.00 060116

However, the format of the text file has changed and instead of the info being on 1 line it's now on several lines

Example:-

===
22601331 NC MANORD 1624.0 812.00 748.29 0.00 0.00 0.00 0.00 1560.29 0.00 060130
22601622 NC MANORD 1150.0 575.00 530.25 0.00 0.00 0.00 0.00 1105.25 0.00 060116

So the script is falling over as it isn't finding anything after the start point. I need a way of change the scripts from looknig for 1 continues line to looking for several lines.

The code that is currently being used is:-

 Dim Airline, AWBNum As String 
Dim ProcDone, Cont, UseEst
Dim Charges(100)
Dim Descs(100), ChgDesc(100) As String
Dim Estimates(100), Actuals(100)
Dim I, J As Integer
crow = 2
chgcode = Sheets("Charges").Range("A" & crow)
While chgcode <> ""
Descs(chgcode) = Sheets("Charges").Range("B" & crow)
crow = crow + 1
chgcode = Sheets("Charges").Range("A" & crow)
Wend
numdesc = 0
For I = 1 To 99
desc = Descs(I)
If desc <> "" Then
found = False
For J = 1 To numdesc
If desc = ChgDesc(J) Then found = True: J = 99
Next J
If Not found Then
numdesc = numdesc + 1
ChgDesc(numdesc) = desc
End If
End If
Next I
ChgDesc(numdesc + 1) = "Other"
ProcDone = False
infile = Range("E7")
UseEst = Range("AA1")
On Error GoTo NoInFile
Open infile For Input As #1
On Error GoTo 0
Workbooks.Add
bookname = ActiveWorkbook.Name
While Not EOF(1)
fstart = 0
While fstart = 0 And Not EOF(1)
Line Input #1, inrec
Cont = False
recno = recno + 1
fstart = InStr(inrec, "IATA CARGO ACCOUNTS SETTLEMENT SYSTEM")
If fstart = 0 Then
stpos = InStr(inrec, "=== ")
If stpos <> 0 Then
Cont = True
fstart = 1
Else
If Left(inrec, 7) = "CARRIED" Then
While Not EOF(1) And Left(inrec, 7) <> "BROUGHT"
Line Input #1, inrec
recno = recno + 1
Wend
If Not EOF(1) Then
Line Input #1, inrec
recno = recno + 1
Cont = True
stpos = -3
fstart = 1
End If
End If
End If

Sorry for the stupidly long post but if anybody could shed any light on this I would be more then grateful.

Thanks in advance.

[Banfa]

Your question is unanswerable because you have not posted complete code and the part you have missed is the part that contains the problem you are asking about. This becaomes obvious when I reformat some of the posted code (the end) so that it is properly indented.

The code stpos = InStr(inrec, "=== ") tests for your start condition, the string probably wants to change from "=== " to "===" as you have no garunttee of a space after the = signs. If this is true it sets the variable cont to True. The code that this variable is used by has to be after the end of the code you have posted.

While Not EOF(1)
  fstart = 0
  While fstart = 0 And Not EOF(1)
    Line Input #1, inrec
    Cont = False
    recno = recno + 1
    fstart = InStr(inrec, "IATA CARGO ACCOUNTS SETTLEMENT SYSTEM")
    If fstart = 0 Then
      stpos = InStr(inrec, "=== ")
      If stpos <> 0 Then
        Cont = True
        fstart = 1
      Else
        If Left(inrec, 7) = "CARRIED" Then
        While Not EOF(1) And Left(inrec, 7) <> "BROUGHT"
          Line Input #1, inrec
          recno = recno + 1
        Wend
        If Not EOF(1) Then
          Line Input #1, inrec
          recno = recno + 1
          Cont = True
          stpos = -3
          fstart = 1
        End If
      End If
    End If

[Rodney Roe]

I don't know if you want to rewrite your program but this would work and it's a little cleaner and easier to read.

Dim F, FS As Object
Dim strContents, arrContents() As String
Const ForReading = 1, ForWriting = 2, ForAppending = 8

Sub getString()

Set FS = CreateObject("Scripting.FileSystemObject")
Set F = FS.opentextfile("C:\Rodney\Test.txt", ForReading) 'Change the path to your .txt file

Do Until InStr(1, strContents, "===") = 1
    strContents = F.readline
Loop
ReDim arrContents(0)

Do While Not F.atendofstream
    ReDim Preserve arrContents(UBound(arrContents) + 1)
    strContents = F.readline
    arrContents(UBound(arrContents)) = strContents
Loop

Range("A1").Select
For i = 1 To UBound(arrContents)
    ActiveCell = arrContents(i)
    ActiveCell.Offset(1, 0).Select
Next
End Sub