Add-in functions when starting Excel programmatically

[Steve K.]

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.Applicatio
Set oExcel = CreateObject("Excel.Application"
oExcel.Workbooks.Ad
oExcel.Visible = Tru
End Su

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 Phillips]

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)

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?

 

[Bob Phillips]

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)

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.

 

[Steve K.]

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 cod
to install the add-in within that Excel object that you accessed. You wil
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 V
groups

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Hhmm, I see

allows the users to export the data from our app to Excel, so we won't b
able to (and more properly, shouldn't) access those add-ins

 

[Bob Phillips]

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)

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.

 

[Paul Clement]

¤ 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.aspx?scid=kb;en-us;270844&Product=vbb

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


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Bob Phillips]

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

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)

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

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). 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? automation,
but I'd like to find the solution just for add-ins to begin with, if at all
possible. 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.

not the
VB

groups.... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct) 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. it's
opened programmatically?

get

loaded,
nor do are and
put this

 

[Bonj]

Wrong.

¤ 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?

 

[Gale Green]

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.

I haven't had time to try it but if you search in Excel 2000 Help for
Analysis Toolpak, one of the articles tells you how to ensure that it
is installed and loaded.

Gale.

 

[Gale Green]

I haven't had time to try it but if you search in Excel 2000 Help for
Analysis Toolpak, one of the articles tells you how to ensure that it
is installed and loaded.

Sorry, I didn't mean Excel Help itself - I mean the Help in the Visual
Basic editor in Excel - it's also in the Help you get in the VB IDE
when using Automation.

Gale.