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
– Tim Williams, Commented Oct 26, 2013 at 0:17
Run Time Error '91': Object variable or With block variable not set — oMG
– oMG, Commented Oct 26, 2013 at 0:22

Tim Williams · Accepted Answer · 2023-03-13 19:51:08Z

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, 2023 at 19:51

answered Oct 25, 2013 at 22:58

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

oMG Over a year ago

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

Tim Williams Over a year ago

@oMG - please don't add code in comments: you can update your question with your new code.

oMG Over a year ago

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.

Community · Accepted Answer · 2017-05-23 11:46:52Z

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, 2017 at 11:46

CommunityBot

11 silver badge

answered Oct 26, 2013 at 1:16

noseratio

62.1k37 gold badges226 silver badges509 bronze badges

3 Comments

oMG Over a year ago

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

noseratio Over a year ago

@oMG, try Workbook.ToggleFormsDesign method to disable design mode. Regardless of that, you still need a message loop for WebBrowser to load stuff.

oMG Over a year ago

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.

Collectives™ on Stack Overflow

Excel VBA create an embedded WebBrowser and use it

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related