Search for a word in a certain column in html table and return the full row where that word was found, or return the number of row in that table
Uses < table >, < / table >, < tr >, < / tr >, < td >, < / td > inside HTMLTable to look for ForWord in columns of table
Function SearchinHTMLTable(ForWord, HTMLTable, Optional inColumn = 1, Optional SearchMode = 1, _
Optional ReturnValue = "FullRow", Optional AndNotWord = "", Optional StopSearching = "", _
Optional StartingRow = 0)
' Search for a word in a certain column in html table and return the full row where that word was found, or return the number of row in that table
' Uses <table >, </table >, <tr >, </tr >, <td >, </td > inside HTMLTable to look for ForWord in columns of table
' inColumn = 1, 2, ...
' Which column to search in, using <td >, all three parameters (ForWord, AntNotWord and StopSearching) will be searched in that column
' SearchMode = 1, 2, 3, 4
' 1 to match word as part of column
' 2 to match start of col matching that word as in Gear%
' 3 to match end of col matching that word as in %Gear
' 4 to match full column of that word.
' ReturnValue = "FullRow" to return full row where that word was found
' = "RowNo" or anything else to return the number of row, 1,2,3, etc
' AndNotWord = Any word to make sure that column does not have it inside, always partial search
' So we need to return row where 1st has ForWord, but at the same time AndNotWord should not be there
' StopSearching = Any word to stop searching after it, always partial search
' If we find a row having part of this word, then stop searching and get back.
'
' Needs CutString3(), RemoveHTMLTags(), VBInstr()
XRr = 0
If ReturnValue = "FullRow" Then
Rett = ""
Else
Rett = 0
End If
For Each TRow In Split(HTMLTable, " </tr >")
TRow = TRow & " </tr >"
Colo1 = CutString3(TRow, inColumn, " </td >") & " </td >"
If Colo1 = TRow Then GoTo NextRow
XRr = XRr + 1
If StartingRow > 0 And StartingRow > XRr Then GoTo NextRow
TCol = RemoveHTMLTags(Colo1)
If TCol = "" Then GoTo NextRow
HaveWord = 0
' Stop Searching
If StopSearching > "" Then
' If we found partially the word StopSearching, then exit and stop searching
' Used for {$Until$}
If VBInstr(StopSearching, TCol, 1) > 0 Then Exit For
End If
If SearchMode = 1 Then ' Any part of column
HaveWord = IIf(VBInstr(ForWord, TCol, 1) > 0, 1, 0)
ElseIf SearchMode = 2 Then ' Start/Left part of column
HaveWord = IIf(UCase(Left(TCol, Len(ForWord))) = UCase(ForWord), 1, 0)
ElseIf SearchMode = 3 Then ' End/Right part of column
HaveWord = IIf(UCase(Right(TCol, Len(ForWord))) = UCase(ForWord), 1, 0)
ElseIf SearchMode = 4 Then ' Exact column
HaveWord = IIf(UCase(TCol) = UCase(ForWord), 1, 0)
End If
If HaveWord = 1 And AndNotWord > "" Then
' Do we need to search for the NotWord?
HaveWord = IIf(VBInstr(AndNotWord, TCol, 1) > 0, 0, 1)
End If
If HaveWord = 1 Then
' Found row with that word in 1st col, read column after this one with number
If ReturnValue = "FullRow" Then
Rett = TRow
Else
Rett = XRr
End If
Exit For
End If
NextRow:
DoEvents
Next
SearchinHTMLTable = Rett
End Function
Optional ReturnValue = "FullRow", Optional AndNotWord = "", Optional StopSearching = "", _
Optional StartingRow = 0)
' Search for a word in a certain column in html table and return the full row where that word was found, or return the number of row in that table
' Uses <table >, </table >, <tr >, </tr >, <td >, </td > inside HTMLTable to look for ForWord in columns of table
' inColumn = 1, 2, ...
' Which column to search in, using <td >, all three parameters (ForWord, AntNotWord and StopSearching) will be searched in that column
' SearchMode = 1, 2, 3, 4
' 1 to match word as part of column
' 2 to match start of col matching that word as in Gear%
' 3 to match end of col matching that word as in %Gear
' 4 to match full column of that word.
' ReturnValue = "FullRow" to return full row where that word was found
' = "RowNo" or anything else to return the number of row, 1,2,3, etc
' AndNotWord = Any word to make sure that column does not have it inside, always partial search
' So we need to return row where 1st has ForWord, but at the same time AndNotWord should not be there
' StopSearching = Any word to stop searching after it, always partial search
' If we find a row having part of this word, then stop searching and get back.
'
' Needs CutString3(), RemoveHTMLTags(), VBInstr()
XRr = 0
If ReturnValue = "FullRow" Then
Rett = ""
Else
Rett = 0
End If
For Each TRow In Split(HTMLTable, " </tr >")
TRow = TRow & " </tr >"
Colo1 = CutString3(TRow, inColumn, " </td >") & " </td >"
If Colo1 = TRow Then GoTo NextRow
XRr = XRr + 1
If StartingRow > 0 And StartingRow > XRr Then GoTo NextRow
TCol = RemoveHTMLTags(Colo1)
If TCol = "" Then GoTo NextRow
HaveWord = 0
' Stop Searching
If StopSearching > "" Then
' If we found partially the word StopSearching, then exit and stop searching
' Used for {$Until$}
If VBInstr(StopSearching, TCol, 1) > 0 Then Exit For
End If
If SearchMode = 1 Then ' Any part of column
HaveWord = IIf(VBInstr(ForWord, TCol, 1) > 0, 1, 0)
ElseIf SearchMode = 2 Then ' Start/Left part of column
HaveWord = IIf(UCase(Left(TCol, Len(ForWord))) = UCase(ForWord), 1, 0)
ElseIf SearchMode = 3 Then ' End/Right part of column
HaveWord = IIf(UCase(Right(TCol, Len(ForWord))) = UCase(ForWord), 1, 0)
ElseIf SearchMode = 4 Then ' Exact column
HaveWord = IIf(UCase(TCol) = UCase(ForWord), 1, 0)
End If
If HaveWord = 1 And AndNotWord > "" Then
' Do we need to search for the NotWord?
HaveWord = IIf(VBInstr(AndNotWord, TCol, 1) > 0, 0, 1)
End If
If HaveWord = 1 Then
' Found row with that word in 1st col, read column after this one with number
If ReturnValue = "FullRow" Then
Rett = TRow
Else
Rett = XRr
End If
Exit For
End If
NextRow:
DoEvents
Next
SearchinHTMLTable = Rett
End Function
ForWord, HTMLTable, Optional inColumn = 1, Optional SearchMode = 1, Optional ReturnValue = "FullRow", Optional AndNotWord = "", Optional StopSearching = "", Optional StartingRow = 0
Views 194
Downloads 39
CodeID
DB ID