How do you take a vba addin and make an installer?

Question

I have written a vba module that installs an addin button to excel. I would like to deploy it on many computers as easy as possible. Currently, these are my steps.

1. Save the excel as *.xlsm
2. Open the doc on the computer that I want to install
3. Save the document as an extension (*.xlam) which places it in the addin folder
4. Go to options>Add-Ins>Manage /Go>
5. Click the check box for my add in

Is there a way to automate this process? Write something in vb?

Solution 1

You can build an exe or msi installer with a tool such as Advanced Installer (the free edition). One thing you can do is copy files to a specific location and if you put it in the XLSTART folder then that add-in will automatically load for the user when starting Excel. There's a complimentary folder within the Excel program files directory, usually something like this

C:\Program Files (x86)\Microsoft Office\Office15\XLSTART

which if you drop xlam files into that folder they'll load by default too. The user specific option is

C:\Users[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART

there are PROS/CONS to both models. One is will all users have access or just the user that installs. The benefit of the user specific option is it requires limited rights for installation (no admin privileges)

Solution 2

As others have said, you can easily copy the files to the needed folder in each users Apps directory. They will then need to check the box in the Add-Ins menu, but at least the file will be there. I use this at work with some success by putting the Add-ins and .bat file on a shared drive that everybody has access to. You can then just provide a link to the .bat file which will copy the file from the shared drive to the person's computer in their Apps directory. Here's an example .bat that I use:

xcopy "\\server\share\folder\Addins\CRWScleanup.xlam" "%APPDATA%\Microsoft\AddIns\" /y

Initially I had the .bat files setup to detect and handle Win7 and WinXP because we have a mix at work, but then I realized that the folder location after %APPDATA% (which leads to a different place for XP vs Win7) is the same for each version of Windows. I.e. \Microsoft\AddIns\ %APPDATA% is a global Windows variable and its value will vary for each user (that is good).

It works brilliantly once the Add-In is installed because to update it all I have to do is put the new version on the shared drive and have the user click the link to the .bat while Excel is closed. The new version gets copied over and the user doesn't have to do anything.

Solution 3

With VBA you have to rely on Office files (Excel ones, in this case) and cannot move to executables/installation packages. You might create a program (or a macro) performing the steps you want in an automated way. But if what you want is relying on a standard installation package, which the user might execute (as usual, when installing a program), you would have to move to VB.NET.

VB.NET and VBA are not too different (well... actually, VB.NET includes many more things, but "understands" most of the VBA code) and VB.NET is quite programmer friendly; so a conversion from VBA to VB.NET wouldn't take you too long. In VB.NET you have different ways to interact with Excel; from your question, I understand that you want the Add-in alternative: it generates a custom "installation package" which, once clicked, will install the give Excel Add-in in the target computer. Relying on this option is easy: in your Visual Studio (you need a VS to work with VB.NET), open "New Project" and, within the Visual Basic Templates, select Office (, your version) and Excel Add-in.

NOTE: useful link provided my Mehow: it refers to an old VS (2008) but things haven't changed too much since then.

NOTE 2: the aforementioned suggestion is available in any fee-based VS version since the 2008 one. Not sure about the support in free versions (Express ones).

Solution 4

The easiest way would be to add some install code to the xlsm file on open.
This code could then do the following:

• Turn off the install code by changing a variable on sheet (this will stop the on open code running next time the file opens);
• Save a copy of the file as an xlam to the add-in folder;
• Activate the add-in;
• Then close the xlsm file.

All you need to do is then email the xlsm file out and ask people to open it.

The code is quite simple, but I can show if you need.

Solution 5

I researched a lot of the methods described above and on other sites, but have managed to create my own install and uninstall using Excel add-ins (*.xlam) themselves. My add-ins now install themselves the first time and I have an uninstallation script as well. Works like a charm.

I used my own variation and derivations of Ivan's Solutions': https://grishagin.com/vba/2017/01/11/automatic-excel-addin-installation.html

View more solutions

Comments

[varocarbas]

@mehow I have included your link and your reference to Express versions.

[varocarbas]

@CameronAziz if you are used to VBA, you will surely enjoy working with VB.NET & VS (much more programmer-friendly).

[Lalaland]

Can you please share your mentioned code?

[Tyler2P]

Your answer could be improved by adding more information on what the code does and how it helps the OP.

[abakum]

Look at GitHub