What is the best way to package and distribute an Excel application.

Question

I've writen an Excel-based, database reporting tool. Currentely, all the VBA code is associated with a single XLS file. The user generates the report by clicking a button on the toolbar. Unfortunately, unless the user has saved the file under another file name, all the reported data gets wiped-out.

When I have created similar tools in Word, I can put all the code in a template (.dot) file and call it from there. If I put the template file in the Office startup folder, it will launch everytime I start Word. Is there a similar way, to package and distribute my code in Excel? I've tried using Add-ins, but I didn't find a way to call the code from the application window.

Answer 1

You can modify the user's personal.xls file, stored in the excel startup directory (varies between Office versions). If you have lots of users though, that can be fiddly.

An alternative way to get over your problem is to store the macro in a template (.xlt) file. Then when the users opens it they can't save it back over the original file, but have to specify a new filename to save it as. The disadvantage of this method is that you then get multiple copies of your original code all over the place with each saved file. If you modify the original .xlt and someone reruns the old macro in a previously-saved .xls file then things can get out of step.

Answer 2

Simply move your code into an Excel Addin (XLA) - this gets loaded at startup (assuming it's in the %AppData%\Microsoft\Excel\XLSTART folder) but if it's a addin, not a workbook, then only your macros and defined startup functions will be loaded.

If the functions depend on a spreadsheet itself, then you might want to use a combination of templates and addins.

I'm distributing part of an application like this, we have addins for Word, Excel and Powerpoint (XLA, PPA, DOT) and also Office 2007 'ribbon' versions (DOTM, XLAM and PPAM)

The addin startup code creates toolbar buttons if they're not found, this means in any workbook/document/etc they can simply hit the toolbar button to run our code (we have two action buttons and one button that displays a settings dialog)

Templates aren't really the way to go for VBA code, Addins are definitely the way to go...

So to load the toolbars on startup we're using something like.. (checking to see if toolbar exists though - code will run for each worksheet that is opened, but toolbars are persistent for the user session)

Public Sub Workbook_Open()
     ' startup code / add toolbar / load saved settings, etc.
End Sub

hope that helps :)

Answer 3

Here is a good guide to custom menues

Answer 4

I always use an Add-in(xla)/Template(xlt) combination. Your add-in creates the menu (or other UI entry points) and loads templates as needed. It also write data that you want to persist to a database (Access, SQLServer, text file, or even an xls file).

The first rule is to keep your code separate from your data. Then, if you later have bug fixes or other code changes, you can send a new add-in and all of their templates and databases aren't affected.

Answer 5

Have you looked into ClickOnce deploying the Excel file?