We came across the need to remove ribbon customisation from a macro-enabled Office file recently and spent some time researching how to do it and coming up with a neat solution which we’re sharing here.
Why do we need to do this?
Firstly, let’s explore why the need arose. When you add a ribbon customisation (customUI part) to a macro-enabled Office file such as PowerPoint (.pptm), Excel (.xlsm) or Word (.docm) which includes callback to embedded VBA code, you may want to publish the finished file as a non-macro enabled file format e.g. PowerPoint (.pptx), Excel (.xlsx) or Word (.docx). Programmatically saving a copy of the macro-enabled file causes the VBA project to be automatically deleted from the file so that part is easily taken care of. However, when you then open that non-macro enabled file, it still includes the ribbon customisation in the customUI XML part so your UI callbacks won’t find the corresponding code in the non-existent VBA project any more. This then leads to the need to remove that customUI part from the XML file that represents your Office document.
Our solution overview
Doing this manually is quite a simple task. For anyone unaware of the Office file formats, you can simply rename any XML-based Office file (the standard format since Office 2007) to .zip and then explore the contents of the file archive using Windows Explorer or any third party Zip file tool.
For a relatively simple Office file, let’s use PowerPoint as an example, you’ll then see a structure like this:
If you then open the customUI folder, you’ll see one or more of these files which define the UI customisation:
- customUI.xml : Office 2007
- customUI14.xml : Office 2010 and onwards (to 2016 as of writing this article)
To remove the customUI it’s simply a case of deleting the customUI folder from the Zip archive before renaming it back to the original Office document name, without the .zip extension.
Solution implementation in code
The above process looks straight forward but manipulating Zip files in VBA is a relatively unknown area of Windows. We’re going to use an instance of the Windows Shell application to set a reference to the Folder object and then look inside the Zip file to then manipulate it’s contents.
Here is the full code before we look at it in detail after:
Option Explicit
' To use IntelliSense when coding, change this to True and add references to:
' 1. Microsoft Shell Controls and Automation
' 2. Microsoft Scripting Runtime
' When finished coding, change back to False and remove the reference
#Const EarlyBinding = False
' ==========================================================================
' Removes the customUI folder from the specified Office file.
' Note : the _rels customUI reference is not removed as it's just a pointer.
' Simplified design for PC versions of Office only (adapt paths for Mac use)
' Inputs : FilePathAndName - the full path and file name of the source
' Office file. Can be any XML-based file for PowerPoint, Excel or
' Word e.g. .potx,.potm, .xlsx,.xlsm, .docx,.docm
' Author : Jamie Garroch of YOUpresent.co.uk
' ==========================================================================
Sub RemoveCustomUI(FilePathAndName As String)
Dim oApp As Object
#If EarlyBinding Then
Dim oShell As Shell
Dim oFSO As FileSystemObject
Dim SourcePath As FolderItem
#Else
Dim oShell As Object
Dim oFSO As Object
Dim SourcePath As Variant
#End If
Set oApp = Application
Select Case True
Case UCase(oApp.Name) Like "*POWERPOINT*": SourcePath = oApp.ActivePresentation.Path
Case UCase(oApp.Name) Like "*EXCEL*": SourcePath = oApp.ThisWorkbook.Path
Case UCase(oApp.Name) Like "*WORD*": SourcePath = oApp.ActiveDocument.Path
Case Else
MsgBox "Please modify code to support other Office apps.", vbCritical + vbOKOnly, "Office App Not Supported"
Exit Sub
End Select
If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"
' Rename the Office file as a Zip file, maintaining the source name by simply appending ".zip"
Name FilePathAndName As FilePathAndName & ".zip"
Set oShell = CreateObject("Shell.Application")
' Move the customUI folder out of the Zip archive (without fully decompressing the archive)
' Doesn't raise an error if no customUI folder found
oShell.NameSpace(SourcePath).MoveHere (FilePathAndName & ".zip\customUI")
' Delete the decompressed customUI folder from the [uncompressed] file system
Set oFSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next ' In case no customUI was present
oFSO.DeleteFolder SourcePath & "customUI"
On Error GoTo 0
' Rename the Zip file back to the source file name
Name FilePathAndName & ".zip" As FilePathAndName
' Clean up
Set oApp = Nothing
Set oShell = Nothing
Set oFSO = Nothing
End Sub
Let’s look at this code in more detail.
Firstly, if you like using IntelliSense when writing your code, you need to add references to libraries that you use beyond those set by default when you open the VBE. Since we’re using a Shell object and a File System object in this code (which each require a new library reference), it’s useful to declare these objects as their actual object types, not just ‘Object’, so that IntelliSense works as expected. This approach is called Early Binding. But there’s a downside, when you publish projects that use Early Binding, they don’t always work on other user’s machines due to library versions. That’s when declaring variables as either Object or Variant allows us to remove the library references from the project and set references to the object at runtime. This approach is called Late Binding.
This part of the code sets up our environment for early or late binding:
' To use IntelliSense when coding, change this to True and add references to: ' 1. Microsoft Shell Controls and Automation ' 2. Microsoft Scripting Runtime ' When finished coding, change back to False and remove the reference #Const EarlyBinding = False #If EarlyBinding Then Dim oShell As Shell Dim oFSO As FileSystemObject Dim SourcePath As FolderItem #Else Dim oShell As Object Dim oFSO As Object Dim SourcePath As Variant #End If
We simply toggle the compiler constant (statements starting with # are conditional compiler code lines) EarlyBinding and add/remove the dependent references as described depending on if we are in development phase or publication phase.
The next block of code allows us to determine which Office application is hosting the VBA and then we use that to find the path of the file in which the VBA is being run from:
Dim oApp As Object
Set oApp = Application
Select Case True
Case UCase(oApp.Name) Like "*POWERPOINT*": SourcePath = oApp.ActivePresentation.Path
Case UCase(oApp.Name) Like "*EXCEL*": SourcePath = oApp.ThisWorkbook.Path
Case UCase(oApp.Name) Like "*WORD*": SourcePath = oApp.ActiveDocument.Path
Case Else
MsgBox "Please modify code to support other Office apps.", vbCritical + vbOKOnly, "Office App Not Supported"
Exit Sub
End Select
If Right(SourcePath, 1) <> "\" Then SourcePath = SourcePath & "\"
We can now rename the source Office file that was passed to the subroutine as FilePathAndName:
Name FilePathAndName As FilePathAndName & ".zip"
For example, if our source file was this:
C:\Temp\MyDeck.pptx
Then when it’s be renamed, it becomes:
C:\Temp\MyDeck.pptx.zip
We now need to manipulate this zip file with Windows Explorer and we’re going to create an instance of the Shell application to do this:
Set oShell = CreateObject("Shell.Application")
Now we have a reference to this oShell application object, we can use the MoveHere method (more info on MSDN) to extract the customUI folder and its contents from the Zip file to the source folder we located earlier. Note that there is no Delete method which is why we’re moving it.
oShell.NameSpace(SourcePath).MoveHere (FilePathAndName & ".zip\customUI")
Now that the folder has been removed from the Zip archive, we need to delete it from the uncompressed part of the file system:
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.DeleteFolder SourcePath & "customUI"
But because there might not have been a customUI folder in the Zip archive, we need to handle the possibility of an error. In this instance, “handling” any potential error is as simple as ignoring it because we don’t care if there was customUI folder. We use the On Error Resume Next statement to ignore any error from that point until we reset it with the On Error Goto 0 statement:
Set oFSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next ' In case no customUI was present
oFSO.DeleteFolder SourcePath & "customUI"
On Error GoTo 0
With the customUI folder now eradicated, we can rename the archive back to its original file name:
Name FilePathAndName & ".zip" As FilePathAndName
Taking our our example file name above, C:\Temp\MyDeck.pptx.zip reverts to C:\Temp\MyDeck.pptx
The final step just clears all references to the objects we used, removing them from memory:
Set oApp = Nothing Set oShell = Nothing Set oFSO = Nothing
Conclusion
This article explained how to remove a specific folder and file(s) from an Office file but since it deals with Zip file manipulation, the same technique can be used to modify any other Zip file.