VBA text file read fails when large

[krausr79]

So I'm reading a text file with extension .DAT from the file into excel. I assembled a method to do this from a few different posts. Here is the method which works:
Sub LoadFile()
Dim objFSO As Object
Dim objTF As Object
Dim strIn As String
Dim X
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile("C:\DL\DATATEST.DAT", 1)
strIn = objTF.readall
X = Split(strIn, vbNewLine)
Range("A1:A" & UBound(X) + 1) = Application.WorksheetFunction.Transpose(X)
objTF.Close
End Sub

But it stops working on a larger (11 MB) file. I get a Error 13 Type mismatch. It doesn't seem to be related to the data, because if I delete either the first or second half of all the data it works on either smaller file.

A line-by-line reading method is no good because that takes about 1 second per 500 lines and I've got upwards of 120k.

 

[ranman256]

error 13 means its trying to put a string into a numeric variable.

Did you try importing via FILE, OPEN? (instead of using code)

 

[V_Malkoti]

If you want to read the text file into Excel, why not read it as QueryTable to import it?

 

[krausr79]

I am able to import the data manually in the ways you two describe. In vba, I'd rather not deal with learning the particulars of querytables and I also didn't want to leave a data connection in the workbook. I found a thread with a solution I like by using your ideas as search terms:

vba: Importing text file into excel sheet - Stack Overflow
Sub Sample()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Sheet1") '<~~ Sheet where you want to import
Set wbO = Workbooks.Open("C:\OMEGA\P-19_071015.DAT")
wbO.Sheets(1).Cells.Copy wsI.Cells
wbO.Close SaveChanges:=False
End Sub