16

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.

brettdj
  • 54,857
  • 16
  • 114
  • 177
Sanket
  • 746
  • 3
  • 13
  • 26
  • 1
    This previous answer on the same subject might be helpful: http://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba – Netloh Dec 05 '13 at 02:55
  • Thanks Hansen. But my text file may have multiple lines. In the post link given by you I can only read one line. But I want to read the entire text file. My text file can have multiple lines. – Sanket Dec 05 '13 at 03:17
  • possible duplicate of [Importing 100 text files into Excel at once](http://stackoverflow.com/questions/19410503/importing-100-text-files-into-excel-at-once) – Siddharth Rout Dec 05 '13 at 15:22
  • See the array method mentioned in the above link – Siddharth Rout Dec 05 '13 at 15:23
  • same method can be seen here as well http://stackoverflow.com/questions/20128115/input-past-end-of-file-vba-excel – Siddharth Rout Dec 05 '13 at 15:24

8 Answers8

17

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
ChaimG
  • 7,024
  • 4
  • 38
  • 46
brettdj
  • 54,857
  • 16
  • 114
  • 177
13

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
Netloh
  • 4,338
  • 4
  • 25
  • 38
  • Hey Thanks again. Your code works!!.. but I'm getting two additional characters in the first line. My first line output looks like this ÿþUnable to open COM10. Make sure it is connected. I tried using the trim fucntion and worksheets.clean method but it is not removing those additional charecters. any help on this? – Sanket Dec 05 '13 at 05:17
  • I used this Worksheets("UI").Range("H12").Value = Right(Worksheets("UI").Range("H12").Value, Len(Worksheets("UI").Range("H12").Value) - 2) Thanks for all your help.. setting your answer as the right answer. – Sanket Dec 05 '13 at 05:54
  • 2
    The two characters appears because because you are reading from a unicode text document. To avoid this you would have to use the `OpenTextFile` method which can open and understand the unicode format. This is, however, an entirely different syntax from what you are using now. – Netloh Dec 05 '13 at 06:02
8

More Slightly modified for those who do not like VBA to have to make up explicit variables and then waste time transfer data to them.. Let With. do the job

Function LoadFileStr$(FN$)

  With CreateObject("Scripting.FileSystemObject")

          LoadFileStr = .OpenTextFile(FN, 1).readall

        End With

End Function
3vts
  • 778
  • 1
  • 12
  • 25
Harry S
  • 481
  • 6
  • 5
6

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
Fidel
  • 7,027
  • 11
  • 57
  • 81
2

To read line by line:

Public Sub loadFromFile(fullFilename As String)

    Dim FileNum As Integer
    Dim DataLine As String

    FileNum = FreeFile()
    Open fullFilename For Input As #FileNum

    While Not EOF(FileNum)
        Line Input #FileNum, DataLine
        Debug.Print DataLine
    Wend
End Sub
Fidel
  • 7,027
  • 11
  • 57
  • 81
1
Sub LoadFile() ' load entire file to string
' from Siddharth Rout
' http://stackoverflow.com/questions/20128115/
    Dim MyData As String
    Open "C:\MyFile" For Binary As #1
    MyData = Space$(LOF(1)) ' sets buffer to Length Of File
    Get #1, , MyData ' fits exactly
    Close #1
End Sub
dcromley
  • 1,373
  • 1
  • 8
  • 23
0

I think an easier alternative is Data > From Text and you can specify how often the data is refreshed in the Properties.

Slai
  • 22,144
  • 5
  • 45
  • 53
0

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
Marcelo Scofano Diniz
  • 590
  • 1
  • 7
  • 24