NorthWall Applications

Custom Applications, Automated Solutions

Data migration, collection and organization

 

Automating an Excel Workbook to Install an Add-In to Excel

Using VBA to add an Add-In

I was assigned a task to update an outdated Excel Add-In VBA project so the office could upgrade their version of Microsoft Office. After updating the code, part of my job was implementing a strategy to batch release the updated Add-In. My client requested a way of doing this so new employees could use the Add-In application without having security prompts and without changing the security settings. This is the Enable Editing warning that pops up each time the user opens a new workbook from the new Add-In Toolbar:

Batch Release an Add-In Update

One way to batch release the application update without changing security settings is to request they add it to their trusted files. This will put the file in a special trusted folder and suppress the Enable Editing warning message thereafter. Another way is to automate this choice and have the Add-In Ribbon Toolbar be able to run macros for an application without any user input on any security setting. For Excel 2007 and later, each excel user has a special Add-Ins folder. This is the file path that opens when hitting browse in Excel-Addins. In Windows 10 the file folder path looks like this: C:\Users\UserName\AppData\Roaming\Microsoft\AddIns


If you are unsure of your path you can use the following code: 

Sub FindAddinsFolder()
	MsgBox Application.UserLibraryPath
End Sub

Some Things to Remember about Excel Add-Ins


  • An Add-In file is the same as a .xlsm file but is saved as .xlam
  • .xlam files differ in that they do not have visible worksheets for the user
  • An Add-In is a VBA project that contains the code to utilize an application

    • VBA Code to automate this process is in the Workbook Module of the .XLSM file

    The Workbook Module controls the code that will trigger upon a predetermined event by the workbook. This is common for running code when a workbook opens or closes. We want the .xlsm file to check to see if the user has the Add-In and if they don’t, prompt the user to install the Add-In. From there the .xlsm is no longer needed and can be closed. The next time excel is opened, your Add-In will be displayed as an available Add-In in the Excel-Addins menu. Activating the Add-In, in my case, turns on the custom toolbar tab for the application.

    In the workbook module of the macro file you are turning into an add-in, enter the following code: 

    OPTION EXPLICIT
		DIM INSTALLEDPROPERLY AS BOOLEAN
		SUB WORKBOOK_OPEN()
		DIM AI AS ADDIN, NEWAI AS ADDIN
		DIM M AS STRING
		DIM ANS AS LONG

		'CHECK VERSION AND CLOSE WORKBOOK IF VERSION ISN’T NEWER THAN 2007
		IF VAL(APPLICATION.VERSION) < 12 THEN
			MSGBOX "THIS WORKS ONLY WITH EXCEL 2007 OR LATER"
			THISWORKBOOK.CLOSE
		END IF

		'WAS JUST INSTALLED USING THE ADD-INS DIALOG BOX?
		IF INSTALLEDPROPERLY THEN EXIT SUB

		‘IF THIS ADD-IN IS ALREADY ACTIVE, EXIT SUB
		FOR EACH AI IN ADDINS
			IF AI.NAME = LEFT(THISWORKBOOK.NAME, LEN(THISWORKBOOK.NAME) - 5) & ".XLAM" THEN 'REPLACE THE .XLSM WITH .XLAM THEN
				IF AI.INSTALLED THEN EXIT SUB
			END IF
		NEXT AI

		'ADDIN NOT FOUND, PROMPT USER TO INSTALL
		M = "INSTALL NEW ADDIN?" & VBNEWLINE & "YES - INSTALL ADD-IN. "
		M = M & VBNEWLINE & "NO - OPEN IT, BUT DON'T INSTALL IT. "
		M = M & VBNEWLINE & "CANCEL - CLOSE THIS WORKBOOK, CLOSE THE ADD-IN. I'M OUTA HERE."
		ANS = MSGBOX(M, VBQUESTION + VBYESNOCANCEL, THISWORKBOOK.NAME)

		SELECT CASE ANS
		CASE VBYES
				CALL SAVEWORKBOOKTOADDINSCOLLECTION
				MSGBOX "SUCCESS, YOU CAN CLOSE THIS WORKBOOK. “ & VBNEWLINE & “WHEN YOU ARE READY TO USE THE ADD-IN, ACTIVATE THE ADD-IN BY SELECTING IT IN EXCEL ADD-INS"
		CASE VBNO
				'NO ACTION
		 CASE VBCANCEL
				THISWORKBOOK.CLOSE
		END SELECT
		END SUB

		PRIVATE SUB WORKBOOK_ADDININSTALL()
		‘CHANGE THE STATUS AFTER WORKBOOK HAS INSTALLED ITSELF AS AN ADD-IN
			 INSTALLEDPROPERLY = TRUE
		END SUB

    Each time the workbook is opened, the procedure to check for the updated Add-In will occur. If the Add-In is not found, the user is asked to install it with a command prompt. If the user selects yes then the sub routine SaveWorkbookToAddinsCollection will be triggered. This macro dynamically installs the .xlsm file running the code as a .xlam file into the trusted Add-Ins folder. This macro can go into the workbook module as well. 

    Private Sub SaveWorkbookToAddinsCollection()
Dim NewAddin As AddIn
Application.DisplayAlerts = False
‘SET INSTALL LOCATION TO THE CURRENT USERS ENVIRONMENT TRUSTED ADD IN FILE FOLDER
Dim UserPath As String: UserPath = "C:\Users\"
Dim ActiveUser As String: ActiveUser = Environ("username") & "\"
Dim AddInsPath As String
AddInsPath = & _
"AppData\Roaming\Microsoft\AddIns\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5) & ".xlam" 'Replace the .xlsm with .xlam
ThisWorkbook.SaveAs Filename:=UserPath & ActiveUser & AddInsPath, FileFormat:=xlOpenXMLAddIn
Set NewAi = Application.AddIns.Add(Filename:=UserPath & ActiveUser & AddInsPath)
Application.DisplayAlerts = True
End Sub

    Successful Installation Walk-Through

    In my project, the Add-In was an application called JEM that is controlled via a new Ribbon tab named JEM in the ribbon toolbar.

    Once the workbook is opened, the user will see a prompt for installing the updated Add-In.

    After the installation, the user can activate the new Add-In from the Excel AddIns Menu.


    Workbooks will display the newly updated ribbon tab after pressing ok and will not receive pesky Excel messages to run the application’s code.