Error code 52 when counting the number of rows in a text file

Question

I am writing a code to open a text file through VBA, and match the value of mail_id taken from excel in the notepad. I saw few sample codes for this, and have modified it according to my data and requirement, but when counting the number of lines of the code, it is displaying error number 52. Also, please let me know how can I search for a string in notepad, and copy the text below it, if the string matches. Please find below code for reference:

 If mail_id <> " " Then

      Dim fso As Object
      Dim sfile As String


      Set fso = CreateObject("shell.application")
      sfile = "C:\My Documents\Textfile.txt"
      fso.Open (sfile)
       f = FreeFile
         Do While Not EOF(f)
            Ingline = Ingline + 1
            Line Input #f, strLine
            If InStr(1, strLine, mail_id, vbBinaryCompare) > 0 Then
            MsgBox Ingline, vbInformation
            bInfound = True
            End If
            Exit Do
          'End If
      Loop
      Close #f
      If Not blnFound Then
           MsgBox "Search string not found", vbInformation
       End If

Its showing the error at Do While Not EOF(f)

score 1 · Answer 1 · answered Mar 05 '18 at 14:52

I think you mixed up different methods how to open text files in VBA:

This should open a file and read line by line:

Option Explicit

Public Sub ReadLineByLineFromTextFile()
    Const ForReading = 1, ForWriting = 2, ForAppending = 8

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim FileName As String
    FileName = "c:\temp\test.txt"

    Dim MyFile As Object
    Set MyFile = fso.OpenTextFile(FileName, ForReading)

    '' Read from the file line by line
    Do While MyFile.AtEndOfStream <> True
        Dim TextLine As String
        TextLine = MyFile.ReadLine

        '' Do stuff to TextLine here like …
        'If InStr(1, TextLine, mail_id, vbBinaryCompare) > 0 Then
        '    MsgBox "found", vbInformation
        '
        '    Exit Do 'cancel loop when found
        'End If

    Loop
    MyFile.Close
End Sub

score 0 · Answer 2 · answered Mar 05 '18 at 14:54

0

You are mixing up two different ways to read a text file in VBA. Both are explained in the same question on SO.

a) Use open: https://stackoverflow.com/a/11528932/7599798

b) Use FileSystemObject: https://stackoverflow.com/a/11529980/7599798

answered Mar 05 '18 at 14:54

FunThomas

23,043
3
18
34

Error code 52 when counting the number of rows in a text file

2 Answers2