Using MSXML in VBA Macros

I created XML files for my menus and an XSL file to transform them into HTML.  Here is the code from my MenuFromXML Class.

Private WithEvents mySiteScanner As SiteScanner
Private strMainMenu As String
Private strNoteBookMenu As String
Private strWroxMenu As String

Private Sub mySiteScanner_WebPage(aPage As PageWindow)
    InsertMenu
    aPage.Close True
End Sub

Public Sub UpdateMenuFromXML()
    GetMenus
    Set mySiteScanner = New SiteScanner
    mySiteScanner.Recursive = True
    mySiteScanner.EnumPageWindows
End Sub

Private Sub GetMenus()
    Dim source As MSXML2.DOMDocument30
    Set source = New MSXML2.DOMDocument30
    Dim style As MSXML2.DOMDocument30
    Set style = New MSXML2.DOMDocument30
    
    style.Load "c:\web3\spider\_xml\menu3.xsl"
    
    source.Load "c:\web3\spider\_xml\mmenu.xml"
    strMainMenu = source.transformNode(style.documentElement)
    
    source.Load "c:\web3\spider\_xml\nbmenu.xml"
    strNoteBookMenu = source.transformNode(style.documentElement)
    
    source.Load "c:\web3\spider\_xml\wmenu.xml"
    strWroxMenu = source.transformNode(style.documentElement)
End Sub

Private Sub InsertMenu()
    Dim fpDoc As IHTMLDocument2
    Set fpDoc = ActiveDocument
    Dim myTable As IHTMLTable
    Dim colTables As IHTMLElementCollection
    Set colTables = fpDoc.all.tags("table")
    Dim sepDivs As IHTMLElementCollection
    Dim bIsChanged As Boolean
    For Each myTable In colTables
        bIsChanged = False
        If myTable.Id = "menu" Or myTable.Id = "mmenu" Then
            myTable.outerHTML = strMainMenu
            bIsChanged = True
        Else
            If myTable.Id = "nbmenu" Then
                myTable.outerHTML = strNoteBookMenu
                bIsChanged = True
            Else
                If myTable.Id = "wmenu" Then
                    myTable.outerHTML = strWroxMenu
                    bIsChanged = True
                End If
            End If
        End If
        If bIsChanged Then
            Set sepDivs = fpDoc.all.tags("div")
            Dim myDiv As IHTMLDivElement
            Dim n As Integer
            n = sepDivs.Length - 1
            For j = n To 0 Step -1
                Set myDiv = sepDivs(j)
                If myDiv.Id = "separator" Then
                    myDiv.outerHTML = "<hr>"
                End If
            Next
        End If
    Next
End Sub

Here is the Macro which uses the MenuFromXML Class.

Sub MenuUpdate()
    Dim myMenu As New MenuFromXML
    myMenu.UpdateMenuFromXML
End Sub

One problem I encountered was using <hr></hr> in my XSL file.  This would cause the VBA to fail when executing the line myTable.outerHTML = strMainMenu.  It looks like FrontPage considers <hr></hr> to be invalid HTML.  You can't just put <hr> in your XSL file, because it would not be a well formed document and the parser would flag an error.  My solution for now is to use a <div id="separator"></div> to my XSL file.  In the VBA, I replace this div with an <hr> tag.  The VBA must replace the <div> tags in the collection in reverse order, because they will disappear as the change is made, changing the contents of the collection.  Using the normal For Each construct to work through the collection will cause the change to be applied to every other member of the collection.

Some improvements for the future:  The section of code which parses the XML files and transforms them, needs to be more generic.  The file names should be obtained from a configuration file, written in XML.  I should go back to using <hr></hr> in the XSL file, and remove the </hr> from the string before assigning it to myTable.outerHTML.  This would make the XML/XSL more usable in other environments.  All workarounds for FrontPage limitations should be kept in the VBA Macros and not affect the nature of the XML/XSL files.  It is also time to start using XSLT.