Add-In Functions not listed in Automation

[Guest]

Q from Excel novice

1. I created an Excel Addin (myUDF.xla) that contains my user defined
functions
and saved it in the "...Application Data\Microsoft\Excel\XLSTART"
folder so that it is automatically loaded everytime I launch Excel.

2. I then launched Excel. (it opens Book1.xls)

3. I click the "Paste Function fx" function and browse to see what User
defined functions are available.
The functions the I added in myUDF.xla show up on the list. Great...

However, when I launch Excel through automation, and then open a workbook,
and repeat step 3
my user defined functions are no longer on the list.

Please note: My addin"myUDF" is never listed in the Add-ins available even
in 2 when I launch Excel directly without automation. However, the functions
are made available in this case.

Has anyone encountered this problem when invoking Excel through automation?
How can I resolve this? Your help is greatly appreciated.

Thanks,
Siva

================
Here is a sample VB code that I used to test this:
Public Function DoLaunch(objExcelApplication As Excel.Application) As Boolean
On Local Error GoTo DoLaunchEH
Set objExcelApplication = CreateObject(mcsExcelApplication)
With objExcelApplication
.Visible = True
'.AddIns
End With
DoLaunch = True
DoLaunchEH:
End Function

 

[Fredrik Wahlgren]

Q from Excel novice

1. I created an Excel Addin (myUDF.xla) that contains my user defined
functions
and saved it in the "...Application Data\Microsoft\Excel\XLSTART"
folder so that it is automatically loaded everytime I launch Excel.

2. I then launched Excel. (it opens Book1.xls)

3. I click the "Paste Function fx" function and browse to see what User
defined functions are available.
The functions the I added in myUDF.xla show up on the list. Great...

However, when I launch Excel through automation, and then open a workbook,
and repeat step 3
my user defined functions are no longer on the list.

Please note: My addin"myUDF" is never listed in the Add-ins available even
in 2 when I launch Excel directly without automation. However, the functions
are made available in this case.

Has anyone encountered this problem when invoking Excel through automation?
How can I resolve this? Your help is greatly appreciated.

Thanks,
Siva

This is how Excel works. It won't load add-ins when Excel is started as an
automation object. Behavior by design.

/Fredrik

 

[Guest]

Thanks.
Are there other ways to work at this? ( I want to have my functions in a
separate place; I then want to use them repeatedly in different worksheets)

 

[Jon Peltier]

First, you haven't installed the add-in as an add-in. Double clicking on an add-in
in Explorer opens the workbook, but not as an add-in, so it won't show up in the list.

Second, as Fredrik points out, opening Excel via Automation avoids opening any of
the installed add-ins. But you can open the add-in file using
xlApp.Workbooks.Open(<add-in full name>)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jens Thiel]

Try something like Application.AddIns("MyUDF").Installed = true

 

[Guest]

First, you haven't installed the add-in as an add-in. Double clicking on an
add-in

in Explorer opens the workbook, but not as an add-in, so it won't show up in the list.

Thanks Jon. However, I think you misunderstood my question. I did not
double click on an add-in. The fact that I put it in the XLSTART seems to be
sufficient for Excel to realize that this Add-in should be loaded when Excel
is launched.

Second, as Fredrik points out, opening Excel via Automation avoids opening any of the installed add-ins. But you can open the add-in file using

xlApp.Workbooks.Open(<add-in full name>)
Again, I am not trying to open the Add-in in Excel. Rather I am trying to
open a different workbook and in that workbook I want to use the functions
defined in my add-in.

 

[Guest]

Jens, I did try your suggestion. However it did not work.
I even tried using the full path of the add-in file.
I also tried adding it first, but then it quits at that line (see code below)

Thanks.

==========================
Public Function DoLaunch(objExcelApplication As Excel.Application) As Boolean
On Local Error GoTo DoLaunchEH
Set objExcelApplication = CreateObject(mcsExcelApplication)
With objExcelApplication
.Visible = True
.AddIns.Add FileName:="C:\Documents and
Settings\natarajs\Application Data\Microsoft\Excel\XLSTART\IPEWB_UDF1.xla" '
Path + "myUDF.xla" --->IT JUMPS THE END FUNCTION AFTER THIS LINE
.AddIns("IPEWB_UDF1").Installed = True 'AddIns("myUDF").Installed =
True
End With

End Function

 

[Fredrik Wahlgren]

Jens, I did try your suggestion. However it did not work.
I even tried using the full path of the add-in file.
I also tried adding it first, but then it quits at that line (see code below)

Thanks.

I don't think you have installed your add-in properly. Follow these steps
carefully

1) Remove the add-in from the xkstart directory. Put it where you think it
should be.
2) Start Excel and open Tools|Add-ins.
3) If Excel complains that it can't find your add-in and asks whether it
should be removed, click yes.
4) Exit Excel. This will Save the registry settings.
5) Start Excel. Open Tools|add-ins and install your add-in.
6) Exit Excel. Again, this will save the settings.
7) Start Excel and verify that your add-in has been loaded.

Now you can do what Jens suggested.

/ Fredrik

 

[Guest]

Fredrik: What you suggested would work fine. However, this is not the
solution I am looking for.
My question was how to do all of this when Excel is invoked programatically.
In my case, I do not want to go through the steps you suggested manually.
From the earlier reply from Jon , Excel does not allow this in Automation.

Thanks for your suggestons.

 

[Fredrik Wahlgren]

Fredrik: What you suggested would work fine. However, this is not the
solution I am looking for.
My question was how to do all of this when Excel is invoked programatically.
In my case, I do not want to go through the steps you suggested manually.
From the earlier reply from Jon , Excel does not allow this in Automation.

Thanks for your suggestons.


:

I did have exactly the same problem and the solution we came up with was, as
far as I can remember, identical to what Jens suggested. I think you have to
do these steps manually in order for Jens' solution to work.

/ Fredrik

 

[Tom Ogilvy]

Opening an addin is equivalent to loading it in Tools=>Addins.

So, As Jon suggested, after starting excel with automation, open your addin
(it won't be visible), then open your workbook and your functions should be
available.

--
Regards,
Tom Ogilvy

an up in the list.
Thanks Jon. However, I think you misunderstood my question. I did not
double click on an add-in. The fact that I put it in the XLSTART seems to be
sufficient for Excel to realize that this Add-in should be loaded when Excel
is launched.

opening any of the installed add-ins. But you can open the add-in file
using

 

[Jon Peltier]

What Siva wants isn't for the add-in to be installed. It won't open by automation
anyway, so it doesn't matter (half the add-ins I use aren't "installed", just opened
like regular workbooks, and they work just the same).

As has been suggested, have the code open Excel, open the add-in, then open the
workbook.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______