VBA ReadLine Error while reading from a file

Question

I am using Excel 2003 & I have following code in my macro.

Dim fs, a, retstring
Set fs = CreateObject("scripting.filesystemobject")
Set a = fs.OpenTextFile("C:\file.txt", ForReading, False)
Do While a.AtEndofStream <> True
  retstring = a.ReadLine
Loop
a.Close

When I execute this, it shows

"Runtime Error:5"

Invalid Procedure Call or argument at OpenTextFile

Define the constant ForReading = 1. Check out the code [here](http://stackoverflow.com/questions/1722196/what-is-dim-fso-myfile-filename-textline-in-vba). — deusxmach1na, Jun 15 '12 at 14:11
@deusxmach1na : Thanks for that. it works. Please put it as answer so that i can accept it. — logan, Jun 15 '12 at 14:18

score 2 · Answer 1 · answered Jun 15 '12 at 14:32

You must define the constant ForReading first. And you may as well define the constants ForWriting and ForAppending while you're at it.

Dim fs, a, retstring
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("C:\file.txt", ForReading, False)

Do While a.AtEndofStream <> True
   retstring = a.readline
Loop
a.close

score 2 · Answer 2 · answered Jun 15 '12 at 16:27

2

fso is considered slow. Here is a faster method to read a text file.

Sub Sample()
    Dim MyData As String, strData() As String
    Dim i as Long

    '~~> Read the entire file in 1 go
    Open "C:\MyFile.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    For i = LBound(strData) To UBound(strData)
        Debug.Print strData(i)
    Next
End Sub

answered Jun 15 '12 at 16:27

Siddharth Rout

147,039
17
206
250

Cool! Do you have a link to a tutorial on using For Binary? It looks interesting. – deusxmach1na Jun 15 '12 at 16:54
@deusxmach1na: There are plenty on the web. Here is the first one I came across... http://www.vb6.us/tutorials/reading-and-writing-binary-files-visual-basic – Siddharth Rout Jun 15 '12 at 17:30
@SiddharthRout The link you have given is for VB. will that work in VBA too ? – logan Jun 16 '12 at 06:27

score 0 · Answer 3 · answered Jun 15 '12 at 14:18

It worked when i did like this.

    Dim fs, a, retstring
    Set fs = CreateObject("scripting.filesystemobject")
    Set a = fs.OpenTextFile("C:\Users\228319\Desktop\file.txt", 1, False)
    Do While a.AtEndofStream <> True
    retstring = a.readline
    Loop
    a.Close

score -1 · Answer 4 · answered Oct 14 '14 at 13:59

-1

I am using Excel 2007 and got the same problem with near the same code snippet. Enabling 'Microsoft Scripting Runtime' should solve it (Main menu >Tools > References), it worked for me.

answered Oct 14 '14 at 13:59

zoltanh

13
3

VBA ReadLine Error while reading from a file

4 Answers4