PDA

View Full Version : Solved: Problem auto-installing VBA add-in



JWhite
06-19-2008, 09:03 AM
I hesitate to post this because I'm beginning to doubt my sanity.

I'm developing an Excel add-in which will be distributed to a large number of users and updated regularly so I was very pleased to find John Skewes' code (Thanks, John!) on VBA Express for auto-installing addins. I had to make a few minor changes because some things seem to work a little differently in Excel 2007 but I did a dozen or so installs and re-installs before I started having problems.

My problem now is that about 1 out of 4 re-installs fail. I'm developing in Windows XP SP2 with a completely updated version of Office Enterprise 2007.

I do most of my test installs on a Vista machine (with the same version of Office) but when it fails on Vista I then try it on XP and it fails there, too - consistently. The message I'm getting on Vista is "Excel cannot complete this task with available resources. Choose less data or close other applications." When I click OK I get a VBA error. The failure is occurring when I do the SAVEAS an add-in. However, the problem is not in the VBA SAVEAS instruction, as you will see.

As I said, MOST OF THE TIME the re-install works. I start with saving the XLAM file on my development machine as an XLSM file. When I open that file on another machine it asks if I want to install an update and then it asks me if I want to overwrite the existing file. No problem, usually.

Here's the weird part. If I respond NO to the re-install, I can then install it manually with no problem, i. e., I uncheck the add-in in Excel Options, delete the existing add-in manually and then manually save the file as an add-in. And it functions perfectly. In fact, if I uncheck the add-in and delete the file, I can run the auto-install and it installs the add-in with no problem. Only the re-install has a problem.

I thought that maybe something in the Reinstall code was causing a problem so I stopped the execution of the code after responding YES to the install and BEFORE it went into the Re-install routine and tried to manually install it at that time. When I manually did the SAVEAS I got the same "Excel cannot complete ..." error. So somehow the file had become corrupted before it even got into the Re-install - but nothing had happened yet! I even tried commenting out the first question so it went straight into the re-install and it still failed.

I don't expect anyone to have an answer to this. Right now when I'm ready to send out an update I test the re-install. If it fails, I can make ANY change to my add-in (like adding a blank comment line), re-save it, and the re-install works. Like I said, I'm doubting my sanity.

This is the WORKBOOK OPEN code. Since the workbook opens as an XLSM file it bypasses the upper code. I'm not posting the Re-install code because the problem occurs before it gets there. Keep in mind that if I respond NO I can install the add-in manually. If I respond YES and stop the execution at the next instruction the manual install fails - BELIEVE IT OR NOT!


Private Sub Workbook_Open()
'---------------------------------------------------------------------
' Purpose of sub: Call for installation as an addin if not installed
'---------------------------------------------------------------------
'
Dim sAddinTitle As String, sAddinName As String
Dim sXlamName As String
'
sAddinTitle = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
sXlamName = sAddinTitle & ".xlam"
' If this workbook is functioning as an add-in, exit - otherwise, install the add-in
If ThisWorkbook.Name = sXlamName Then
' Delete and re-add the Menu short-cuts
AddMenuShortCut
' Load configuration data
GetConfig
' Initialize Application-wide Event trapping
Exit Sub
End If
' This is an XLSM workbook, ready to be installed as an add-in
sAddinName = sAddinTitle & ".xlam"
'
'ask if user wants to install now
If MsgBox("Install " & sAddinTitle & _
" as an add-in?", vbYesNo, _
"Install?") = vbNo Then Exit Sub
'check the addin's not already installed in UserLibraryPath
If Dir(Application.UserLibraryPath & sAddinName) = Empty Then
Run "GLB_InstallAddIn"
Else
Run "GLB_ReInstall"
End If
'
End Sub

.
~Oorang

Oorang
06-23-2008, 09:13 AM
Hi JW,
Welcome to the board:) The error you are getting would (seem) to indicate a memory issue. Are any of your routines working with large amounts of data in memory?

JWhite
06-26-2008, 03:23 PM
Thanks for replying. No, there's nothing in memory when it happens other than the XLSM workbook, which is just over 100k in size. All it has is maybe 2,000 lines of VBA code and a few things stored in a single worksheet.

My system has 4 GB memory so I very seldom have memory issues. When I run this routine it is the only Excel workbook open.

This problem is driving me crazy! I'm doing all my development in the add-in itself and periodically saving it as an XLSM to test it on another machine. The add-in is working great. It's only when I do the SaveAs to an XLSM and then later do a SaveAs to an XLAM on that I have a problem - and then only sometimes!

JWhite
06-26-2008, 03:31 PM
Forgot to mention that if I just copy the Add-in into the Add-ins directory without doing the conversion to XLSM and back to XLAM, the add-in works fine.

But that's not a fix. This is about to go into distribution as a commercial product and I don't want my customers to have to have to learn how to do that. As I'm sure you know, you can't copy the XLAM file into the Add-ins directory unless Excel is closed or you have uninstalled the add-in first.

Maybe I need to just throw out John Skewes approach but it's so elegant.

Does anyone else have a different approach to auto-reinstalling add-ins for distribution?

Bob Phillips
06-26-2008, 04:22 PM
Surely, if you are doing a full and regular distribution, it is tie to invest ain a production quality installer tool?

JWhite
06-26-2008, 04:34 PM
A production-quality installation tool? You're absolutely right. I've been trying to do it on the cheap using the code provided on this web site.

I have to admit I'm trying to do some pretty fancy stuff (not just in the add-in install but with the whole application) with no background at all and no help from anyone except what I've found on the Web. So if you can recommend somewhere I can look to find a production-quality installation tool, I'll get it. Our product isn't going to be cheap so we should invest in these things. But things are moving faster than we expected and we haven't invested in any tools to help my VBA development yet. I'm doing everything from scratch.

Incidentally, I think I've found a work-around to my installation problem. Instead of doing the SaveAs from the Add-in to an XLSM and back to an XLAM I had found that there's no problem if I just copy the XLAM directly into the Add-in directory manually. So I wrote macros in a separate "Install.XLSM" workbook that uninstalls the existing add-in, deletes it, then copies and installs the new Add-in file. I'll zip them together in a single file for distribution. It seems that, whatever the problem is, it happens during the two SaveAs operations and now that I've bypassed it it seems to work fine.

That will get me by for now but can someone please recommend a good Add-in installation program?

Oorang
06-27-2008, 12:15 AM
Caphyon makes a very friendly msi builder and for something as basic as what you are doing the free version will get the job done.

http://www.caphyon.com/

JWhite
06-27-2008, 06:00 AM
Thanks very much. I'll look into that right away.

Cosmo
06-27-2008, 06:20 AM
Another installer I have used is Tarma Installer http://tarma.com. I don't know if they have a free version anymore, but their full version is very reasonably priced at $250, and they have a quick install program that runs $99.

Bob Phillips
06-27-2008, 09:08 AM
I use Setup Factory. It is not free, but it does have scripting which means that you can adda script that updates the registry, auto-loading your addin.

Another good one is Inno Setup, that is free.

JWhite
07-08-2008, 08:22 AM
I'm not sure how to show this as "Solved" but I guess I'll count it as solved since I now have a work-around and I'm evaluating commercial installation software which should work even better.

But I still don't know why an Add-in which seems to work fine can cause Excel to crash - SOMETIMES - when you save it as a regular macro-enabled workbook and then re-save it as an add-in. Guess that will remain a mystery.

Thanks to everyone for your helpful suggestions regarding a commercial installation product!