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.

This previous answer on the same subject might be helpful: stackoverflow.com/questions/11528694/… — Netloh
– Netloh, Commented Dec 5, 2013 at 2: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
– Sanket, Commented Dec 5, 2013 at 3:17
possible duplicate of Importing 100 text files into Excel at once — Siddharth Rout
– Siddharth Rout, Commented Dec 5, 2013 at 15:22
same method can be seen here as well stackoverflow.com/questions/20128115/… — Siddharth Rout
– Siddharth Rout, Commented Dec 5, 2013 at 15:24

ChaimG · Accepted Answer · 2019-07-23 17:32:03Z

19

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

edited Jul 23, 2019 at 17:32

ChaimG

7,5925 gold badges41 silver badges50 bronze badges

answered Dec 5, 2013 at 3:54

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sanket Over a year ago

Hey brettdj, When I ran your code I get "ÿþU" in H12, no matter what is the content of the test file. Any suggestion on how to format it?

brettdj Over a year ago

Can you make your test file available?

Siddharth Rout Over a year ago

+ 1 Much better method than looping... A similar approach (faster than fso) is mentioned in the two links below op's question...

Netloh · Accepted Answer · 2013-12-05 03:33:11Z

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

answered Dec 5, 2013 at 3:33

Netloh

4,3784 gold badges27 silver badges38 bronze badges

3 Comments

Sanket Over a year ago

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 Over a year ago

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.

Netloh Over a year ago

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.

L Tyrone · Accepted Answer · 2024-08-02 03:50:31Z

10

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

Function LoadFileStr$(FN$)

  With CreateObject("Scripting.FileSystemObject")

          LoadFileStr = .OpenTextFile(FN, 1).readall

  End With

End Function

edited Aug 2, 2024 at 3:50

L Tyrone

8,34123 gold badges34 silver badges47 bronze badges

answered Jul 4, 2017 at 23:07

Harry S

5216 silver badges6 bronze badges

1 Comment

toncsi Jul 20 at 11:19

Hi, I like your solution. But how to close the file in this case?

Fidel · Accepted Answer · 2016-08-20 11:45:06Z

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

edited Aug 20, 2016 at 11:45

answered Aug 20, 2016 at 11:05

Fidel

7,53511 gold badges64 silver badges88 bronze badges

Comments

Fidel · Accepted Answer · 2016-08-20 11:46:27Z

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

answered Aug 20, 2016 at 11:46

Fidel

7,53511 gold badges64 silver badges88 bronze badges

Comments

dcromley · Accepted Answer · 2016-11-08 22:57:48Z

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

answered Nov 8, 2016 at 22:57

dcromley

1,4281 gold badge10 silver badges25 bronze badges

1 Comment

johny why Over a year ago

this works, but text file comes in with spaces inserted between every character

Slai · Accepted Answer · 2016-08-20 11:50:28Z

0

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

answered Aug 20, 2016 at 11:50

Slai

23k5 gold badges49 silver badges55 bronze badges

Comments

Marcelo Scofano Diniz · Accepted Answer · 2019-12-12 14:49:46Z

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

answered Dec 12, 2019 at 14:49

Marcelo Scofano Diniz

7091 gold badge12 silver badges27 bronze badges

Collectives™ on Stack Overflow

reading entire text file using vba

8 Answers 8

3 Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

3 Comments

1 Comment

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related