|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 Thanks SamD |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
This is the following structure of the original txt file:
Quote:
Quote:
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 |
#4
|
||||
|
||||
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 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 |
#5
|
|||
|
|||
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 |
|
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 |
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 | KHTAY | Excel | 6 | 03-28-2009 11:18 PM |