Today's Code snippet comes to us from Jeff Miller. He was asked to take a look at a script that was running slowly. This script opened a file and read it in. One of the very first things he noticed was something that was happening with each pass of the loop that read the file.

He writes, "I noticed first off that the file was being re-opened with each pass of the loop and somehow it was managing to process each line of the file by only using ReadLine."

It's no surprise that Jeff called this setup "The Fragmenter"

Sitting innocently near the end of the function was a jewel, waiting to be discovered. The Jewel of the File: Delete_Line.

Private Sub ProcessFile(strFileName)

Do Until blnLastTime = True

Set fileReader = fso.OpenTextFile(strFileName)

If fileReader.AtEndOfStream = True Then
blnLastTime = True
Else
strTextLine = fileReader.ReadLine
End If

….

fileReader.Close
Delete_Line (strFileName)
Loop
fileReader.Close

End Sub



Private Sub Delete_Line(strFile)

Set fileReader = fso.OpenTextFile(strFile)

If fso.FileExists(strFile & "2") Then
fso.DeleteFile (strFile & "2")
End If

Set fileWriter = fso.CreateTextFile(strFile & 2)

If fileReader.AtEndOfStream = False Then
fileReader.ReadLine
End If

If fileReader.AtEndOfStream = False Then
strLine = fileReader.ReadAll
fileWriter.Write (strLine)
End If


fileReader.Close
fileWriter.Close

fso.DeleteFile strFile, True
fso.CopyFile strFile & "2", strFile, True
fso.DeleteFile strFile & 2, True

End Sub

Moral of the story? Opening files is never trivial, especially in a loop, but multiple times - heart attack! Also,before calling a function inside a loop, find out what that function does. And... Oh never mind, there are too many to list.

What's your moral of the story?