Distributing an excel application

[benjammind]

I have developed an application using Excel 2000 and VBA, the app requires the Analysis toolpack for VBA to be installed for some of the code to run correctly.
The code does not execute correctly if the toolpack is not installed.

When I come to distribute this app to our field-based end users, will each PC need to install this toolpack? and therefore require the original office install files. Or is there a way to bundle the toolpack with the excel app?

Any advice or info is appreciated.

Ben

[dominicb]

Good morning Benjammind

The analysis toolpak comes as standard with all versions of excel (I think as far back as 97, may be even longer). You need to use the code below to make sure that the add-in is installed when the file is opened, if it isn't already.

Sub auto_open()
AddIns("Analysis ToolPak - VBA").Installed = True
End Sub

If you are using event procedures in your code you may want to put it into a workbook_open event, instead of using auto_open.

One point, I have never actually done what you are trying to do, but you may have to save the file with the calculation set to manual, and use your vba code to reset it back to automatic, because if someone opens your file for the first time without the toolpak installed any calculation requiring the toolpak would fail, until the toolpak is invoked and a recalc forced.

HTH

DominicB

[benjammind]

Thank you for your reply Dominic,
Am I correct in understanding that if the code you kindly supplied below indicates that the Analysis toolpack is not installed, then the original install files will be needed when office prompts for them?

Ben

[dominicb]

Hi Benjammind

If the analysis tool pak file (atpvbaen.xla) is not present in the users installation the the code will come to a grinding halt. We need a cheeky little error trapping routine like the one below which, if the file is not present will inform the user of a problem. If the file is present but the add-in is not invoked, then it will be invoked, if the file is present and the add-in is invoked then nothing will happen. This will happen so seamlessly that unless the error message is diplayed, the user will not even be aware that his add-in has just been invoked.

BTW the file must be run with macros enabled.

Sub auto_open()
On Error GoTo Last
AddIns("Analysis ToolPak - VBA").Installed = True
Exit Sub
Last:
MsgBox "An error has occurred - suggest you see " _
& "Benjammind 'cos he's the man"
End Sub

HTH

DominicB

[benjammind]

Thanks once again for your response, the code you supplied works beautifully, my next question though is: If the code determines that the toolpak is not installed, can it be installed via VBA? without the user having to do it manually by clicking on tools -> addins -> etc etc.

Ben

[dominicb]

Hi Benjammind

OK, you can forget about the user having to go through the Tools > Addins bit. They won't have to - your code does that for them. However there is a remote chance that the file atpvbaen.xla is not on their PC - if so your code politely points out that they should contact you.

If the code isn't there we have a problem. There is a special directory in which the Microsoft add-ins live and a special directory in which user developed add-in should live. Now, remembering that an XL97-Win2000 add-in directory might be in a different location to an XL2003-WinXP add-in directory, is where the problem occurs. VBA can determine the user developed add-in directory, but not the Microsoft one, now if all your field staff have the same OS and same XL version then their MS add-in directories will be the same. If that's the case then something like this would help.

Source = "a:\atpvbaen.xla"
Destination = "c:\Program Files\Microsoft Office\ _
Office11\Library\Analysis\atpvbaen.xla"
FileCopy Source, Destination

Obviously, your path may be different, that is my path on a WinXP SP2 Home system running XL2003.

However, all that said, I think that all the PC's will have this add-in on their hard drive - from what I remember you don't get a choice about some of the add-ins at installation, and the analysis toolpak is just about the most popular add-ins available for Excel. My advice would be to just distribute the file but include your telephone extension number in the error msgbox - just in case, but I wouldn't forsee too many problems on this issue.

Good luck!!

HTH

DominicB