VBA in FrontPage

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)
    aPage.Close True
End Sub

Public Sub UpdateMenuFromXML()
    Set mySiteScanner = New SiteScanner
    mySiteScanner.Recursive = True
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
            If myTable.Id = "nbmenu" Then
                myTable.outerHTML = strNoteBookMenu
                bIsChanged = True
                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
        End If
End Sub

Here is the Macro which uses the MenuFromXML Class.

Sub MenuUpdate()
    Dim myMenu As New MenuFromXML
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.

You might also like...



Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An expert is a man who has made all the mistakes that can be made in a very narrow field” - Niels Bohr