sourcing a control on one page in multipage control from another page in the same multipage control

Question

I’m using VBA in Excel 2007. On an excel userform there four controls:

1. a single multipage control
2. an active x webbrowser control on page1 of the multipage control
3. a textbox control on page2 of the multipage control
4. a list box control on the userform

I’m trying to pass the loaded webbrowers document innerhtml to the textbox control using the onchange event but keep getting error 91 Object variable or With block variable not set. What am I doing wrong?

Here is the code:

Private Sub MultiPage1_Change()
 Dim strhtml As String
 Dim strSelectItem As String

 strhtml = ""


If IsNull(Me.ListBox1.value) Then
 MsgBox "No Source View selection made. Have gone with 'Doc innerHTML'"
 Me.ListBox1.Selected(0) = True
 strSelectItem = "Doc innerHTML"
 Else
 strSelectItem = Me.ListBox1.value
End If


 Select Case strSelectItem

 Case "Doc innerHTML"
 strhtml = WebBrowser1.Document.documentElement.innerhtml

 Case "Doc innerText"
 strhtml = WebBrowser1.Document.documentElement.innerText

 Case "Body innerHTML"
 strhtml = WebBrowser1.Document.body.innerhtml

 Case "Body innerText"
  strhtml = WebBrowser1.Document.body.innerText

 End Select


' strhtml = WebBrowser1.Document.documentElement.outerhtml
 MultiPage1.value = 1
  MultiPage1.Pages(1).TextBox2.value = ""
 MultiPage1.Pages(1).TextBox2.value = strhtml
End Sub

Private Sub UserForm_Initialize()
'Disable the Back button
CommandButton2.Enabled = False
'Disable the Forward button
CommandButton3.Enabled = False

With Me.ListBox1

.AddItem "Doc innerHTML"
.AddItem "Doc innerText"
.AddItem "Body innerHTML"
.AddItem "Body innerText"
End With

End Sub

Answer 1

Going out on a limb here but I have seen this type 91 error frequently when using IE/browser automation. Usually accompanied by "my code works if I step through it manually!"

The common problem is that you have not allowed the web-browser to finish loading, and if it's still loading, then the .Document is Nothing, which will yield that 91 error.

A solution that usually works is a ready waiting loop. The rest of your code looks OK assuming that it's actually named WebBrowser1.

How to make a ready-waiting loop for Browser control

Basically you need to put the thread in a loop until the browser has loaded the page. In pseudo-code, you're doing:

Do Until Browser Is Loaded
   DoEvents 'etc.
Loop

Using the WinAPI Sleep Function

You will need to use the WinAPI Sleep function (a lot of people use DoEvents or Application.Wait but I have found here that Sleep is preferable.

Because it is a Declare, you must put it in an ordinary module, it can't go in a class or userform module.

'## This needs to go in an ordinary code module
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Calling the WinAPI Sleep Function

Sleep 250   'sleep for 1/4 of a second, or Sleep 1000 to sleep for a full second, etc.

Putting it all together

You will need to put a Do ... Loop that calls on this function immediately after the .Navigate method.

   '## This goes in where you do Navigate method:
    WebBrowser1.Navigate "http://google.com"

    Do
        Sleep 250
    Loop While Not WebBrowser1.ReadyState <> READYSTATE_COMPLETE

    Debug.Print WebBrowser1.Document.DocumentElement.InnerHtml

If it still doesn't work...

Yes, there's always an exception. I do very little web-based automation, and most of what I do is just tinkering with stuff here to help people out. What I have noticed is that increasingly websites are served dynamically (AJAX maybe?) and that in these cases, the browser may be .ReadyState = READYSTATE_COMPLETE and even .Busy = False, yet the .Document is still Nothing.

IF that happens, you could put a secondary loop, like:

Dim numberOfAttempts
Do While WebBrowser1.Document Is Nothing
    Sleep 250
    numberOfAttempts = numberOfAttempts + 1
    If numberOfAttempts > 100 Then Exit Do
Loop

You may want to add an iterator/counter there and simulate a timeout otherwise this could potentially go in to an infinite loop.

Note I'm really limited in my ability to assist on this, the above has been tested but I have literally never been able to get the WebBrowser control to "work" for more than about 5 minutes. I can make a single call to any of the methods but subsequent calls to any method result in failure, and about 50% of the time it crashes the application.

Answer 2

Turns out the issue is a bug with the ActiveX Web Browser control when embedded into a multipage control. The Web Browser control will disappear if change between the pages of the multipage control. I think it can happen to other controls that are not native form controls.