Copying Text String From Website Into Excel VBA

Question

I need to have VBA look into the HTML of a website, find a certain string within the text, and place into an Excel cell a value containing that string, and X characters to the left of that string, let's say 20 for the sake of example.

For example, if I need to find the string "elit" in a site containing the following string:

Lorem ipsum dolor sit amet, consectetur adipiscing elit.

The code would need to return a value of "sectetur adipiscing elit" to a specified cell. That is, the string itself, and 20 characters to the left of the string.

Here's what I've come up with so far (I know .select is not best practice but it works for me):

Sub String_Checker()
Sheets("Sheet1").Range("a2").Select
Dim IE As Object
Do Until IsEmpty(ActiveCell)
    Set IE = CreateObject("internetexplorer.Application")
    IE.Visible = True
    IE.navigate "https://website.com"
     Do Until (IE.readyState = 4 And Not IE.Busy)
    
          DoEvents
Loop
Set objDoc = IE.document
strMyPage = objDoc.body.innerHTML
Dim s As String: s = ActiveCell.Offset(0, 1).Value
ActiveCell.Offset(0, 2).Value = Left(strMyPage, 20)

IE.Quit
ActiveCell.Offset(1, 0).Select
Loop
End Sub

That gives me the last 20 characters of the HTML, but I need to get the code to start "looking" at the specified string, which would consistently be ActiveCell.Offset(0,1).Value in the Excel. Any help would be appreciated. Thanks!

When the string is found, what would you like to return in the cell? The part left of or the part right of the found string? — VBasic2008
– VBasic2008, Commented Oct 9, 2020 at 12:48
@VBasic2008: The part left of the found string, as well as the string itself. — Learner04390
– Learner04390, Commented Oct 9, 2020 at 13:03
Is there an actual url to go with this please? Are you using string functions rather than an html parser because you are working with different webpages? — QHarr
– QHarr, Commented Oct 9, 2020 at 13:45
@QHarr: Unfortunately I cannot provide the specific website, as it is confidential. While the website I'm going to be pulling data from will be the same each time, there is a wildcard at the end of the URL that will change with each use. I have already accounted for this wildcard in my code so the script will navigate to the correct webpage. If an HTML parser makes more sense to you (it certainly sounds like what I should be using), I'd appreciate more info on how to do that. — Learner04390
– Learner04390, Commented Oct 9, 2020 at 14:51

VBasic2008 · Accepted Answer · 2020-10-09 16:32:42Z

String From innerHTML

This is a bad solution from top to bottom, but my investigation led to it and hopefully it should do the trick.

The Code

Option Explicit

Sub String_Checker()

' I only ran this from VBE. Sometimes the following error would occur:
' Run-time error '2125463506 (8150002e)':
' The text associated with this error code could not be found.
' I don't know why.
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    'IE.Visible = True
    IE.navigate "https://www.wikipedia.com"
    Do Until (IE.readyState = 4 And Not IE.Busy)
        DoEvents
    Loop
    Dim objdoc As Object
    Set objdoc = IE.document
    Dim strMyPage As String
    strMyPage = objdoc.body.innerHTML
    IE.Quit
    
    Const pLeft As Long = 20
    
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")
    
    Dim cel As Range
    Set cel = ws.Range("A2")
    Dim s As String
    Dim pStart As Long
    Dim pLen As Long
    
    Do Until IsEmpty(cel)
        s = cel.Offset(0, 1).Value
        pStart = InStr(1, strMyPage, s, vbTextCompare) - pLeft
        If pStart > 0 Then
        ' The string ('s') was found.
            pLen = InStr(1, strMyPage, s, vbTextCompare) + Len(s) - pStart
            s = Mid(strMyPage, pStart, pLen)
            On Error Resume Next
            ' Here I would receive the following error:
            ' Run-time error '1004': Application-defined or object-defined error
            ' It would occur when the first character would be "=".
            cel.Offset(0, 2).Value = s
            If Err.Number <> 0 Then
                cel.Offset(0, 2).Value = "'" & s ' Maybe this can always be used.
            End If
            On Error GoTo 0
        Else
        ' The string ('s') was NOT found.
        End If
        Set cel = cel.Offset(1)
    Loop

End Sub

Hey, a bad solution that works is better than a good one that doesn't! This did the trick with a couple of minor tweaks. I set the workbook definitions above the Loop instructions and it ran like a charm. Thanks to you and everyone who commented for your insights!

Collectives™ on Stack Overflow

Copying Text String From Website Into Excel VBA

1 Answer 1

String From innerHTML

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

String From innerHTML

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related