It's not clear to me from your description exactly what you're doing,
but if you're starting Excel from Visual Basic using automation, none of the
add-ins load that you would normally expect to load if you started Excel
manually.

If you need one of these add-ins, like the Analysis Toolpak, you'll need
to load it in your VB code after you start Excel. The syntax looks like
this, assuming you have an object reference to your Excel Application object
in a variable named xlApp:

xlApp.Addins("Analysis ToolPak").Installed = True

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"wikamto" wrote in message
om...
Hi,

If someone could help me on this it would be appreciated greatly as it
has baffled me. I'm working on a project where I dynamically populate
Excel spreadsheets using Excel templates, Visual Basic and a SQL
Server Back-End. I'm currently having problems loading the Analysis
ToolPak Upon opening the the template from Visual Basic. The function
I am using is the WORKDAY() function.

When I open the template on Its own the workday function works and it
is added when I look in the addins manager. However as soon as I open
the template the the WORKDAY function does not work, returning #NAME.
If I go into the function builder the function is no longer there, and
when I go into the addins manager the analysis toolpak is checked.
Unchecking-Applying-Checking-Applying does not work. All of this
happens regardless of whether I have another excel session running or
not.

The only way I have found around it is if I save it and open the saved
file in a NEW excel session. If I close the saved file in the
Automated session and try to open it in the same session the error
remains, So I think it lies in an error in the automated session. I've
tried late and early binding but that doesn't seem to be the problem.
Can anyone help?

Thanks in advance
Mike.