reading entire text file using vba

Question

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

Open "C:\tester.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

brettdj's answer, slightly adjusted

Public Function readFileContents(ByVal fullFilename As String) As String
    Dim objFSO As Object
    Dim objTF As Object
    Dim strIn As String

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTF = objFSO.OpenTextFile(fullFilename, 1)
    strIn = objTF.readall
    objTF.Close

    readFileContents = strIn
End Function

Answer 2

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:\temp\test.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:\temp\test.txt", 1)
strIn = objTF.readall
X = Split(strIn, vbNewLine)
[h12].Resize(UBound(X) + 1, 1) = Application.Transpose(X)
objTF.Close

End Sub