Add-in functions when starting Excel programmatically

Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated.

An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’.

If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000.

To launch Excel I simply created a new VB project with a form and put this code:

Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub

Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in?

Thanks!

When you are using Excel in automation, the addins do not get loaded, nor do
any files in XLStart.

To overcome this, you have to do it yourself.

Add and install the addin,

oExcelAddIns.Add("myAddin.xla").Installed = True

you can then reference it as any other workbok

oExcel.Workbooks("myAddin.xla")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
news
Not sure if this question has already been discussed, I couldn't find an
answer in Google newgroups, so your help will be greatly appreciated.

An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack'
option. Now if you go under Insert-Function, you'll see new functions from
that add-in, such as MROUND function under function category 'All'.

If you close Excel and launch it programmatically, the add-in seems to be
loaded (checked) but the functions from it (such as MROUND) are missing.
This happens both in 97 and 2000.

To launch Excel I simply created a new VB project with a form and put this
code:

Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub

Is this an Excel bug or is there something special that needs to be done
to load the functions from the add-in?

Thanks!

Steve,

I'm not following.

Your code is working with the Excel app, and I just gave you some more code
to install the add-in within that Excel object that you accessed. You will
notice that I accessed it via your oExcel object variable.

PS, can you reply to all, my main hangout is the Excel NGs, not the VB
groups.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
Hhmm, I see.

