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

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 2

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.