Split text file only works once

sumdumgai

Registered User.
Local time
Today, 05:58
Joined
Jul 19, 2007
Messages
453
Following code works for smaller .txt files (e.g. 10mb). But for large .txt files (e.g., 120mb), it only works once. Then must close and reopen Access to have it work again:


Code:
Function SplitFileIntoLines(PathFileName As String) As String()

    FileNum = FreeFile
    Open PathFileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
    Close #FileNum
    TotalFile = Replace(TotalFile, vbCr, vbLf)
    TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
    SplitFileIntoLines = Split(TotalFile, vbLf)
End Function


    FileLines = SplitFileIntoLines(filename)

    rcdCount = UBound(FileLines)
rcdCount returns -1 on second and subsequent tries. BTW, when I execute this code as an Excel VBA macro, it always works.


Any help would be appreciated.


Thanks.
 
Lots of questions to help you/us troubleshoot.

You have two lines of code after End Function.
Where are these lines located?

Why does it need to run repeatedly?
Have you tried debugging line by line? If not do so.
Have you tried adding error handling?

What does FileLines show using Debug on second run?
Similarly for TotalFile on which everything else depends
 
I would add some error handling.

Maybe the openfile action works the first time, but not the second.
Out of interest, I don't see why you get -1 as a result rather than 0 for the ubound result, so there must be something unusual happening.


So I just tried it with a text file. I had to type the variables, but it worked fine.


How are you calling each time?
 
Thanks.
1) The two lines after End Function are code lines in another module that calls the 'SplitFileIntoLines' function.
2) I'd like to run it repeatedly because I'm testing the code in the other module and do not want to close Access each time I run a test.
3) I've MSGBOX'd the variables in the SplitFileIntoLines function. On first pass, LOF(FileNum) matches Len(TotalFile). But on second pass, Len(TotalFile) is zero.
4) Filelines = -1 on second pass. Len(TotalFiles) = 0
 
Code:
Function SplitFileIntoLines(PathFileName As String) As String()
    Dim fso As Object
    Dim strTextStream As Object
    Dim strTextLine As String
    Dim i As Long
    Dim arrLines() As String
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set strTextStream = fso.OpenTextFile(strFileName, 1)
    With strTextStream
        Do While Not .AtEndOfStream
            i = i + 1
            strTextLine = .ReadLine
            ReDim Preserve arrLines(1 To i)
            arrLines(i) = strTextLine
        Loop
    End With
    strTextStream.Close
    Set strTextStream = Nothing
    Set fso = Nothing
    SplitFileIntoLines = arrLines
End Function
 
Thanks Husky. Function is called by macro as 'FileLines = SplitFileIntoLines(filename)'. If I remove 'On error resume next', first pass gives 'Out of memory' error on 'TotalFile = Space(LOF(FileNum))'. Second pass gives 'Out of memory' on 'Get #FileNum, , TotalFile'.
If 'On error resume next', first pass works. Second pass gives subscript error on 'Debug.Print UBound(FileLines)' and rcdCount gives -1.
 
Thanks Arnelgp. Your code seems to work fine. I'll test with it and will probably go with it.
 

Users who are viewing this thread

Back
Top Bottom