How to get hold of the full path of the calling workbook

[Bobby]

Hi,

I want to get the full path of the workbook which contains the
function implemented using ExcelDna

I tried using the COM interrop, but it is causing problem as this code
is executed inside the code path of an XLL function.

using MsExcel = Microsoft.Office.Interop.Excel;

...

var app = ExcelDnaUtil.Application as
MsExcel.Application;
if (app != null && app.ActiveWorkbook !=
null)
{
return app.ActiveWorkbook.FullName;
}
return null;

Is there a way that I can do the same using XlCall or similar
functions provided by ExcelDna?

Thanks,

Bobby

[Govert van Drimmelen]

Hi Bobby,

You need to use the GET.XXX information functions. An example of how
to call these are in the GetInfoSample.zip file in the Files section
of the Google group. The zip file also has an .xls that calls all the
functions.

You can get the full path to the active file as follows.

[ExcelFunction(IsMacroType=true)]
public static string CallingFileName()
{
ExcelReference reference = (ExcelReference)XlCall.Excel
(XlCall.xlfCaller);
string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm,
reference);

return System.IO.Path.Combine(
(string)XlCall.Excel(XlCall.xlfGetDocument, 2, sheetName),
(string)XlCall.Excel(XlCall.xlfGetDocument, 88, sheetName));
}

Hope this helps.

Regards,
Govert

[Bobby]

HI Govert,

What is the difference betwen (IsMacroType=True ) and having it as
false? Is it a requirement to have this set to true to call the XlCall
functions?

Thanks,

Bobby

> > Bobby- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi Bobby,

UDF functions are registered in Excel as either Class 1
(IsMacroType=false) or Class 2 (IsMacroType=true). Class 1 UDFs are
more restricted as to which Excel functions can be called - in general
only worksheet functions can be called from Class 1 UDFs, while Class
2 UDFs can also call most macro functions, like GET.DOCUMENT
(xlfGetDocument) in this case. Only real macros (Class 3), which in
ExcelDna are methods with return type void, can call xlSet to set the
values of other cells. (Read http://msdn.microsoft.com/en-us/library/bb687835.aspx).

For this function, if you had marked the function as IsMacroType=false
(or left it out - false is also the default), the xlfCaller and
xlSheetNm calls would be fine, but the xlfGetDocument calls to
retrieve the path would fail.

Note that you should also add some error checking (or dealing with the
results from xlfGetDocument better) for the case where the document is
not yet saved.

Hope this makes sense.

Regards,
Govert

> > - Show quoted text -- Hide quoted text -

[Bobby]

Hi Govert,

Thanks for your reply. I made the changes and it now works. I also
added error handling for the case when document are not saved.

Thanks,

Bobby

On 29 Jul., 15:30, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi Bobby,
>

> UDF functions are registered in Excel as either Class 1
> (IsMacroType=false) or Class 2 (IsMacroType=true). Class 1 UDFs are
> more restricted as to which Excel functions can be called - in general
> only worksheet functions can be called from Class 1 UDFs, while Class
> 2 UDFs can also call most macro functions, like GET.DOCUMENT
> (xlfGetDocument) in this case. Only real macros (Class 3), which in
> ExcelDna are methods with return type void, can call xlSet to set the

> values of other cells. (Readhttp://msdn.microsoft.com/en-us/library/bb687835.aspx).

> > - Show quoted text -- Zitierten Text ausblenden -
>
> - Zitierten Text anzeigen -

[Alex Michel]

Hi Bobby,

Is it possible that you share your final code?

Can you call this function from background thread or must be invoked from UI as macro?

Thanks,

Alex