topic How do I programmatically access an embedded Excel document from a C# addon? in Inventor Programming Forum

How do I programmatically access an embedded Excel document from a C# addon?

Anonymous — Wed, 21 Mar 2012 12:14:36 GMT

I have an excel spreadsheet I need to access. It is embedded within a dwg file(I'm a C# guy, not Inventor). How can I access this spreadsheet from an addon written in C#? I wasn't sure where to look in the documentation.

I assume I should start with ActiveDocument, but from there I'm not sure where to go. I'll need to be able to read and write to the table.

Thanks

Re: How do I programmatically access an embedded Excel document from a C# addon?

Mike.Wohletz — Thu, 22 Mar 2012 02:15:41 GMT

I don't think it is an Excel document anymore as it should be now inserted as a custom table. To access this table you can do it like the following.

Inventor.DrawingDocument oDrawDoc = ThisApplication.ActiveDocument;
Sheet oSheet = oDrawDoc.ActiveSheet;
if (oSheet.CustomTables.Count > 0) {
	CustomTable oCustomTable = oSheet.CustomTables.Item(1);
	foreach (Row oRow in oCustomTable.Rows) {
		string[] RowArray = new string[oCustomTable.Columns.Count + 1];
		for (int I = 1; I <= oCustomTable.Columns.Count; I++) {
			RowArray[I] = oRow.Item(I).Value;
		}
		Interaction.MsgBox(string.Join(Constants.vbLf, RowArray));
	}
}

Re: How do I programmatically access an embedded Excel document from a C# addon?

Anonymous — Fri, 23 Mar 2012 13:47:51 GMT

No, what I mean is I actually have it embedded as an Excel spreadsheet(not a custom table). It's listed under the 3rd party tab. When I open it from inside inventor, it opens the spreadsheet in Excel.

Here is the code which DOES work in VBA

Sub openexcel()
Dim oApp As Inventor.Application
Set oApp = ThisApplication
Dim oOleRef As ReferencedOLEFileDescriptor
Set oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1)
Dim oWB As Excel.Workbook
Call oOleRef.Activate(kEditOpenOLEVerb, oWB)
Dim oSheet As Excel.WorkSheet
Set oSheet = oWB.ActiveSheet
End Sub

Here is what I have so far in C#, but it crashes on kEditOpenOLEVerb

Inventor.Application oApp = this.m_inventorApplication;
            ReferencedOLEFileDescriptor oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors[1];
            Microsoft.Office.Interop.Excel.Workbook oWB;
            oOleRef.Activate(kEditOpenOLEVerb, oWB);
            Microsoft.Office.Interop.Excel._Worksheet oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

Any idea how I can modify this C# code to work the same way the VBA does? (I have to use C#)

Re: How do I programmatically access an embedded Excel document from a C# addon?

Mike.Wohletz — Fri, 23 Mar 2012 15:36:42 GMT

I converted what you had in VBA to VB.NET and then to C# and it looks like this. I have changed it so I could test it from a standalone exe with the first line.

public void openexcel()
{
	Inventor.Application oApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Inventor.Application");
	ReferencedOLEFileDescriptor oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1);
	Excel.Workbook oWB = null;
	oOleRef.Activate(OLEVerbEnum.kEditOpenOLEVerb, oWB);
	Excel.WorkSheet oSheet = oWB.ActiveSheet;
}

I don't do much C# ..

Re: How do I programmatically access an embedded Excel document from a C# addon?

Anonymous — Fri, 23 Mar 2012 15:49:26 GMT

Your code converted to C# is very similar to what I have for C#. However, unless you have a using reference I don't have yours wouldn't work either. This is because in VBA, Inventor has kEditOpenOLEVerb defined as something. But from outside Inventor, C# or VB code still needs to define kEditOpenOLEVerb before it can be used.

Re: How do I programmatically access an embedded Excel document from a C# addon?

Mike.Wohletz — Fri, 23 Mar 2012 15:55:16 GMT

Look at it agin, kEditOpenOLEVerb is not correct in yours I dont think.. it should be OLEVerbEnum.kEditOpenOLEVerb...

Re: How do I programmatically access an embedded Excel document from a C# addon?

Anonymous — Fri, 23 Mar 2012 15:59:23 GMT

I tried that as well. Neither way works for me. As I have it now, it tells me it doesn't exist in the current contect(which makes sense). Using your way, OLEVerbEnum.kEditOpenOLEVerb, it tells me that "the best overloaded method match has some invalid arguments".

Re: How do I programmatically access an embedded Excel document from a C# addon?

Mike.Wohletz — Fri, 23 Mar 2012 16:25:04 GMT

I don't know the answer to the C# part, but I tried what I have in VB.NET and it works just fine.. This is what I have in VB.NET..

   Sub openexcel()
        Dim oApp As Inventor.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Inventor.Application")
    Dim oOleRef As ReferencedOLEFileDescriptor  = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1)
        Dim oWB As Excel.Workbook = Nothing
        oOleRef.Activate(OLEVerbEnum.kEditOpenOLEVerb, oWB)
        Dim oSheet As Excel.Worksheet = oWB.ActiveSheet
        MsgBox(oSheet.Name)

    End Sub

Re: How do I programmatically access an embedded Excel document from a C# addon?

Anonymous — Mon, 26 Mar 2012 14:48:48 GMT

Here is the error I'm coming across: "cannot convert from 'Microsoft.Office.Interop.Excel.Workbook' to out object". My code below.

Inventor.Application oApp = this.m_inventorApplication;
            ReferencedOLEFileDescriptor oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors[1];
            Microsoft.Office.Interop.Excel.Workbook oWB = null;
            oOleRef.Activate(OLEVerbEnum.kEditOpenOLEVerb, oWB);
            Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.ActiveSheet;
            string name = oSheet.Name;

The Activate function needs an out object. However, it must also be an Excel object, and C# seems unable to figure this out on its own. Can anyone give advice on this?

Re: How do I programmatically access an embedded Excel document from a C# addon?

barbara.han — Tue, 27 Mar 2012 06:48:08 GMT

That means you should add "out" before the second parameter. I slightly modied the code and it should work: (also recommend to use try-catch as more as you can if you are not sure of how to use those functions)

using Inventor;

using System.Runtime;

using Microsoft.Office.Interop.Excel;

....

Inventor.Application oApp = null;

try

{

oApp = System.Runtime.InteropServices.Marshal.GetActiveObject("Inventor.Application") as Inventor.Application;

}

catch

{

}

if (oApp == null) return;

if (oApp.ActiveDocument != null) {

if (oApp.ActiveDocument.ReferencedOLEFileDescriptors.Count > 0)

{

ReferencedOLEFileDescriptor oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors[1];

Object retObj = null;

oOleRef.Activate(OLEVerbEnum.kEditOpenOLEVerb, out retObj);

try

{

Workbook oWB = (Workbook)retObj;

Worksheet oSheet = (Worksheet)oWB.ActiveSheet;

string name = oSheet.Name;

oWB.Close();

}

catch(Exception ex)

{

MessageBox.Show(ex.ToString());

}