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


 

5 thoughts on “Coding Hurts, But the Ribbon Breaks Your Heart”

  1. Not having done any XML coding, I have an ignorant question: why

    rather than

    whatever

    ? Are there still be problems with special chars between start and end tags?

  2. Hi Harlan,

    The dynamicMenu is a “special” (for lack of a better term) element that needs to be passed all of it’s XML the way Bob specified. At runtime it is executed and essentially turned into something like this:


    Why? You’d have to ask MS.

    Bob, I’ve run into this issue as well. I believe that if you replace & with & it should work. The challenge is that the & sign is a symbol to XML to interpret something.

    Iirc, I’ve also had to substitute the | for \ on occassion as well. It’s a pain as you have to encode/decode the path, and can end up running the risk that something doesn’t decode properly.

  3. Hi Bob,

    You may adapt this to your needs, I used it to create valid url’s, which seem to have similar problems:

    Const sCriticalchars As String = “$&+,/:;=?@ “”<>#%{}|\^~[]`”
    Const sReplacementCodes As String = “%24,%26,%2B,%2C,%2F,%3A,%3B,%3D,%3F,%40,%20,%22,%3C,%3E,%23,%25,%7B,%7D,%7C,%5C,%5E,%7E,%5B,%5D,%60″

    Function MakeProperData(sData As String) As String
    Dim lCount As Long
    Dim vReplacementCodes As Variant
    Dim sNewData As String
    vReplacementCodes = Split(sReplacementCodes, “,”)
    sNewData = “”
    For lCount = 1 To Len(sData)
    If InStr(sCriticalchars, Mid(sData, lCount, 1)) > 0 Then
    sNewData = sNewData & vReplacementCodes(InStr(sCriticalchars, Mid(sData, lCount, 1)) – 1)
    Else
    sNewData = sNewData & Mid(sData, lCount, 1)
    End If
    Next
    MakeProperData = sNewData
    End Function

    Function MakeProperDataReverse(sData As String) As String
    Dim lCount As Long
    Dim vReplacementCodes As Variant
    Dim sNewData As String
    vReplacementCodes = Split(sReplacementCodes, “,”)
    sNewData = sData
    For lCount = 0 To UBound(vReplacementCodes)
    sNewData = Replace(sNewData, vReplacementCodes(lCount), Mid(sCriticalchars, lCount + 1, 1))
    Next
    MakeProperDataReverse = sNewData
    End Function

  4. Ken,

    I read Harlan’s question as much more fundamental than that. I think he was asking why does some stuff get declared as attributes, and some as content.

    For instance, instead of

    label="Dir List
    size="large”
    imageMso="SmartArtOrganizationChartRightHanging"
    getContent="rxGetContent">

    Where everything is declared as an attribute, why not



    large
    SmartArtOrganizationChartRightHanging
    rxGetContent

    I must admit I have always struggled with this ever since I first came across XML, and indeed early on tried to make everything content and came a real cropper.

    I came to the conclusion that each XML element only had one content item, so everything else was declared as attributes, but then you look at MS’ implementation, and the dynamicMenu and button elements have no content! I admit, I don’t get it fully.

    I agree with your conclusion on encode/decode though, that seems the only solution, but you have to know which are problemmatical. But I still have to ask why. The & is part of textual content, not any of the identifying element, so XML should treat it as text. XML is supposed to make it simple to share a textual data structure, this is anything but easy.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>