Get the current Workbook Object in C#

Question

I've been writing an application in C# which creates Custom Document properties in an Excel spreadsheet, I have a function for this which takes in a Workbook Object...

However, actually getting the current Workbook object is proving to be quite annoying, I am using ExcelDNA to add functionality, however, I can't seem to pass my function a valid Workbook COM object.

Thorbert

Answer 1

This is the way I am currently doing it it seems to work really well

using Excel = Microsoft.Office.Interop.Excel;      

Then you get active workbook

//Gets Excel and gets Activeworkbook and worksheet
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); 
oXL.Visible = true;
oWB = (Excel.Workbook)oXL.ActiveWorkbook; 
           
docProps = oWB.CustomDocumentProperties

Then I would try what you have and see how it works

Hope this helps

Beaufert

EXCELLENT. You sir are my hero, this has fixed a rather annoying problem I was having, needed a fresh pair of eyes I guess! Much appreciated. – Thorbert

Yeah not problem I have been doing quite a bit with excel if you have any questions let me know. I hope this helps you!!!! – Beaufert

From Excel-DNA you'll want to call ExcelDnaUtil.Application to make sure you get the Application object for the Excel process you're running in, instead of the last activated Excel instance which Marshal.GetActiveObject will return. – Excruciation

@RussellSaari Thanks for the answer. If there is two instances of Excel open, this method always returns the instance that was opened first. i.e. the second workbook will never get returned even if it is the active one. How do you get the current workbook regardless of what instance the file is opened with? – Over

Answer 2

If you need to find the activeworkbook with C#, if you are using Office Interop, you can try this kind of code:

(Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;

[Source]

Fernando

@AdamNumberFive, yes it does. You can do nearly the same thing with the excel COM object though by using the excel application instance you have and looking at the ActiveWorkbook property. – Heaviness

This works everytime, unlike Marshal.GetActiveObject("Excel.Application"); which would sometime fire new instances of Excel – Highbrow

the source is no longer available. I can't get this to work. Can someone post a good source for this? Extremely interested as Marshal is also creating new instance of Excel for me too. – Italian

@Jar: now you must use static class instead. See this question. – Arrowworm

Answer 3

As @Govert explained above in his comment:

using Excel = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;

// Get the correct application instance
Excel.Application xlapp = (Excel.Application)ExcelDnaUtil.Application;

// Get active workbook
Excel.Workbook wbook = xlapp.ActiveWorkbook;

Gupta

I am getting NullReference for xlapp. Has anything changed? Is this still supposed to work? – Over

I had IsThreadSafe = true in ExcelFunction argument. Once I removed it I was able to get excel reference. – Nonproductive

Answer 4

GetActiveObject() looks in the Running Object Table (ROT) and gives you the last Excel instance opened which may not corresponding with top Z order excel window.

Loop through the Z order and find the matching workbook.

See this link: - https://social.msdn.microsoft.com/Forums/office/en-US/060000d8-a899-49bf-a965-0576dee958d4/how-to-get-active-application?forum=exceldev

Shun