Path of XLL?

[Patrick O'Beirne]

I'm converting an addin that uses ThisWorkbook.Path to look for related
files.

Is there an equivalent in ExcelDna or NetOffice?

If not, I suppose the thing to do is to define in GloalHelpers.vb a
class (with a single instance) ThisWorkbook with child properties Name,
Path, IsAddin, Title, etc and populate them once I know how to find Path etc

TIA
Patrick

[Patrick O'Beirne]

Sorry, Govert, the right Google search found your post of last August
XlCall.Excel(XlCall.xlGetName))
which works as well in VB as C#

AND I found your post from Dec
"The translation from the Macros to XlCall.Excel(XlCall.xlc....) is
normally easy. "

Thanks!

[Govert van Drimmelen]

Hi Patrick,

This C API call will get you the path of the .xll:

Dim myPath As String = XlCall.Excel(XlCall.xlGetName)

Note that the Excel-DNA add-in is not based in a Workbook, so it
doesn't really make sense to talk about ThisWorkbook as if it would be
a Workbook object.

You could get the Name of the Excel-DNA library (defined in the .dna
file with the Name attribute) by something like
Dim myName As String = DnaLibrary.CurrentLibrary.Name

Regards,
Govert

[Patrick O'Beirne]

Govert,
Any idea why this line

XlCall.Excel(XlCall.xlGetName)

throws:

Exception of type 'ExcelDna.Integration.XlCallException' was thrown.

[Govert van Drimmelen]

Hi Patrick,

It's a call to the C API, which I suspect you are now making directly
from a Ribbon event handler.
In the ribbon event handler context you can't call the C API directly.
You could call a macro in your add-in via Application.Run, in which
case the xlGetName call from the macro should succeed. Of course then
you're back to the other issues you've mentioned.

If this really is the only information you need, you could make this
call in your AutoOpen (which is a 'macro context') and store the path
in a static (VB.NET would call it 'Shared') variable for later use.

Regards,
Govert

[Patrick O'Beirne]

The call is not in the class that Inherits ExcelRibbon, but in a macro
called from the Ribbon handler.

Interesting, in AutoOpen it works

Public Class AddIn
Implements IExcelAddIn
Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen
Dim s As String = XlCall.Excel(XlCall.xlGetName)
....etc...

The Ribbon code is

<ComVisible(True)>
Public Class Ribbon
Inherits ExcelRibbon
Sub CCTWAVBButton1(ByVal ctl As IRibbonControl)
Try
'Application.Run(ctl.Tag) will not throw exceptions, it
just returns here
TA_CountWordsInSelection()
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, ex.Source)
End Try
End Sub

And the macro is
Public Sub TA_CountWordsInSelection()
MsgBox(XlCall.Excel(XlCall.xlGetName))

and that throws an exception

[Patrick O'Beirne]

I've just tested with Application.Run instead of the direct call and now
the XlCall works.
in the example below, ctl.tag is the name of the macro

But I wanted to avoid Application.Run because i cannot guarantee it will
call the macro in the XLL, if a macro by the same name exists in a VBA
addin.

Sub CCTWAVBButton1(ByVal ctl As IRibbonControl)

Application.Run(ctl.Tag)

[Govert van Drimmelen]

Hi Patrick,

In the ribbon handler you are in a context where the C API doesn't
seem to work. Directly calling another function doesn't change this (I
wouldn't expect it to), only calling back to Excel to run a macro via
Application.Run will allow you to make calls to the C API.

I don't know of any documentation from Microsoft that explains why
this is the case, but everything you report is consistent with this.

You can make the call to get the xll path in your AutoOpen and store
the value for later use, or make the call from a macro you initiate
using Application.Run. One issue with the latter approach is the call
precedence issue you have reported.

-Govert

[Patrick O'Beirne]

Thanks, Govert.
I'd like to understand this concept of "context" better,
I'm used to languages where any feature was available anywhere in the
code, and the idea that the execution path would affect this is new to me.
If it's too long to explain, please let me know of any resources where I
can learn more.

Naturally the questions arise:
- Is there any other feature beside XlCall that does not work in code
directly called from the Ribbon?
- is there any other path into the code where these limitations apply?

TIA
P

[Govert van Drimmelen]

Hi Patrick,

I don't use context in any sense more technical than the 'surrounding
conditions in which the code is running'. I'm basically trying to
explain the Excel behaviour that I've seen and that has been reported.
I know of no documentation or resources that give more detail on the
behaviour we're discussing here.

-----

Let me start with an example in the VBA world:
If you create a user-defined function in VBA and it is called from a
worksheet formula, the function will not be able to set other parts of
the sheet. So a function like this will fail when called from a
worksheet formula

Function MyTest() As String
Range("B2").Value = "XXX
MyTest = "Hello"
End Function

Also, if MyTest were to call any other Function or Sub that tries to
set values on the sheet, that set would fail too.

Why does this fail?
I guess Excel has somehow remembered that it is busy calling a
function from a worksheet, so it has some internal flag or something
set that prevents the Range setter from working. So I'd say that when
Excel is calling your function while calculating a worksheet formula,
your code is running in a 'context' where setting values elsewhere in
the sheet is prohibited.

So already in VBA you have a case whether it is not true that 'any
feature was available anywhere'. The execution path to your
Range(...).Value setter determines whether it is a valid call or not.

------

Now Excel seems to also go into a special internal state when it calls
a user-defined function or macro in an .xll - a state where Excel is
ready to accept calls on the C API.
However, in the ribbon event handlers it seems Excel is not in a state
where it can accept calls on the C API.

Where else might Excel not be ready to accept C API calls?
* Certainly from any thread that is not the main Excel thread you
can't just call the C API (except for some restricted calls in the
multithreaded function evaluation).
* I think if you make some kind of UI on the main thread, say a
Windows Forms form or WPF window, and have event handlers for the
control, your event handlers can't call the C API.
* If you're in any of the RTD server methods, it seems you can't call
the C API.

As discussed previously, you can always use the COM interface to call
Application.Run to run a macro, and in this case Excel will know it is
running a macro and will be ready to accept C API calls.

How do you know if you're using the C API or the COM object model?
In Excel-DNA the ExcelReference and XlCall types define the C API. Any
part of the COM object model (that you know from VBA) reached through
ExcelDnaUtil.Application is part of the COM object model, and so
should be safe within your ribbon handlers.

-------

Do you ever have to use the C API - can't you always just use the COM
object model?

Here's a bit of a discussion about the different ways to interface
with Excel: http://msdn.microsoft.com/en-us/library/bb687829.aspx.

Excel-DNA add-ins are Xll add-ins and are registered using the C API -
because this is the only way of providing high-performance well-
integrated user-defined functions to Excel. But Excel-DNA also has
support for integrating with the COM object model. So Excel-DNA gives
you access to the full range of Excel extensibility.

For user-defined functions (called from a worksheet formula), only the
C API is supported. In my experience the COM object model used
carefully seems to work OK, but it is not supported by Microsoft - see
http://support.microsoft.com/kb/301443.

For macros (I mean anything run from a menu or ribbon handler, or via
Application.Run) the COM object model is supported and is probably
what I'd recommend for manipulating Excel. This is what your code
ported from VBA (using late-binding, the PIA or NetOffice) is doing.
The COM object model is richer than the C API - there are many
features of Excel not exposed through the C API.

However, sometimes the C API gives better performance at getting data
into or out of the sheet - see the story here:
http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects/3868370#3868370.

Rarely you might need some specialised information that is only
provided through the C API - this is the case for example when trying
to retrieve the .xll's path, which is a concept the COM object model
doesn't know about.

----

For the simplest add-ins that just add some user-defined functions to
Excel, none of this is important. If you are some macros from VBA,
mostly you can continue to use the COM object model and all will be
fine.

But at some level of sophistication you'll have to deal with the
complexity of the mix of interfaces Excel presents. Current versions
of Excel just do not offer a well-documented, comprehensive
programming interface. With Excel-DNA I try to make it possible to
access both the C API and COM interfaces from .NET, as completely and
flexibly as possible.

----

None of this talk about calling Excel from other threads yet. There
are some issues particular to calling the Excel COM object model from
another thread.

Anyway, I'm also still learning about this. So any additional
information would be very welcome.

I hope this answers your current questions.

Regards,
Govert

[Patrick O'Beirne]

Lots to learn, indeed.... it's a complex beast.

Thanks for all that, Govert!

P

[Sebastian]

Hello Govert,
Ive just had problem with getting path to xll in event handler, so i googled this thread.

This way

var myName = XlCall.Excel(XlCall.xlGetName); 

in AutoOpen method works fine.

But, if I need get this path in event handler, is it possible ?  Could you please write some example in c#  ?  

--
Regards,
Sebastian

[Govert van Drimmelen]

Hi Sebastian,

The C API call (XlCall.Excel(...)) will fail in a ribbon or form event handler.

Easiest would be to just set a static property of some class in your AutoOpen, which you can then read at any time.

A more convoluted workaround is to call a (possibly hidden) function in your add-in (say GetXllPath()) via the COM API:

result = Application.Run("GetXllPath").

Regards,

Govert

[Sebastian]

Hi Govert,
Thanks for quick reply.  I used similar  solution, made class that inherit from ExcelRibbon and  IExcelAddIn  too.
Something like:

class MyClass : ExcelRibbon, IExcelAddIn

so I got all event method and AutoOpen in  MyClass.

--
Regards,
Sebastian