reading entire text file using vba

user_1344013 0
Viewed 61625

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.

vba excel
8 Answers

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
user_6410670

The following code will loop through each line in the text document and print these from range H12 and downward in the UI-sheet.

Sub ImportFromText()
    Open "C:\tester.txt" For Input As #1
    r = 0
    Do Until EOF(1)
        Line Input #1, Data
        Worksheets("UI").Range("H12").Offset(r, 0) = Data
        r = r + 1
    Loop
    Close #1
End Sub
user_21195230

Fidel's answer, over Brettdj's answer, adjusted for ASCII or Unicode and without magical numbers:

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

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTF = objFSO.OpenTextFile(fullFilename, IOMode:=ForReading, format:=IIf(asASCII, TristateFalse, TristateTrue))
    strIn = objTF.ReadAll
    objTF.Close
    readFileContents = strIn
End Function
user_27523080
Related