Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-11-2021, 03:57 AM
SamDsouza SamDsouza is offline Subscript out of Range Error when searched for string and its line number in Text File Windows 10 Subscript out of Range Error when searched for string and its line number in Text File Office 2013
Advanced Beginner
Subscript out of Range Error when searched for string and its line number in Text File
 
Join Date: Aug 2019
Posts: 37
SamDsouza is on a distinguished road
Default Subscript out of Range Error when searched for string and its line number in Text File

Hello

Am Getting Subscript out of Range Error using the following code

if the below defined in Function FileSearch(.........
WhatToFind = "Age"
FromFile = "C:\Working with Textfiles\Original2-Name-Age.txt"
Msgbox FileSearch(WhatToFind, FromFile)

I get Error Subscript out of Range Error

if used in UF_Initialize then
Msgbox FileSearch(WhatToFind, FromFile) displays blank




Code:
Private Sub UserForm_Initialize()
Dim WhatToFind As String, FromFile As String

WhatToFind = "Age"
FromFile = "C:\WOrking with Textfiles\Original2-Name-Age.txt"
MsgBox FileSearch(WhatToFind, FromFile)
'''''MSGBOX IS DISPLAYED BLANK

End Sub

Private Function FileSearch(ByVal WhatToFind As String, ByVal FromFile As String) As String
Dim objFSO As Object
Dim objTextFile As Object
Dim lngCount As Long, i As Long
Dim FileNum As Integer
Dim DataLine As String
Dim strFound As String
Dim bFound As Boolean
Dim vLine As Variant
    FileNum = FreeFile()
    Open FromFile For Input As #FileNum
    Do While Not EOF(FileNum)
        lngCount = lngCount + 1    'increment a counter'
        Line Input #FileNum, DataLine    ' read in data 1 line at a time'
        If InStr(1, DataLine, WhatToFind) > 0 Then    'the string is found'
            bFound = True    'set a boolean value to true'
            Exit Do    'and stop the loop'
        End If
    Loop
    Close #FileNum    'close the file
    If bFound = True Then    'The text string was found'
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set objTextFile = objFSO.OpenTextFile(FromFile, 1)
        'Read through the file line by line to the line after the found line'
        For i = 1 To lngCount + 1
            strFound = objTextFile.ReadLine    'and note the content of the line'
        Next i
        vLine = Split(strFound, Chr(34))    'split the line at the " symbol'
        FileSearch = vLine(1)    'read and output the second value'
        objTextFile.Close    'close the file'
        Set objFSO = Nothing
        Set objTextFile = Nothing
    Else    'The text was not found'
        FileSearch = "Not found" 'tell the user'
    End If
lbl_Exit:
    Exit Function
End Function
Will appreciate your help in correcting my mistake
Thanks
SamD
Reply With Quote
  #2  
Old 01-11-2021, 05:32 AM
gmayor's Avatar
gmayor gmayor is offline Subscript out of Range Error when searched for string and its line number in Text File Windows 10 Subscript out of Range Error when searched for string and its line number in Text File Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,344
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

Your code works here, assuming that the text file in question is compatible with the search pattern e.g.


Name "John Smith"
Age "27"
Date of Birth "12/12/1993"


would show you 12/12/1993 in the message box.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 01-11-2021, 07:38 AM
SamDsouza SamDsouza is offline Subscript out of Range Error when searched for string and its line number in Text File Windows 10 Subscript out of Range Error when searched for string and its line number in Text File Office 2013
Advanced Beginner
Subscript out of Range Error when searched for string and its line number in Text File
 
Join Date: Aug 2019
Posts: 37
SamDsouza is on a distinguished road
Default

This is the following structure of the original txt file:
Quote:
Name Tom
Age 13

Name John
Age 15

Name Jack
Age 16

Name Michael
Age 12
Hey as per your format i quickly changed the Data of Text File to:
Quote:
Name "Tom"
Age "13"
DOB "27/12/1993"

Name "John"
Age "15"
DOB "15/12/1991"

Name "Jack"
Age "16"
DOB "05/08/1990"

Name "Michael"
Age "12"
DOB "25/02/1994"

Name "Martin"
Age "33"
DOB "30/12/1980"

Name "George"
Age "43"
DOB "15/12/1970"
1. Yes indeed it reads the 1st date but does not move on further and reads the other dates in msgbox

Another observation
2. for Eg if WhatToFind ="Name" then its First Record will show the Value of Age =13 rather than "Tom"
if WhatToFind ="Age" then its First Record will show the Value of Date 27/12/1993

SamD
Reply With Quote
  #4  
Old 01-11-2021, 11:07 PM
gmayor's Avatar
gmayor gmayor is offline Subscript out of Range Error when searched for string and its line number in Text File Windows 10 Subscript out of Range Error when searched for string and its line number in Text File Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,344
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

Your code is only programmed to find the first instance and then read the following line, hence your observation.
If you want to read all the values and then read the values on the lines that reflect the 'WhatToFind' text then you need a different and simpler approach e.g.
Code:
Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 12 Jan 2021
Private Sub UserForm_Initialize()
Dim WhatToFind As String, FromFile As String

    WhatToFind = "DOB"
    FromFile = "C:\Working with Textfiles\Original2-Name-Age.txt"
    MsgBox FileSearch(WhatToFind, FromFile)
    '''''MSGBOX DISPLAYS DATES OF BIRTH
End Sub

Private Function FileSearch(ByVal WhatToFind As String, ByVal FromFile As String) As String
Dim FileNum As Integer
Dim DataLine As String
Dim strFound As String
Dim vLine As Variant
Dim strList As String

    FileNum = FreeFile()
    Open FromFile For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, DataLine    ' read in data 1 line at a time'
        If InStr(1, DataLine, WhatToFind) > 0 Then    'the string is found'
            If InStr(1, DataLine, Chr(34)) > 0 Then
                strFound = DataLine
                vLine = Split(strFound, Chr(34))    'split the line at the " symbol'
                strList = strList & vbCr & vLine(1)     'read and output the second value'
            End If
        End If
    Loop
    If strList = "" Then
        FileSearch = "Not Found"
    Else
        FileSearch = strList
    End If
    Close #FileNum    'close the file
lbl_Exit:
    Exit Function
End Function
Given the reference to a userform then surely this code is intended to do something more useful than pop up a message box e.g. list the items in a list box? In that case set the FileSearch to output the items directly to a list box (here ListBox1) e.g.
Code:
Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 12 Jan 2021 
Const FromFile As String = "C:\Working with Textfiles\Original2-Name-Age.txt"
Const WhatToFind As String = "DOB"

Private Sub UserForm_Initialize()
    ListBox1.Clear
    FileSearch WhatToFind, FromFile
End Sub

Private Function FileSearch(ByVal WhatToFind As String, ByVal FromFile As String)
Dim FileNum As Integer
Dim DataLine As String
Dim strFound As String
Dim bFound As Boolean
Dim vLine As Variant

    FileNum = FreeFile()
    Open FromFile For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, DataLine    ' read in data 1 line at a time'
        If InStr(1, DataLine, WhatToFind) > 0 Then    'the string is found'
            If InStr(1, DataLine, Chr(34)) > 0 Then
                strFound = DataLine
                vLine = Split(strFound, Chr(34))    'split the line at the " symbol'
                ListBox1.AddItem vLine(1)     'read and output the second value to the listbox'
            End If
        End If
    Loop
    Close #FileNum    'close the file
lbl_Exit:
    Exit Function
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #5  
Old 01-12-2021, 07:08 PM
SamDsouza SamDsouza is offline Subscript out of Range Error when searched for string and its line number in Text File Windows 10 Subscript out of Range Error when searched for string and its line number in Text File Office 2013
Advanced Beginner
Subscript out of Range Error when searched for string and its line number in Text File
 
Join Date: Aug 2019
Posts: 37
SamDsouza is on a distinguished road
Default

GMayor Sir,

Indeed valuable inputs. Thank you so much for the help you have provided and derive the results in two different objects.

Last question How can i define a blank line as string. Although vbCr , vbCrlf are predefined. One can see the 1st and 2nd Structure of text file in #3

Thank you so much

SamD
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Declare wBk as workbook. Set wBk = workbooks("NewBook.xls") gives error: Subscript out of range. Syed Shabbir Excel Programming 1 09-27-2020 09:35 AM
Subscript out of Range Error when searched for string and its line number in Text File replace with text contains a group number which is out of range Error Message raghugada Word 6 05-30-2017 02:09 PM
Runtime error '9': Subscript out of Range KingoftheKassel Excel Programming 1 06-13-2016 08:00 PM
VBA Conditional Formatting Error (Subscript out of Range)) tinfanide Excel Programming 1 05-15-2015 08:12 AM
Subscript out of Range Error when searched for string and its line number in Text File Subscript out of range error KHTAY Excel 6 03-28-2009 11:18 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft