GS said:
No need to open the file more than once! You could *dump* the file
contents into an array and work the array for the rows you need to
manipulate, then *dump* the array back into a new file. If your
manipulation adds/removes rows then you'll have to use a temp array (or
string) for the output text.
If the file has a header row in the 1st line then the number of lines =
UBound(vaText). Otherwise, the number of lines is UBound(vaText)+1. I
suggest vaText be dimmed a variant data type, not dimmed an array...
Dim vaText As Variant
vaText = Split(Read_TextFromFile(sFilename), vbCrLf)
..and when ready to put back...
Write_TextToFile Join(vaText, vbCrLf), sNewFilename
..where Read_TextFromFile is a function that takes a filename and
returns a string, and Write_TextToFile is a sub that takes the text and
the filename.
Depending on available resources, this more efficient approach should
be quite fast.
Yes, "depending on available resources" is the operative phrase here. On my
admittedly-older workstation (1GHz Athlon64, ~700MB RAM), Split() made
Excel "run off into the bushes and not come back" (bonus points for anyone
who recognizes the quote), with the added bonus that you can't break in the
middle of a Split() operation.
Depending on Salmec's specific situation, it might very well be worth it to
just skip the "Split" step and work directly on the string itself. Perhaps
you (or someone else) can improve upon my code.
Salmec, I'm assuming this is part of what you needed help with in the other
group (microsoft.public.office.developer.vba, if anyone else is interested)
so I'll just roll them together. This makes the code somewhat more complex
than before but that can't be helped.
Public Sub Parsedxf()
'***IMPORTANT!*** If there are leading or trailing spaces,
'put them inside the quotes or you won't get *any* matches.
Const LOOKFOR As String = "HATCH" & vbCrLf
'Ditto this one (sample data had 1 leading space):
Const DATAMATCH As String = vbCrLf & " 62" & vbCrLf
LOOKFORLEN = Len(LOOKFOR)
DATAMATCHLEN = Len(DATAMATCH)
Dim vaText As Variant, stmp As String
Dim sFileName As String
Dim iFileNum As Integer
Dim sBuf As String
'Don't know what the below are for;
'not going to delete them, but they serve no purpose here.
'Dim Fields As String
'Dim TempStr As String
'Dim strGenerator(0 To 3) As String
'Dim i As Integer
'i = 0
sFileName = "E:\Batch\parse.txt"
'Does the file exist?
If Len(Dir$(sFileName)) Then
iFileNum = FreeFile
Open sFileName For Binary As iFileNum
sBuf = Space$(LOF(iFileNum))
Get #iFileNum, 1, sBuf
Close iFileNum
Do 'This is where the actual work is done. Here there be tigers!
where = InStr(where + 1, sBuf, LOOKFOR)
If where Then
vatmp1 = InStr(where + LOOKFORLEN, sBuf, vbCrLf)
If vatmp1 Then
stmp = Mid$(sBuf, where + LOOKFORLEN, _
vatmp1 - where - LOOKFORLEN)
Select Case Trim$(stmp)
Case "5"
vatmp2 = InStr(vatmp1 + 2, sBuf, vbCrLf)
If vatmp2 Then
stmp = Mid$(sBuf, vatmp1 + 2, vatmp2 - vatmp1 - 2)
If Trim$(stmp) = "A7123" Then
vatmp1 = InStr(vatmp2, sBuf, DATAMATCH)
If vatmp1 Then
vatmp2 = InStr(vatmp1 + DATAMATCHLEN, sBuf, vbCrLf)
'Time to extract the data...
If vatmp2 Then
stmp = Mid$(sBuf, vatmp1 + DATAMATCHLEN, _
vatmp2 - vatmp1 - DATAMATCHLEN)
Else
stmp = Mid$(sBuf, vatmp1 + DATAMATCHLEN)
End If
ltmp = Len(stmp) 'Save this; important later.
'At this point stmp should have the data you want;
'manipulate it here. Minor example:
stmp = stmp + 1
'Then put it back into the extracted data:
Mid$(sBuf, vatmp1 + DATAMATCHLEN, ltmp) = _
Space$(ltmp - Len(stmp)) & stmp
Else
'No more instances of 62? No need to keep looking.
Exit Do
End If
End If
End If
End Select
End If
Else
Exit Do
End If
Loop
iFileNum = FreeFile
Open "outputfile" For Binary As iFileNum
Put #iFileNum, 1, sBuf
Close iFileNum
End If
End Sub
On my workstation (the above-mentioned Athlon64), this takes about 3
seconds to run, with your appropriate data being found every 36 lines.
(This will be affected by how often "HATCH" is in the actual data, how
often the rest of the data matches what you're looking for, and what kinds
of data manipulation you actually do.)
