Excel VBA create an embedded WebBrowser and use it

Question

Hi I'm trying to dynamically create a web browser inside a spreadsheet and then use it but the WebBrowser functions don’t seem to work

Here is how I create the WebBrowser

Set myWebBrowser = Sheets("test").OLEObjects.Add(ClassType:="Shell.Explorer.2", Link:=False, DisplayAsIcon:=False, left:=147, top:=60.75, width:=141, height:=96)

This will work

myWebBrowser.top = 10

But this will give me an error

myWebBrowser.Navigate ("about:blank")

Any ideas on what should I do thank you

UPDATE:

This will also don't work and give an error:

myWebBrowser.Object.Document.body.Scroll = "no"
myWebBrowser.Object.Silent = True
myWebBrowser.Object.Navigate ("about:blank")
While myWebBrowser.Object.ReadyState <> READYSTATE_COMPLETE
    Application.Wait (Now + TimeValue("0:00:01"))
Wend
myWebBrowser.Object.Refresh

UPDATE 2 (almost there):

Now I need a way to remove the Sheet2.Activate Sheet1.Activate

Sheet2.Activate
Sheet1.Activate

Set wb = myWebBrowser.Object

With wb
    .Silent = True
    .Navigate "about:blank"
    Do While .ReadyState <> READYSTATE_COMPLETE
        Application.Wait (Now + TimeValue("0:00:01"))
    Loop
    .Document.Open "text/html"
    Do While .ReadyState <> READYSTATE_COMPLETE
        Application.Wait (Now + TimeValue("0:00:01"))
    Loop
    .Document.write html
    .Document.Close
    .Document.body.Scroll = "no"
    .Refresh
    Debug.Print .Document.body.innerHTML
End With

What is the error? Until you've loaded some content in the control there's no "body" element, so you can't set the scroll attribute... — Tim Williams, Oct 26 '13 at 00:17
Run Time Error '91': Object variable or With block variable not set — oMG, Oct 26 '13 at 00:22

Tim Williams · Accepted Answer · 2023-03-13T19:51:08.047

8

myWebBrowser.Object.Navigate "http://www.google.com"

more complete example:

Sub AddWebBrowserToWorksheet()

    Dim myWebBrowser
    Dim wb, doc, x As Long
    
    Sheet2.Activate
    Sheet1.OLEObjects(1).Delete
    
    Set myWebBrowser = Sheet1.OLEObjects.Add(ClassType:="Shell.Explorer.2", _
                       Left:=147, Top:=60.75, Width:=400, Height:=400)
                       
    Set wb = myWebBrowser.Object
    With wb
        .Navigate "about:blank"
        .Document.Open "text/html"
        For x = 1 To 100
        .Document.write "hello world<br>"
        Next x
        .Document.Close
        .Document.body.Scroll = "no"
        Debug.Print .Document.body.innerHTML
    End With
    Sheet1.Activate 'switching back to the sheet seems to 
    '               '   trigger the display of the object
    
End Sub

edited Mar 13 '23 at 19:51

answered Oct 25 '13 at 22:58

Tim Williams

154,628
8
97
125

Thank you for the reply but this didn’t work I also get errors when trying the above code `myWebBrowser.Object.Document.body.Scroll = "no" myWebBrowser.Object.Silent = True myWebBrowser.Object.Navigate ("about:blank") While myWebBrowser.Object.ReadyState <> READYSTATE_COMPLETE Application.Wait (Now + TimeValue("0:00:01")) Wend myWebBrowser.Object.Refresh` – oMG Oct 25 '13 at 23:27
@oMG - please don't add code in comments: you can update your question with your new code. – Tim Williams Oct 25 '13 at 23:38
Thank you for your help but I have a lot of problems with the above code. First you need a minimum of two sheets and change between them to refresh the webBrowser and even if that is possible with a hack (create a sheet activated, go to the one with the web browser activated, delete sheet—not pretty :-P) it will not work if the html inside needs to download code like javascripts etc. so it needs to be refreshed. – oMG Oct 26 '13 at 01:20

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

You need to pump Windows messages inside your WebBrowser.ReadyState <> READYSTATE_COMPLETE loop for this to work. Calling DoEvents/Sleep inside the loops does that, but has its own implications. Check these answers for further details and sample code:

https://stackoverflow.com/a/19019200/1768303

https://stackoverflow.com/a/19308865/1768303

edited May 23 '17 at 11:46

Community

1
1

answered Oct 26 '13 at 01:16

noseratio

59,932
34
208
486

Hi thank you for your answer but I don’t thing that this is the problem. What I found was that for some reason when a web browser is created it stays in some kind of Design Mode. If I put before the code this Sheet2.Activate Sheet1.Activate It will work. So know I’m trying to find a way to get out of the Design Mode without needing two sheets and changing between them. Does anyone have a clue> Is like I am in the fondant and I can’t drink the water :-P – oMG Oct 26 '13 at 01:42
@oMG, try [`Workbook.ToggleFormsDesign`](http://msdn.microsoft.com/en-us/library/office/ff196845.aspx) method to disable design mode. Regardless of that, you still need a message loop for `WebBrowser` to load stuff. – noseratio Oct 26 '13 at 01:53
Thank you again but that didn’t work. I don’t want to get in on out Design Mode but rather get that object from that mode. – oMG Oct 26 '13 at 02:09

Excel VBA create an embedded WebBrowser and use it

2 Answers2

Linked