Problem with browser control in VBA form

sw239 · June 23rd, 2008, 11:22 AM

Hi Everyone

I have some data in XML format in Excel 2000 that I want to display in a VBA form using a web browser control. I apply an XSL transform to convert the XML into the XHTML format I want, complete with an embedded CSS stylesheet.

However, when the output file is viewed in the form the styles have not been applied and everything adopts the default formatting.

For testing purposes I have my XML in cell A1 of my spreadsheet and my XSL in cell A2. I have a form containing a web browser control called wbTest. When I open the form I initialise the web browser by doing the following:

Code:

Private Sub UserForm_Activate()
    Me.wbTest.Navigate2 "about:blank"
End Sub

When the browser is complete I populate it with the result of the transformation by doing the following:

Code:

Private Sub wbTest_DocumentComplete(ByVal pDisp As Object, URL As Variant)
    Dim xXML As DOMDocument
    Dim xXSL As DOMDocument
    Dim sXML As String, sXSL As String, sOutputString As String

    Set xXML = New DOMDocument
    Set xXSL = New DOMDocument
    sXML = Worksheets("Sheet1").Range("A1").Value
    sXSL = Worksheets("Sheet1").Range("A2").Value

    If xXML.loadXML(sXML) And xXSL.loadXML(sXSL) Then
        MyString = xXML.transformNode(xXSL)
        Me.wbTest.Document.body.innerHTML = sOutputString 
    End If
End Sub

I know the resulting XHTML is fine because when I capture the value of sOutputString and open it in a normal browser it looks fine. And when I change the XSL so it doesn't use CSS but adds a style attribute to each tag it looks fine also.

Any idea what I'm doing wrong?

Thanks

sw239

cheekybuddha · July 3rd, 2008, 11:06 AM

Hi,

I found your post while looking for an answer to the same problem today. I don't know whether you're still needing assistance.

I found the solution here:http://www.vbforums.com/showthread.php?t=384076

Instead of:

    Me.wbTest.Document.body.innerHTML = sOutputString

Try:

    With Me.wbTest
        .Navigate "about:blank"
        Do While .ReadyState <> READYSTATE_COMPLETE ' loaded (=4)
            DoEvents
        Loop
        .Document.Write sOutputString
        Do While .ReadyState <> READYSTATE_COMPLETE ' loaded
            DoEvents
        Loop
    End With

I'm guessing "MyString" should be "sOutputString" as you didn't declare it anywhere.

hth,

d