PDA

View Full Version : Solved: Problem in auto loading toolpak add-in



Digita
05-19-2008, 12:44 AM
Hi guys,

I have a project that requires regular transfer of data from Access database into a big XL workbook. Due to extensive use of the networkday function, the anaylsis toolpak is required to be loaded. I have put the following lines at the start of my macro:



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


The macro halts half way through and reports a false error. I have spent a lot of time reviewing this rather long winded macro which also calls on various other subs within itself. However, I couldn't narrow down the problem. At the moment, before firing up the macro I have a temporary workaround. That is

1/ Load the toolpaks if they are not already or
2/ Unload and reload the add-ins if they are.

The macro runs OK if I use the above manual steps. Any ideas why this is?

Thanks in advance & regards


kp

Bob Phillips
05-19-2008, 02:25 AM
What code errors?

Digita
05-19-2008, 04:21 PM
Hi XLD.

Thanks for your response.

Without manual loading of the toolpak at the start, the code would stumble on line Mnth = Sheets(2).Range("M2").Value with an error message:


Runtime error '438'.
Object doesn't support this property or method.:banghead:

As background info, this issue arises only when a database application opens a new XL instance and dumps raw data into a new workbook it creates. I then open an existing XL template on top of the new workbook and run the macro to copy data across and perform various calculation inside the template workbook. The add-in has already been pre-loaded in the template file. If I open the template file on its own in a separate XL instance, the code would run fine.

I'm just curious why I have to do manual loading to the template every time when a new worksheet has been created by another application even though the macro does load the add-ins as well.

Kind regards


kp

Digita
05-20-2008, 12:23 AM
Hi XLD,

I think I now sort out this issue with:

AddIns("Analysis ToolPak").Installed = False
AddIns("Analysis ToolPak").Installed = True

It's kind of weird but it works.

Again thanks for your input.

Kind regards


kp

Bob Phillips
05-20-2008, 01:21 AM
Might be because it forces the addin install process?

tpoynton
05-20-2008, 06:48 AM
instead of uninstalling and reinstalling, how about:


If AddIns("Analysis ToolPak").Installed = False
AddIns("Analysis ToolPak").Installed = True
End If

Digita
05-20-2008, 07:14 PM
Hi again,

Unloading and reloading of the toolpak is the only workaround even though I agree it's not really elegant way of doing it.

Tpoynton,

Thanks for the code. I just tried your code but didn't work as we otherwise expect it to. Again, the issue seems to be linked to the opening of an XL instance from a 3rd party application which outputs data in an XL worksheet.

Regards :hi:


kp