Procedure to upgrade and release an Excel addin (XLA)?

[ChrisGreaves]

I have an Excel Addin Stain076.XLA stored in C:\Users\Chris074\AppData\Roaming\Microsoft\AddIns by virtue of saving the Stain076.XLS as an Addin. The Stain076.XLA loads as an Addin.
The original Stain076.XLS is saved in T:\Greaves\Products\USER\Stain, and is NOT loaded as an Addin.
When I open Excel2003 the Addin Stain076.XLA appears with its toolbar and works well.

I would like to hear from Loungers who find a need to modify the Addin; what procedures do you follow?

(1) If I decide to modify the toolbar menu or make a small cosmetic change to the VBA code, I ought not to open the Addin and edit it, for the true source is the XLS, and my changes to the XLA must ultimately be migrated across to the original XLS.(more work!)

(2) If I decide to modify and test the XLS, then I run into any one of a variety of hurdles including (I think) the one about not saving a workbook with the same name – even though the name (“Stain076”) is destined for a different folder. I have (more work!) been disabling the Addin, opening the XLS, making and testing changes, then saving the XLS as an XLA. Of course, I forget to save my original source XLS before issuing the saveAs XLA. (more work!)

(3) I can rename my save, but then must exit Excel and delete/rename workbooks or Addins through Explorer before re-loading Excel.
Of course, editing the XLS and saving it as an XLA means that I should exit Excel and re-load Excel (more work!) allowing it to collect the updated Addin.

I work mainly in Word2003, and so have not spent a great deal of time developing XLAs. I suspect I am missing something critical about upgrading XLAs; my working procedures seem clumsy and cumbersome.

If you are in the business of maintaining your own Addins, how do you go about editing and testing the source code XLS and then releasing that as an Addin XLA?

Thanks
Chris

[stuck]

I 'control' the master version of my .xlam addin using a folder structure. At the highest level it looks like this:
\addins
    \~archive
    \current
    \zDev

Underneath each of those three folders are dated folders:
        \yymmdd

The dated folder holds the actual addin and a simple text file:
            myAddin.xlam
            update.txt
The text file is a change log. NB the master version of the addin exists as a proper, .xlam, addin file, not an .xslm (or .xlsb) file

Meanwhile, the deployed / live version of the addin is in a completely separate location:
    ...\myAddin
and in Excel (365) via Tools | Options | Advanced | General section this separate location is set as the path for 'At startup, open all files in:'

When a change is required I take a copy of the \current\yymmdd folder and paste it under \zDev. Having done that the date is updated to today's date. I next append today's date to the name of the addin. I can then open the addin by double clicking on it. Doing this does give me two sets of buttons on the Ribbon but that doesn't bother me since I'm working in the VBE.

Once I'm happy with the new version I save it. I then remove the date I appended to the file name and update the change log text file. Now all I have to do is deploy the new version in a 'controlled' manner:
1) drag and drop the \yymmdd folder from underneath \current to be underneath \~archive
2) drag and drop the \yymmdd folder under \zDev that hold the new version to be underneath \current
3) take a copy of the updated .xlam file, now in \current\yymmdd, and paste it into the separate location that holds the deployed / live version.

Job done.

Hope this helps,

Ken

[ChrisGreaves]

I 'control' the master version of my .xlam addin using a folder structure. At the highest level it looks like this:

Hi Ken, and thank you. This does indeed help.
Your set of extents differ from mine, but I think the basic structure you are using is to edit (in my Excel2003 case) the XLA addin and save it as a source XLS.
As far as making copies, backups, version numbers of files, I have got that down to a Black Art, a direct result of years of shooting myself in the foot .
I will set aside time to try a new process of actions based on your suggestions and report back.
Thanks again, Chris

[Jan Karel Pieterse]

A bit late in the game, but I do it mostly like you described yourself:
- Open the xls(m) version of the add-in
- Uncheck the add-in
- Edit the xls(m)
- Save-as upping the build number in the file name (this is my now most recent version)
- Save-As, as add-in, overwriting the xla(m)

Then if there are other users involved (usually in my case) I take these steps:
- Update web page with new build number and some text explaining what's new
- Upload xlam to download folder of website
- Update a dedicated html page which just holds the build number (add-in polls this page to find out if there's an update)
- Update the zip container holding the add-in and installation instructions for new users and upload that as well.
See: https://jkp-ads.com/articles/updateanaddin.asp

[ChrisGreaves]

A bit late in the game, but I do it mostly like you described yourself:

Jan Karel, it's never too late to teach or inspire me, and you do it so well!
My tardiness is due to Thursday being the only day of the week devoted to Excel work nowadays.
Nonetheless, here is your suggestion modified, and extracted from the attached document:-
(1) I have thought of a brilliant enhancement to my utility library UX.XLA
(2) Open the current workbook version UX215.XLS in Excel.
(3) Disable (check OFF) the corresponding Addin UX.XLA. In normal life this would have been created from UX215.XLS
(4) Immediately save the current XLS with a new name, in my case UX216.XLS
(5) Make changes to UX216.XLS
(6) Test the changes.
(7) Save the tested UX216.XLS
(8) Save the tested UX216.XLS as UX.XLA
(9) Enable (check ON) the Addin
(10) Exit Excel

Although my projects have three-digit version numbers, I use my addins (and MSWord Startup libraries) without version numbers, so that I don't need to open and reset references in other applications)

Thanks again.
Chris