Keep going to the end of the text file(s). When you're done, save the new text file, delete the original file, and rename the new file to the old one's filename. (I didn
To explain the sample code below, I was searching for a particular string (in this case I was looking for lines like this:
,,R01,222,14876.56,,,,,,
where I wanted to add a dollar sign to the 5th field (14876.56)
Expand|Select|Wrap|Line Numbers
- Dim FSO As Scripting.FileSystemObject
- Dim tsFix As Scripting.TextStream
- Dim tsOrig As Scripting.TextStream
- Dim str_TextLine As String
- Dim str_TempFile As String
- Dim as_Items() As String
- Dim str_OutgoingText As String
- Dim lng_ItemNum As Long
- Set FSO = New Scripting.FileSystemObject
- If FSO.FileExists(str_SavedCSVFileName) Then
- ' open a new (temp) file
- str_TempFile = "TMP_" & str_SavedCSVFileName
- Set tsFix = FSO.CreateTextFile(str_TempFile, True)
- ' open original CSV file
- Set tsOrig = FSO.OpenTextFile(str_SavedCSVFileName)
- Do Until tsOrig.AtEndOfStream
- str_TextLine = tsOrig.ReadLine
- If Len(str_TextLine) > 2 And Len(str_TextLine) < 40 Then
- ' should be a Totals line as below:
- ' find 4th comma
- as_Items = Split(str_TextLine, ",")
- For lng_ItemNum = LBound(as_Items) To UBound(as_Items)
- ' when we find the 4th comma, if the next char is a number, we'll add a $ in front of it
- If lng_ItemNum = 4 And IsNumeric(as_Items(lng_ItemNum)) Then
- ' this should be a dollar value
- If InStr(as_Items(lng_ItemNum), ".") Then
- str_OutgoingText = Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum)) End If
- End If
- DoEvents
- Next
- ' write out contents of original CSV file but replace line above with one containing $
- tsFix.WriteLine Replace(str_TextLine, as_Items(lng_ItemNum), "$" & as_Items(lng_ItemNum))
- Else
- ' write out contents of original CSV file as is
- tsFix.WriteLine str_TextLine
- End If
- DoEvents
- Loop
- End If
- tsOrig.Close
- Set tsOrig = Nothing
- tsFix.Close
- Set tsFix = Nothing
- If FSO.FileExists(str_TempFile) Then
- FSO.CopyFile str_TempFile, str_SavedCSVFileName, True
- End If