Error with method 'ReadAll' of object 'ITextStream' failed

Question

I'm trying to split a big size txt file (around 300mb and 10 million lines) in the vba 1048576 row limit (every 1048576 lines to one independent sheet) and I am using this piece of code:

Mytext= CreateObject("Scripting.FileSystemObject").OpenTextFile(filepath).ReadAll

The problem is that in some computers I am getting this error:

Run-Time error '-2147417848 (80010108)':
Method 'ReadAll' of object 'ITextStream' failed

I dont know how to solve this. Any ideas?

The internal error is RPC_E_DISCONNECTED - The object invoked has disconnected from its clients. I'm guessing that it has to do with the file size. Why on earth do you need to read 10 million lines at once? What are you doing in the rest of the code? — Comintern, Aug 04 '16 at 12:24
excel isn't build for this kind of duty. even though it has 2^20 number of rows on each sheet, you can't really use all of them. The better way is to read the file into Access table — Rosetta, Aug 04 '16 at 14:17
What is charset of the text file? There are may be some chars sequences that `Scripting.FileSystemObject` can't process properly as `TextStream`. Try to use `ADODB.Stream`, `SAPI.spFileStream` or `MSXML2.XMLHttp` to read the content of the file as binary data instead of `Scripting.FileSystemObject`. Note `spFileStream` allows to load the content by chunks and reduce memory usage. — omegastripes, Aug 04 '16 at 14:25

score 1 · Accepted Answer · answered Aug 04 '16 at 14:08

Some points to note: -

Reading a 300MB file in a single go is a big ask, I'm surprised if it ever worked and not surprised it failed
In my experience, an Excel file over 80-90 MB in size is difficult to work with, unless you absolutely have to, I would recommend splitting the result into workbooks (Excel files) and not sheets (worksheets within a single workbook).

It is cumbersome but this would need to be done one line at a time. The below is an example of opening the text file and reading X number of lines into separate workbooks.

Public Sub Sample()
Dim ObjFSO      As Object
Dim ObjTS       As Object
Dim AryData()   As String
Dim LngDataRow  As Long
Dim LngWkbkNo   As Long
Dim WkBk        As Workbook
Dim WkSht       As Worksheet

'This dictates how many rows should be in each workbook
ReDim AryData(100000)


'Open then file
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
    Set ObjTS = ObjFSO.OpenTextFile(ThisWorkbook.Path & "\SampleFile.txt")

        'Process each line
        Do Until ObjTS.AtEndOfStream

            'If we have filled up our array then we need to output it
            If LngDataRow > UBound(AryData, 1) Then
                LngWkbkNo = LngWkbkNo + 1
                Set WkBk = Application.Workbooks.Add
                    Set WkSht = WkBk.Worksheets(1)
                        WkSht.Range("A1:A" & UBound(AryData, 1) + 1) = AryData
                    Set WkSht = Nothing
                    WkBk.SaveAs ThisWorkbook.Path & "\" & Right("000" & CStr(LngWkbkNo), 3) & ".xlsx"
                    WkBk.Close 0
                Set WkBk = Nothing

                'Reset the array and go back to the start
                ReDim AryData(UBound(AryData, 1))
                LngDataRow = 0
            End If

            'Add a line from the file into the array
            AryData(LngDataRow) = ObjTS.ReadLine
            LngDataRow = LngDataRow + 1
            DoEvents
        Loop
    Set ObjTS = Nothing
Set ObjFSO = Nothing

'Put the final lines into a file
If AryData(0) <> "" Then
    LngWkbkNo = LngWkbkNo + 1
    Set WkBk = Application.Workbooks.Add
        Set WkSht = WkBk.Worksheets(1)
            WkSht.Range("A1:A" & UBound(AryData, 1) + 1) = AryData
        Set WkSht = Nothing
        WkBk.SaveAs ThisWorkbook.Path & "\" & Right("000" & CStr(LngWkbkNo), 3) & ".xlsx"
        WkBk.Close 0
    Set WkBk = Nothing
End If

MsgBox "Done"

End Sub

I agree with Gary, but I would use a [batch file to split the text file](http://stackoverflow.com/questions/23593556/batch-split-a-text-file) instead. Its much quicker than using VBA to process the file, but if you want, you can call the batch file from a [Wscript.Shell object](http://stackoverflow.com/questions/37822050/cant-run-dir-from-wscript-shell-in-vba) and process the output. — Tim, Aug 04 '16 at 14:32
Or you could get *really* medieval on it and [open the file as a memory map](http://stackoverflow.com/a/30249386/4088852). — Comintern, Aug 04 '16 at 15:09

Error with method 'ReadAll' of object 'ITextStream' failed

1 Answers1