Unfortunately, I'm not directly working with Excel app. Our application
allows the users to export the data from our app to Excel, so we won't be
able to (and more properly, shouldn't) access those add-ins.

So there's absolutely no way to replicate the state of Excel if it's
opened programmatically?

Thanks!

----- Bob Phillips wrote: -----

When you are using Excel in automation, the addins do not get loaded,
nor do
any files in XLStart.

To overcome this, you have to do it yourself.

Add and install the addin,

oExcelAddIns.Add("myAddin.xla").Installed = True

you can then reference it as any other workbok

oExcel.Workbooks("myAddin.xla")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
news
Not sure if this question has already been discussed, I couldn't
find an
answer in Google newgroups, so your help will be greatly appreciated.
An example: in Excel, under Tools-Add-Ins, select 'Analysis
Toolpack'
option. Now if you go under Insert-Function, you'll see new
functions from
that add-in, such as MROUND function under function category 'All'.
If you close Excel and launch it programmatically, the add-in
seems to be
loaded (checked) but the functions from it (such as MROUND) are
missing.
This happens both in 97 and 2000.
To launch Excel I simply created a new VB project with a form and
put this
code:
Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub
Is this an Excel bug or is there something special that needs to
be done
to load the functions from the add-in?
Thanks!

I must’ve failed to explain myself clearly. Here’s the situation I’m in:

Our customers have Excel installed and configured the way they want it, e.g. they have installed some add-ins, etc. We don’t know anything about what they have or where they installed it from, all we know that they must have Excel (to use the exporting feature in our product).

Ok, now they install our product. Our product displays some tabular data, and allows them to export this data into Excel - either save it to an Excel file or create a new instance of Excel and dump the data there. In the former case everything works fine since the customer opens the Excel file manually. In the latter case, we create a file in Excel format, automate Excel and open the file in that instance. Now in this case the add-ins are not loaded, which represents a problem to the customer. So, basically my question was, is there a way at all to open Excel through automation and have the add-ins loaded, just as if the customer was to open Excel manually?

You mentioned there are other things that aren’t loaded via automation, but I’d like to find the solution just for add-ins to begin with, if at all possible.

Thanks for your kind replies.


----- Bob Phillips wrote: -----

Steve,

I'm not following.

Your code is working with the Excel app, and I just gave you some more code
to install the add-in within that Excel object that you accessed. You will
notice that I accessed it via your oExcel object variable.

PS, can you reply to all, my main hangout is the Excel NGs, not the VB
groups.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
Hhmm, I see.
Unfortunately, I'm not directly working with Excel app. Our application
allows the users to export the data from our app to Excel, so we won't be
able to (and more properly, shouldn't) access those add-ins.
So there's absolutely no way to replicate the state of Excel if it's
opened programmatically?
Thanks!
----- Bob Phillips wrote: -----
When you are using Excel in automation, the addins do not get loaded,
nor do
any files in XLStart.
To overcome this, you have to do it yourself.
Add and install the addin,
oExcelAddIns.Add("myAddin.xla").Installed = True
you can then reference it as any other workbok
oExcel.Workbooks("myAddin.xla")
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
news
Not sure if this question has already been discussed, I couldn't
find an
answer in Google newgroups, so your help will be greatly appreciated.
An example: in Excel, under Tools-Add-Ins, select 'Analysis
Toolpack'
option. Now if you go under Insert-Function, you'll see new
functions from
that add-in, such as MROUND function under function category 'All'.
If you close Excel and launch it programmatically, the add-in
seems to be
loaded (checked) but the functions from it (such as MROUND) are
missing.
This happens both in 97 and 2000.
To launch Excel I simply created a new VB project with a form and
put this
code:
Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub
Is this an Excel bug or is there something special that needs to
be done
to load the functions from the add-in?
Thanks!

Steve,

See your problem.

Give this code a try.

When you create an Excel instance, it loops through the addins collection,
and loads all those that are 'installed'

Dim xlApp As Object
Dim ai as object

Set xlApp = CreateObject("Excel.Application")
With xlApp
For Each ai In xlApp.AddIns
If ai.Installed Then
xlApp.AddIns.Add(ai.Name).Installed = True
End If
Next ai
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
I must've failed to explain myself clearly. Here's the situation I'm in:

Our customers have Excel installed and configured the way they want it,
e.g. they have installed some add-ins, etc. We don't know anything about
what they have or where they installed it from, all we know that they must
have Excel (to use the exporting feature in our product).

Ok, now they install our product. Our product displays some tabular data,
and allows them to export this data into Excel - either save it to an Excel
file or create a new instance of Excel and dump the data there. In the
former case everything works fine since the customer opens the Excel file
manually. In the latter case, we create a file in Excel format, automate
Excel and open the file in that instance. Now in this case the add-ins are
not loaded, which represents a problem to the customer. So, basically my
question was, is there a way at all to open Excel through automation and
have the add-ins loaded, just as if the customer was to open Excel manually?

You mentioned there are other things that aren't loaded via automation,
but I'd like to find the solution just for add-ins to begin with, if at all
possible.

Thanks for your kind replies.


----- Bob Phillips wrote: -----

Steve,

I'm not following.

Your code is working with the Excel app, and I just gave you some
more code
to install the add-in within that Excel object that you accessed. You
will
notice that I accessed it via your oExcel object variable.

PS, can you reply to all, my main hangout is the Excel NGs, not the
VB
groups.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
Hhmm, I see.
Unfortunately, I'm not directly working with Excel app. Our
application
allows the users to export the data from our app to Excel, so we
won't be
able to (and more properly, shouldn't) access those add-ins.
So there's absolutely no way to replicate the state of Excel if
it's
opened programmatically?
Thanks!
----- Bob Phillips wrote: -----
When you are using Excel in automation, the addins do not get
loaded,
nor do
any files in XLStart.
To overcome this, you have to do it yourself.
Add and install the addin,
oExcelAddIns.Add("myAddin.xla").Installed = True
you can then reference it as any other workbok
oExcel.Workbooks("myAddin.xla")
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
news
Not sure if this question has already been discussed, I couldn't
find an
answer in Google newgroups, so your help will be greatly
appreciated.
An example: in Excel, under Tools-Add-Ins, select 'Analysis
Toolpack'
option. Now if you go under Insert-Function, you'll see new
functions from
that add-in, such as MROUND function under function category
'All'.
If you close Excel and launch it programmatically, the add-in
seems to be
loaded (checked) but the functions from it (such as MROUND)
are
missing.
This happens both in 97 and 2000.
To launch Excel I simply created a new VB project with a form and
put this
code:
Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub
Is this an Excel bug or is there something special that needs to
be done
to load the functions from the add-in?
Thanks!

Bob,

Unfortunately, I couldn't make this code work. It seems that you're trying to add a add-in that already exists, which gives me an error.

But I tried something else that seems to be working. If I set the Installed property to False and reset it to True the add-ins seem to get loaded:

For Each oAddIn In oExcel.AddIns
With oAddIn
If .Installed Then
.Installed = False
.Installed = True
End If
End With
Next oAddIn

I'm going to see if this code will cause any issues, but otherwise it seems to do the job.

Thanks a lot for your help!

Respectfully,
Steve

----- Bob Phillips wrote: -----

Steve,

See your problem.

Give this code a try.

When you create an Excel instance, it loops through the addins collection,
and loads all those that are 'installed'

Dim xlApp As Object
Dim ai as object

Set xlApp = CreateObject("Excel.Application")
With xlApp
For Each ai In xlApp.AddIns
If ai.Installed Then
xlApp.AddIns.Add(ai.Name).Installed = True
End If
Next ai
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
I must've failed to explain myself clearly. Here's the situation I'm in:
Our customers have Excel installed and configured the way they want it,
e.g. they have installed some add-ins, etc. We don't know anything about
what they have or where they installed it from, all we know that they must
have Excel (to use the exporting feature in our product).
Ok, now they install our product. Our product displays some tabular data,
and allows them to export this data into Excel - either save it to an Excel
file or create a new instance of Excel and dump the data there. In the
former case everything works fine since the customer opens the Excel file
manually. In the latter case, we create a file in Excel format, automate
Excel and open the file in that instance. Now in this case the add-ins are
not loaded, which represents a problem to the customer. So, basically my
question was, is there a way at all to open Excel through automation and
have the add-ins loaded, just as if the customer was to open Excel manually?
You mentioned there are other things that aren't loaded via automation,
but I'd like to find the solution just for add-ins to begin with, if at all
possible.
Thanks for your kind replies.
----- Bob Phillips wrote: -----
Steve,
I'm not following.
Your code is working with the Excel app, and I just gave you some
more code
to install the add-in within that Excel object that you accessed. You
will
notice that I accessed it via your oExcel object variable.
PS, can you reply to all, my main hangout is the Excel NGs, not the
VB
groups.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
...
Hhmm, I see.
Unfortunately, I'm not directly working with Excel app. Our
application
allows the users to export the data from our app to Excel, so we
won't be
able to (and more properly, shouldn't) access those add-ins.
So there's absolutely no way to replicate the state of Excel if
it's
opened programmatically?
Thanks!
----- Bob Phillips wrote: -----
When you are using Excel in automation, the addins do not get
loaded,
nor do
any files in XLStart.
To overcome this, you have to do it yourself.
Add and install the addin,
oExcelAddIns.Add("myAddin.xla").Installed = True
you can then reference it as any other workbok
oExcel.Workbooks("myAddin.xla")
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
news
Not sure if this question has already been discussed, I couldn't
find an
answer in Google newgroups, so your help will be greatly
appreciated.
An example: in Excel, under Tools-Add-Ins, select 'Analysis
Toolpack'
option. Now if you go under Insert-Function, you'll see new
functions from
that add-in, such as MROUND function under function category
'All'.
If you close Excel and launch it programmatically, the add-in
seems to be
loaded (checked) but the functions from it (such as MROUND)
are
missing.
This happens both in 97 and 2000.
To launch Excel I simply created a new VB project with a form and
put this
code:
Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub
Is this an Excel bug or is there something special that needs to
be done
to load the functions from the add-in?
Thanks!

On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote:

¤ Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated.
¤
¤ An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’.
¤
¤ If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000.
¤
¤ To launch Excel I simply created a new VB project with a form and put this code:
¤
¤ Private Sub Form_Load()
¤ Dim oExcel As Excel.Application
¤ Set oExcel = CreateObject("Excel.Application")
¤ oExcel.Workbooks.Add
¤ oExcel.Visible = True
¤ End Sub
¤
¤ Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in?

See if the following helps - the procedure should be the same when using any functionality from the
Analysis TookPak:

HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak
http://support.microsoft.com/default...44&Product=vbb

XL2000: Add-Ins Don't Load When Using the CreateObject Command
http://support.microsoft.com/default.aspx?kbid=213489


Paul ~~~
Microsoft MVP (Visual Basic)

Steve,

That's good, but very odd. They should all exit, and the registry says which
should be installed, but in automation they don't get installed (which is
why I checked Excel knew which were installed and then installed them).

I did it from Word, but I definitely needed to add the addin and install it.

Anyway, we have some solution.

Regards

Bob

"Steve K." wrote in message
...
Bob,

Unfortunately, I couldn't make this code work. It seems that you're trying
to add a add-in that already exists, which gives me an error.

But I tried something else that seems to be working. If I set the
Installed property to False and reset it to True the add-ins seem to get
loaded:

For Each oAddIn In oExcel.AddIns
With oAddIn
If .Installed Then
.Installed = False
.Installed = True
End If
End With
Next oAddIn

I'm going to see if this code will cause any issues, but otherwise it
seems to do the job.

Thanks a lot for your help!

Respectfully,
Steve

----- Bob Phillips wrote: -----

Steve,

See your problem.

Give this code a try.

When you create an Excel instance, it loops through the addins
collection,
and loads all those that are 'installed'

Dim xlApp As Object
Dim ai as object

Set xlApp = CreateObject("Excel.Application")
With xlApp
For Each ai In xlApp.AddIns
If ai.Installed Then
xlApp.AddIns.Add(ai.Name).Installed = True
End If
Next ai
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steve K." wrote in message
...
I must've failed to explain myself clearly. Here's the situation
I'm in:
Our customers have Excel installed and configured the way they
want it,
e.g. they have installed some add-ins, etc. We don't know anything
about
what they have or where they installed it from, all we know that they
must
have Excel (to use the exporting feature in our product).
Ok, now they install our product. Our product displays some
tabular data,
and allows them to export this data into Excel - either save it to an
Excel
file or create a new instance of Excel and dump the data there. In
the
former case everything works fine since the customer opens the Excel
file
manually. In the latter case, we create a file in Excel format,
automate
Excel and open the file in that instance. Now in this case the
add-ins are
not loaded, which represents a problem to the customer. So, basically
my
question was, is there a way at all to open Excel through automation
and
have the add-ins loaded, just as if the customer was to open Excel
manually?
You mentioned there are other things that aren't loaded via
automation,
but I'd like to find the solution just for add-ins to begin with, if
at all
possible.
Thanks for your kind replies.
----- Bob Phillips wrote: -----
Steve,
I'm not following.
Your code is working with the Excel app, and I just gave you
some
more code
to install the add-in within that Excel object that you
accessed. You
will
notice that I accessed it via your oExcel object variable.
PS, can you reply to all, my main hangout is the Excel NGs,
not the
VB
groups.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
...
Hhmm, I see.
Unfortunately, I'm not directly working with Excel app. Our
application
allows the users to export the data from our app to Excel, so
we
won't be
able to (and more properly, shouldn't) access those add-ins.
So there's absolutely no way to replicate the state of Excel if
it's
opened programmatically?
Thanks!
----- Bob Phillips wrote: -----
When you are using Excel in automation, the addins do not
get
loaded,
nor do
any files in XLStart.
To overcome this, you have to do it yourself.
Add and install the addin,
oExcelAddIns.Add("myAddin.xla").Installed = True
you can then reference it as any other workbok
oExcel.Workbooks("myAddin.xla")
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Steve K." wrote in message
news Not sure if this question has already been discussed, I couldn't
find an
answer in Google newgroups, so your help will be greatly
appreciated.
An example: in Excel, under Tools-Add-Ins, select 'Analysis
Toolpack'
option. Now if you go under Insert-Function, you'll see new
functions from
that add-in, such as MROUND function under function category
'All'.
If you close Excel and launch it programmatically, the add-in
seems to be
loaded (checked) but the functions from it (such as MROUND)
are
missing.
This happens both in 97 and 2000.
To launch Excel I simply created a new VB project with a form
and
put this
code:
Private Sub Form_Load()
Dim oExcel As Excel.Application
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add
oExcel.Visible = True
End Sub
Is this an Excel bug or is there something special that needs to
be done
to load the functions from the add-in?
Thanks!

Wrong.

"Paul Clement" wrote in message
...
On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote:

¤ Not sure if this question has already been discussed, I couldn't find an
answer in Google newgroups, so your help will be greatly appreciated.
¤
¤ An example: in Excel, under Tools-Add-Ins, select 'Analysis Toolpack'
option. Now if you go under Insert-Function, you'll see new functions from
that add-in, such as MROUND function under function category 'All'.
¤
¤ If you close Excel and launch it programmatically, the add-in seems to
be loaded (checked) but the functions from it (such as MROUND) are missing.
This happens both in 97 and 2000.
¤
¤ To launch Excel I simply created a new VB project with a form and put
this code:
¤
¤ Private Sub Form_Load()
¤ Dim oExcel As Excel.Application
¤ Set oExcel = CreateObject("Excel.Application")
¤ oExcel.Workbooks.Add
¤ oExcel.Visible = True
¤ End Sub
¤
¤ Is this an Excel bug or is there something special that needs to be done
to load the functions from the add-in?

See if the following helps - the procedure should be the same when using
any functionality from the
Analysis TookPak:

HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak
http://support.microsoft.com/default...44&Product=vbb

XL2000: Add-Ins Don't Load When Using the CreateObject Command
http://support.microsoft.com/default.aspx?kbid=213489


Paul ~~~
Microsoft MVP (Visual Basic)

I am somewhat confused on a related note. I am trying to make an EDATE call within a function using VBA in excel2000 (part of the analysis toolpak). Unfortunately I receive the error message
"Function or sub not defined" during compilation.
From reading the message boards, I understand this is due to the analysis pack not being loaded up at startup, however I would appreciate if someone could suggest the exact code that I have to include (i.e. an include style statement) to ensure that the EDATE function is visible.

Thanks for your help in advance..
Vatche

"Paul Clement" wrote:

On Tue, 13 Apr 2004 14:16:04 -0700, "Steve K." wrote:

¤ Not sure if this question has already been discussed, I couldn’t find an answer in Google newgroups, so your help will be greatly appreciated.
¤
¤ An example: in Excel, under Tools-Add-Ins, select ‘Analysis Toolpack’ option. Now if you go under Insert-Function, you’ll see new functions from that add-in, such as MROUND function under function category ‘All’.
¤
¤ If you close Excel and launch it programmatically, the add-in seems to be loaded (checked) but the functions from it (such as MROUND) are missing. This happens both in 97 and 2000.
¤
¤ To launch Excel I simply created a new VB project with a form and put this code:
¤
¤ Private Sub Form_Load()
¤ Dim oExcel As Excel.Application
¤ Set oExcel = CreateObject("Excel.Application")
¤ oExcel.Workbooks.Add
¤ oExcel.Visible = True
¤ End Sub
¤
¤ Is this an Excel bug or is there something special that needs to be done to load the functions from the add-in?

See if the following helps - the procedure should be the same when using any functionality from the
Analysis TookPak:

HOWTO: Create an Excel Histogram by Using Automation and Analysis ToolPak
http://support.microsoft.com/default...44&Product=vbb

XL2000: Add-Ins Don't Load When Using the CreateObject Command
http://support.microsoft.com/default.aspx?kbid=213489


Paul ~~~
Microsoft MVP (Visual Basic)