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
Not having done any XML coding, I have an ignorant question: why
rather than
? Are there still be problems with special chars between start and end tags?
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.
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
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
Where everything is declared as an attribute, why not
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.
Bob
Are you coming up against some URL encoding issue?
Here is another encoding routine to choose from:
http://stackoverflow.com/questions/218181/how-can-i-url-encode-a-string-in-excel-vba