Coding Hurts, But the Ribbon Breaks Your Heart

Normal

0

false

false

false

false

EN-US

X-NONE

X-NONE

MicrosoftInternetExplorer4

I was
building a ribbon this week (yes, I can’t get enough of them), when I cam across
what is a really stupid problem.

My
application is building a cascading dynamic menu , a list of all files in the
selected directory, and all sub-directories in the parent directory are added
as sub-dynamic menus (creating the cascade when they are clicked).

No sweat I
thought, just use FSO to get the files and the folders, and string the XML
together dynamically … I’ve done it a hundred times, it will be a breeze.

Problem was
that on testing, I kept getting a rather unhelpful message like the following

It is most
unhelpful as the XML is being built dynamically, so it is not easy to see the
string (you need to debug  the code and output
the string!).

This wasn’t
happening every time, just sometimes. Because it was in an application with
other functionality, I decided to build a test workbook focussing
specifically on the problem.

I built a
workbook with a single dynamic menu control. That control invoked code that
queried for a directory, and then builds the XML for the files and the
first-level of sub-directories.

One thing
to note, the crux of the problem, is that I use the tag property to hold the
full file or directory path, as this is intended to be a technique to open
these files, or trigger another dynamic list for the sub-directory.

It turned
out to be that when the directory name includes an & (ampersand) character,
and we load the tag property with that value, the XML complains.

It also
turns out that you cannot load the tag attribute with such a directory name in
the CustomUI editor.

I know I
can replace these characters with some other unique string before loading, but
then I would need to decipher it before using it. I feel madness rushing up on
me.

I am sure
there is some excuse, I mean valid reason, that is all down to the way that XML
handles these characters, but … it is just text and the tag attribute is just a
string value; an & is perfectly allowable within a directory name, so this
strikes me as being nuts.

This is the
custom XML and code in my test workbook

<customUI xmlns=“http://schemas.microsoft.com/office/2006/01/customui“>

   
<ribbon>

        <tabs>

            <tab idMso=“TabHome“>

                <group id=“rxgrpInvalid“ label=“Invalid Chars“>

 

                    <dynamicMenu id=“rxdynInvalid“

                                 label=“Dir List“

                                 size=“large“

                                 imageMso=“SmartArtOrganizationChartRightHanging“

                                 getContent=“rxGetContent“ />

 

                    <button id=“rxbtnRefresh“

                            label=“Refresh“

                            imageMso=“HighImportance“

                            size=“large“

                            onAction=“rxGetButtonAction““/>

                 </group>

            </tab>

        </tabs>

   
</ribbon>

</customUI>

 

Option
Explicit

Private rxIRibbonUI As IRibbonUI

Global cntDiv As Long

Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
    Set rxIRibbonUI = ribbon
End Sub

Public Sub rxGetContent(control As IRibbonControl, ByRef Content)
Dim mpDialog As FileDialog
Dim FSO As Object
Dim TemplateFolder As Object
Dim XML As String

    Stop
    Set mpDialog =
Application.FileDialog(FileDialogType:=msoFileDialogFolderPicker)
    With mpDialog
       
        .AllowMultiSelect = False
        If .Show = -1 Then

            Set FSO =
CreateObject(“Scripting.FileSystemObject”)
            Set TemplateFolder =
FSO.GetFolder(.SelectedItems(1))

            XML = “<menu
xmlns=””http://schemas.microsoft.com/office/2006/01/customui””>”
& vbNewLine
            XML = XML &
XMLFiles(TemplateFolder, True)
            Content = XML &
“</menu>”

            Set TemplateFolder = Nothing
            Set FSO = Nothing
        End If
    End With

    Set mpDialog = Nothing

End Sub

Public Sub rxGetButtonAction(control As IRibbonControl)
    Select Case control.Id

        Case “rxbtnRefresh”
            rxIRibbonUI.InvalidateControl
(“rxdynInvalid”)

        Case Else
            If Left$(control.Id, 9) =
“rxbtnFile” Then Workbooks.Open (control.Tag)
    End Select
End Sub

Public Function XMLFiles( _
    ByRef Folder As Object, _
    ByVal TopMenu As Boolean) As String
Dim thisXML As String
Dim subFolder As Object
Dim file As Object
Dim cntFiles As Long

    For Each file In Folder.Files

        ‘Check if file is a temporary
file
        If Not Left(file.Name, 2) =
“~$” Then

           Select Case True

                Case file.Type Like
“*Microsoft*Excel*”

                    cntFiles = cntFiles +
1
                    thisXML = thisXML
& “<button ” & vbNewLine & _
                              “    id=””rxbtnFile” & cntDiv
& cntFiles & “”” ” & vbNewLine & _
                              “    label=””” & file.Name
& “”” ” & vbNewLine & _
                              “   
imageMso=””FileSaveAsExcel97_2003″” ” &
vbNewLine & _
                              “   
onAction=””rxGetButtonAction”” ” &
vbNewLine & _
                              “    tag=””” & file.Path
& “””/>” & vbNewLine
           End Select
        End If
    Next file

    If cntFiles = 0 Then

        ‘Create a “No
Templates” button if no applicable files found
        thisXML = thisXML &
“<button ” & vbNewLine & _
                  “    id=””rxbtnNoFiles” &
cntDiv & “0”” ” & vbNewLine & _
                  “    label=””No Files
Found””/>” & vbNewLine
    End If

    For Each subFolder In
Folder.subFolders

        cntDiv = cntDiv + 1
    ‘   
thisXML = thisXML & “<menuSeparator id=””div”
& cntDiv & “””/> ” & vbNewLine & _
                  “<dynamicMenu
” & vbNewLine & _
                  “   
id=””rxdmnuSub” & ClearId(subFolder.Name) &
“”” ” & vbNewLine & _
                  “    label=””” &
subFolder.Name & “”” ” & vbNewLine & _
                  “    imageMso=””FileOpen””
” & vbNewLine & _
                  “    getContent=””rxGetContent””
” & vbNewLine & _
                  “    tag=””” & subFolder.Path
& “””/>” & vbNewLine
    Next subfolder

    XMLFiles = thisXML

End Function

Private Function ClearId(ByVal Id As String) As String
Dim RegEx As Object
    Set RegEx =
CreateObject(“VBScript.RegExp”)
    RegEx.Pattern = “[\
\\/:\*\?””<>\|]”
    RegEx.Global = True
    ClearId = RegEx.Replace(Id,
“”)
    Set RegEx = Nothing
End Function