wimgielis.com

Switch between an add-in and its source file

• Nov. 4, 2016

	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, macros to manipulate pivot tables, 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) and 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
' https://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!

• « Previous Excel article
• Next Excel article »