excel - reading entire text file using vba

Question

I'm trying to read a text file using vba. I tried the below code

Open "C:ester.txt" For Input As #1
Worksheets("UI").Range("H12").Value = Input$(LOF(1), 1)
Close #1

When I run this I'm getting an error.

Run-time error '62'. Input past end of file.

The content of text file is:

Unable to open COM10. Make sure it is connected
Plus other stuff
And more stuff
way more stuff

Thanks in advance for help.

Answer 1

Rather than loop cell by cell, you can read the entire file into a variant array, then dump it in a single shot.

Change the path from C:empest.txt to suit.

Sub Qantas_Delay()
Dim objFSO As Object
Dim objTF As Object
Dim strIn 'As String
Dim X

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTF = objFSO.OpenTextFile("C:empest.txt", 1)
strIn = objTF.readall
X = Split(strIn, vbNewLine)
[h12].Resize(UBound(X) + 1, 1) = Application.Transpose(X)
objTF.Close

End Sub