Switch between an add-in and its source file

Example files with this article:
  • Switch between an add-in and its source file
  • Introduction

    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

    My code

    Below I will present you my 4 timesaving routines:

    Const Addin_FileName As String = "Menu_Wim.xlsm"
    
    
    ' Wim Gielis ' http://www.wimgielis.com
    ''''' ' Procedures to switch between a file and its addin counterpart ' 04/11/16 '''''
    Sub Addin__SAVE_AS()
    ''''' ' PURPOSE: ' - save the active workbook as an addin ' - the extension is "preserved": xls ==> xla, xlx/xlsm ==> xlam '''''
    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_INSTALLED
    End Sub
    Sub Addin_INSTALLED()
    ''''' ' PURPOSE: ' - install the add-in ' - close the base xlsm file '''''
    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.Add
    End Sub
    Sub Addin_UNINSTALLED()
    ''''' ' PURPOSE: ' - uninstall the add-in ' - open the base xlsm file '''''
    '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 0
    End Sub
    Sub Addin_TOGGLE_VISIBILITY()
    ''''' ' PURPOSE: ' - allow the addin to be visible '''''
    '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 0
    End Sub
    Sub Addin_NO_ADDIN()
    ''''' ' PURPOSE: ' - uninstall the add-in ' - close the base xlsm file '''''
    '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 0
    End 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!




    Homepage

    Section contents

    About Wim

    Wim Gielis is a Business Intelligence consultant and Excel expert

    Other links