I am writing a makro to read thousands of big text files, analyse their content and save a desired part of its content into my Excel worksheet. On another thread it said that the readAll method of a TextStream object is a good way to do so, so I copied a piece of code and built my makro around it. Every few hundred textfiles though it throws a weird error. The file being processed is being emptied by my makro and the makro stops working. Again: Before I start the makro the textfile that throws an error has content in it(as far as I can see it looks like any other textfile), but when Excel throws the error the textfile's content is deleted.
Can you think of the reason for this error or suggest an alternative that might now give this error?
Function readFileContent(FILENAME As String) As String
'reads the txt file into a string and deletes parts before the first "<TestFlow" Element`
Dim lngStart As Long
Dim lngLength As Long
Dim fsoMyFile As FileSystemObject
Dim tsTempo As TextStream
Dim StrContent As String
lngStart = 0
lngLength = 0
Set fsoMyFile = New FileSystemObject
Set tsTempo = fsoMyFile.OpenTextFile(FILENAME, ForReading)
StrContent = tsTempo.ReadAll
Set tsTempo = fsoMyFile.OpenTextFile(FILENAME, ForWriting, False, TristateFalse)
tsTempo.Write (StrContent)
'Cut of everything up to the first <TextFlow tag
lngStart = InStr(StrContent, "<TextFlow")
If lngStart = 0 Then
readFileContent = "SKIPPED"
End If
lngLength = Len(StrContent)
StrContent = Right(StrContent, lngLength - lngStart)
readFileContent = StrContent
End Function
The line StrContent = tsTempo.ReadAll is the line where the program stops. I get no error message.
As you use same file name for reading and writing, you should close it after each operation:
Set fsoMyFile = New FileSystemObject
Set tsTempo = fsoMyFile.OpenTextFile(FILENAME, ForReading)
StrContent = tsTempo.ReadAll
tsTempo.Close '// <=== CLOSE
Set tsTempo = fsoMyFile.OpenTextFile(FILENAME, ForWriting, False, TristateFalse)
tsTempo.Write (StrContent)
tsTempo.Close '// <=== CLOSE