Add-In Functions not listed in Automation

[mrvgson]

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]

"mrvgson" <[email protected]> wrote in message
news:[email protected]...
> 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

[mrvgson]

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)


"Fredrik Wahlgren" wrote:

>
> "mrvgson" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[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/
_______


mrvgson wrote:

> 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)
>
>
> "Fredrik Wahlgren" wrote:
>
>
>>"mrvgson" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>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
>>
>>
>>

[Jens Thiel]

Try something like Application.AddIns("MyUDF").Installed = true
--
http://ManagedXLL.net/
Replace MSDN with my first name when replying to my email address!



"mrvgson" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[mrvgson]

> 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.

[mrvgson]

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


"Jens Thiel" wrote:

> Try something like Application.AddIns("MyUDF").Installed = true
> --
> http://ManagedXLL.net/
> Replace MSDN with my first name when replying to my email address!
>
>
>
> "mrvgson" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

"mrvgson" <[email protected]> wrote in message
news:[email protected]...
> 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

[mrvgson]

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" wrote:

>
> "mrvgson" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Fredrik Wahlgren]

"mrvgson" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>

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

"mrvgson" <[email protected]> wrote in message
news:[email protected]...
> > 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.
>
>

[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/
_______

Fredrik Wahlgren wrote:

> "mrvgson" <[email protected]> wrote in message
> news:[email protected]...
>
>>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" wrote:
>>
>
>
> 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
>
>