How to get sheet by ExcelReference?

Oct 23, 2011 at 1:03 PM

How do I get a WorkSheet from an ExcelReference?

I've seen examples of the following but this don't work:

ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

Excel.Application xlApp = (Excel.Application)ExcelDna.Integration.ExcelDnaUtil.Application;

string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference);

Excel.Worksheet xlActiveSheet = (Excel.Worksheet)xlApp.ActiveWorkbook.Worksheets[sheetName];

Coordinator
Oct 23, 2011 at 2:05 PM

Hi Niels,

Firstly the caller might not be in the ActiveWorkbook - the end of this thread discusses the difference between the Active sheet and the Current sheet: http://groups.google.com/group/exceldna/browse_thread/thread/32ec88b6a62596ad.

Secondly, your sheetName probably looks like this: "[Book1]Sheet1"; then ....Worksheets["[Book1]Sheet1"] fails to find the sheet with that name. You'll need to get rid of the [Book1] part, and use that to get the right workbook from the Workbooks collection.

-Govert

Oct 23, 2011 at 5:25 PM

Is there anyway you can get the sheet from reference.sheetId?

Coordinator
Oct 23, 2011 at 6:17 PM
Hi Niels,

For the C API you'd normally use an ExcelReference.
If you want to get a COM Worksheet object your plan seems like the right one -
you just need to parse the book and sheet names out of the xlSheetNm result.

What are you trying to do?

-Govert
Oct 23, 2011 at 7:44 PM

Govert,

Yes, I need the COM Worksheet object where the cell is located that has the calling function.

I'll try the sheetName approach. Just hoped there was a clean way to take ExcelReference.SheetId and get the sheet. 

Thanks for your support. I've donate a small token of my appreciation.

/Niels