COM Worksheet vs. ExcelReference to get an Excel sheet in C#

[Nils Tobias Kramer]

I have an ExcelCommand and I'm wondering how get hold of a specific sheet by:
1. Using COM Interop:

Application excelApplication = (Application)ExcelDna.Integration.ExcelDnaUtil.Application;
Worksheet mainSheetTest = excelApplication.Worksheets.get_Item("Name");

2. Use ExcelReference:

ExcelReference mainSheet = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Name");

In the end I want to be able to read and write from/to that sheet.
Thanks

[Govert van Drimmelen]

Hi Nils,

That all seems fine. Under .NET 4 you can use the property indexer directly for Application.Worksheets, and Range:

        [ExcelCommand(ShortCut = "^D")]   // Press Ctrl+Shift+D to run

        public static void dnaMyMacro3()

        {

            // Using COM Automation interfaces (with a reference to Microsoft.Office.Interop.Excel)

            Application app = (Application)ExcelDnaUtil.Application;

            Worksheet ws = app.Worksheets["Sheet1"];

            ws.Range["B2"].Value = "Hello there!";

        }

        [ExcelCommand(ShortCut = "^E")]   // Press Ctrl+Shift+E to run

        public static void dnaMyMacro4()

        {

            // Using the C API

            ExcelReference mainSheet = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet1");

            ExcelReference cellA1 = new ExcelReference(0, 0, 0, 0, mainSheet.SheetId);

            cellA1.SetValue("Hello via C API");

            ExcelReference cellA2 = new ExcelReference(1, 1, 0, 0, "Sheet1");

            cellA2.SetValue("Hello again!");

        }

-Govert

[Nils Tobias Kramer]

Thanks govert,
I'd like to avoid Excel.Intereop but can't figure out how to get a named range that are defined (a) for the whole workbook and (b) on a single sheet.
Any suggestions where to look at?

[Govert van Drimmelen]

Hi Nils,

Are you trying to define such Names, or just read/write to their targets?

If you post the corresponding COM or VBA code, that does what you want, I can try to consult the Steve Dalton book to see how one might do it with the C API. He's got quite a good discussion about Names.

-Govert

[Nils Tobias Kramer]

Sorry, forgot I already found it:

        public static object GetNamedRange(string namedRange)
        {
            object evalResult = XlCall.Excel(XlCall.xlfEvaluate, namedRange);
            return XlCall.Excel(XlCall.xlCoerce, evalResult);
        }

(a) name ranges for workbook: "!NameOfRange"
(b) named ranges on sheets: "SheetName!NameOfRange"

[Nils Tobias Kramer]

I was trying to read values from some previously defined named ranges.

[Nils Tobias Kramer]

Use ' for the sheet name: (b) named ranges on sheets: " ' SheetName' !NameOfRange "