Problems Loading Analysis ToolPak when automating from Visual Basic

[wikamto]

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.

 

[Rob Bovey]

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]

Thanks Rob, actually I did this about 20 Mins after I posted so I've
worked it out already. I also had to set the Analysis ToolPak to false
then true for it work. Excel still thinks that is added upon entry.

Also, why was this posted somewhere else other than google groups? I
can't reply to the the dev forums you ended up posting it too? why do
that for? I specifically posted to google and I would hope it would
stay that way in future posts.

 

[Tom Ogilvy]

I am reading both your posts in a developer group. Why did you post in a
developer group if you did not want the answer here?