Switch between an add-in and its source file
- Nov. 4, 2016
|Example files with this article:|
Besides all of my activities at Excel message boards and this very website, I am also working on my own ribbon with frequently used macros. You know: a macro to quickly paste values a range of cells, a macro to unhide all hidden sheets, etc. The macros and icons in the ribbon are created in an Excel file with extension "*.xlsm". After that I save the file with extension "*.xlam" to turn it into an add-in.
Well, going back and forth between both files is not always straightforward, nor fast. That's why in my Personal.xlsb (personal macro workbook) I also added five procedures to switch between the xlsm version (mostly for development) en the xlam version (mostly for testing and using):
- Addin_SAVE_AS: to save the xlsm as xlam, the xlsm version is not opened anymore
- Addin_INSTALLED: to install the xlam version, the xlsm version is not opened anymore
- Addin_NO_ADDIN: to de-install the xlam version and the xlsm version is also not opened anymore
- Addin_UNINSTALLED: to de-install the xlam version, the xlsm version will be opened
- Addin_TOGGLE_VISIBILITY: to make the xlam version visible or invisible
Below I will present you my 4 timesaving routines:
Const Addin_FileName As String = "Menu_Wim.xlsm"' Wim Gielis ' http://www.wimgielis.comSub Addin__SAVE_AS()On Error Resume Next Dim o As Object Dim sFileName_Addin As String Set o = CreateObject("scripting.filesystemobject") Application.DisplayAlerts = False With ActiveWorkbook If .Name <> Addin_FileName Then MsgBox "Wrong file to be saved": Exit Sub .Save Select Case o.GetExtensionName(.FullName) Case "xls" sExtension = "xla" lExtension = 18 Case "xlsx", "xlsm" sExtension = "xlam" lExtension = 55 Case Else lExtension = 0 End Select sFileName_Addin = Application.UserLibraryPath & o.GetBaseName(.FullName) & "." & sExtension If CDbl(CDate(FileDateTime(.FullName))) < CDbl(CDate(FileDateTime(sFileName_Addin))) Then 'the addin file is more recent than the source file If MsgBox("The addin file is more recent than the source file. Do you want to CONTINUE ?", vbYesNoCancel) <> vbYes Then GoTo LastSteps End If End If Addin_UNINSTALLED '.SaveAs Filename:="C:\Users\WGielis\AppData\Roaming\Microsoft\AddIns\Menu_Wim.xlam", FileFormat:=xlOpenXMLAddIn, CreateBackup:=False .SaveAs Filename:=sFileName_Addin, FileFormat:=lExtension, CreateBackup:=False End With LastSteps: Application.DisplayAlerts = True On Error GoTo 0 Addin_INSTALLEDEnd SubSub Addin_INSTALLED()On Error Resume Next 'Workbooks("Menu_Wim.xlsm").Close True Workbooks(Addin_FileName).Close True On Error GoTo 0 'With Addins("Menu_Wim") With AddIns(CreateObject("Scripting.FileSystemObject").GetBaseName(Application.UserLibraryPath & Addin_FileName)) If Not .Installed Then .Installed = True End With If Workbooks.Count <= 1 Then Workbooks.AddEnd SubSub Addin_UNINSTALLED()'With Addins("Menu_Wim") With AddIns(CreateObject("Scripting.FileSystemObject").GetBaseName(Application.UserLibraryPath & Addin_FileName)) If .Installed Then .Installed = False End With On Error Resume Next 'If Workbooks("Menu_Wim.xlsm") Is Nothing Then 'Workbooks.Open "C:\Users\WGielis\AppData\Roaming\Microsoft\AddIns\Menu_Wim.xlsm" 'End If If Workbooks(Addin_FileName) Is Nothing Then Workbooks.Open Application.UserLibraryPath & Addin_FileName End If On Error GoTo 0End SubSub Addin_TOGGLE_VISIBILITY()'Change the .IsAddin property On Error Resume Next 'With Workbooks(Menu_Wim.xlam) With Workbooks(AddIns(CreateObject("Scripting.FileSystemObject").GetBaseName(Application.UserLibraryPath & Addin_FileName)).Name) .IsAddin = Not .IsAddin End With On Error GoTo 0End SubSub Addin_NO_ADDIN()'With Addins("Menu_Wim") With AddIns(CreateObject("Scripting.FileSystemObject").GetBaseName(Application.UserLibraryPath & Addin_FileName)) If .Installed Then .Installed = False End With On Error Resume Next 'If Not Workbooks("Menu_Wim.xlsm") Is Nothing Then ' Workbooks("Menu_Wim.xlsm").Close 'End If If Not Workbooks(Addin_FileName) Is Nothing Then Workbooks(Addin_FileName).Close End If On Error GoTo 0End Sub
It does not make a lot of sense to discuss all code, those of you who will use this and have knowledge about addins, will understand it I am sure. In the code I show you the real paths to the files, that is more helpful than the variant with code. Do not miss out on the constant called Addin_FileName at the top of the code module ("Menu_Wim.xlsm"). Lastly, I wanted to add that both files (xlsm and xlam) are stored in the default folder of addins.
Hopefully the codes will help you to save time, as well as avoid the situation where you are working in the xlam version and overwrite it by accident with an older xlsm version.
As a side note, should you want to play around with the ribbon and create your own menus, you can use the Custom UI Editor For Microsoft Office. Have a look at these pages by Ron De Bruin for a lot of useful information and examples.
Take the advantage of these codes, just as I do for some time now!