Search HTML for words (1 Viewer)

[stu_c]

Hi all
I have the below code that opens up Internet explorer and I want it to search the HTML for a certain word. when I run the code its coming back with an error
Run-time error: '-2147023170 (800706be) automation error The remote procedure call failed. then highlights
While .Busy Or .ReadyState <> 4

Any suggestions?

    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim html As String

    Set IE = New InternetExplorer
    With IE
        .Visible = True
        .navigate "http://fin1/ViewDetails.asp?Headerid=9249"
        While .Busy Or .ReadyState <> 4: DoEvents: Wend
        Set doc = .Document
        html = doc.documentElement.outerHTML
        Debug.Print html
        If InStr(html, "IncorrectID") > 0 Then
            MsgBox "Incorrect Ref"
        Else
            MsgBox "Correct Ref"
        End If
    End With

End Sub

 

[cheekybuddha]

You probably no longer have IE on your system.

What version of Access are you using?

Also, do you actually need to see IE and the webpage, or do you just need to know whether the page contains your searched-for word?

 

[stu_c]

Hello, Yes we do still use IE as its an internet webpage it only accepts IE, yes it does need to open

 

[cheekybuddha]

Yes we do still use IE as its an internet webpage it only accepts IE

Did you mean an 'intranet' webpage?

 

[Edgar_]

Option 1

ie.navigate yourURL
ie.Visible = True
  
Dim h As string
h = ""
  
Do While len(h) = 0
    On Error Resume Next
    h = ie.Document.documentElement.outerhtml
    On Error GoTo 0
Loop

Debug.Print h

If InStr(h, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

Option 2 is similar to above, but it uses shell to grab the ie window
Option 3 is see if a http request could do it

No particular order, but if you want ie to open, then this could work

 

[moke123]

html = doc.documentElement.outerHTML

are you sure you want the outerHTML?

 

[stu_c]

Hello,
I tried this code, it opens the IE but then it seems to crash the database with no errors?

Option 1

ie.navigate yourURL
ie.Visible = True
 
Dim h As string
h = ""
 
Do While len(h) = 0
    On Error Resume Next
    h = ie.Document.documentElement.outerhtml
    On Error GoTo 0
Loop

Debug.Print h

If InStr(h, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

Option 2 is similar to above, but it uses shell to grab the ie window
Option 3 is see if a http request could do it

No particular order, but if you want ie to open, then this could work

 

[Edgar_]

Hello,
I tried this code, it opens the IE but then it seems to crash the database with no errors?

Add this at the top of the module after option explicit, it's a widely used function to pause execution for a moment.

#If VBA7 Then
     Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else
     Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

Modify the code like this:

ie.navigate yourURL
ie.Visible = True

Dim h As Object
Set h = Nothing

Sleep 2000

Do While h Is Nothing
    On Error Resume Next
    Sleep 500
    Set h = ie.Document.documentElement
    On Error GoTo 0
Loop

Debug.Print h.outerHtml

If InStr(h.outerHtml, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

This would be less violent in trying to wait for the document to load. It probably crashed your app because the previous while loop exceeded some limit. The idea in this case is waiting 2 seconds before the loop runs and when it runs, it waits 0.5 seconds in between loops. I also made h an object instead of a simple string. We still have more options if this does not work and you can tweak the Sleep value for something that does not crash your app.

 

[stu_c]

Morning Edgar
Thank you for the below, I have put the code into the on Click event and popped the first bit of code at the top of everything, I put the timer up to 5000 but still having the same crashing issues?

The IE page loads buts thats as far as it goes

  Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate "http://pfin1/ViewDetails.aspId=11111"
    IE.Visible = True

Dim h As Object
Set h = Nothing

Sleep 5000

Do While h Is Nothing
    On Error Resume Next
    Sleep 500
    Set h = IE.Document.documentElement
    On Error GoTo 0
Loop

Debug.Print h.outerHTML

If InStr(h.outerHTML, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If
End Sub

Add this at the top of the module after option explicit, it's a widely used function to pause execution for a moment.

#If VBA7 Then
     Private Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else
     Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

Modify the code like this:

ie.navigate yourURL
ie.Visible = True

Dim h As Object
Set h = Nothing

Sleep 2000

Do While h Is Nothing
    On Error Resume Next
    Sleep 500
    Set h = ie.Document.documentElement
    On Error GoTo 0
Loop

Debug.Print h.outerHtml

If InStr(h.outerHtml, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

This would be less violent in trying to wait for the document to load. It probably crashed your app because the previous while loop exceeded some limit. The idea in this case is waiting 2 seconds before the loop runs and when it runs, it waits 0.5 seconds in between loops. I also made h an object instead of a simple string. We still have more options if this does not work and you can tweak the Sleep value for something that does not crash your app.

 

[Edgar_]

You should try to find what's making it crash. If the Sleep function is working, you could remove the While loop and let Sleep take over for more time, try with a value of 60000, that's 60 seconds, then try to Set the document. Does this make it crash?

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://pfin1/ViewDetails.aspId=11111"
IE.Visible = True

Dim h As Object

Sleep 60000
Set h = IE.Document.documentElement

Debug.Print h.outerHTML

If InStr(h.outerHTML, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

If it does not crash, lower the Sleep value until it's consistent. You can also try with this

Dim req As Object
Dim res As String
Set req = CreateObject("msxml2.xmlhttp")
req.Open "GET", "http://pfin1/ViewDetails.aspId=11111", False
req.send
res = req.responseText
Debug.Print res
If InStr(res, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

 

[stu_c]

Thank you, the first code kept freezing still but the second one worked perfectly!

You should try to find what's making it crash. If the Sleep function is working, you could remove the While loop and let Sleep take over for more time, try with a value of 60000, that's 60 seconds, then try to Set the document. Does this make it crash?

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://pfin1/ViewDetails.aspId=11111"
IE.Visible = True

Dim h As Object

Sleep 60000
Set h = IE.Document.documentElement

Debug.Print h.outerHTML

If InStr(h.outerHTML, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

If it does not crash, lower the Sleep value until it's consistent. You can also try with this

Dim req As Object
Dim res As String
Set req = CreateObject("msxml2.xmlhttp")
req.Open "GET", "http://pfin1/ViewDetails.aspId=11111", False
req.send
res = req.responseText
Debug.Print res
If InStr(res, "IncorrectID") > 0 Then
    MsgBox "Incorrect Ref"
Else
    MsgBox "Correct Ref"
End If

 

[cheekybuddha]

but the second one worked perfectly!

Also, do you actually need to see IE and the webpage

So you don't actually need to see the webpage (or use IE!)