ArcDesktop 10.5: Adding Excel object to mxd with arcobjects

[JanaMartin]

Background:

I'm using ArcdDesktop 10.5 with Arcobjects (C#) for development.

Problem:

I'm working on automated Chart creation for a client. They need a table of data to be added to the chart, and the excel sheet gives the most flexibility in styling.

I have created the excel with C#, and am trying to add this object to a MXD that is loaded in memory. I believe that I'm close but need some help to get it completed.

I'm able to load the excel sheet into memory but am not able to 'add' or 'update' the OLEFrame with the content.

I have tried adding a new OLEFrame with a given Envelope for placement and I've tried to find the OLEFrame from the IGraphicsContainer of the MXD and update the content. I keep getting the following error:

"Error HRESULT E_FAIL has been returned from a call to a COM component. at ESRI.ArcGIS.ArcMapUI.OleFrameClass.Load(IStream pstm)"

Here is the code that I'm using.

var outputFilePath = @"...";

IMemoryBlobStream MemStream = new MemoryBlobStreamClass();
IPageLayout pageLayout = TheMXD.PageLayout;
IGraphicsContainer graphicsContainer = pageLayout as IGraphicsContainer;
graphicsContainer.Reset();

IElement pElement = graphicsContainer.Next();

while (pElement != null)
{
IElementProperties ElementProps = pElement as IElementProperties;
if (ElementProps.Name.Contains("Excel"))
{
      MemStream.LoadFromFile(outputFilePath);
      IPersistStream oleSteam = pElement as IPersistStream;

      oleSteam.Load(MemStream);
      break;
}

pElement = graphicsContainer.Next();
}

((IActiveView)TheMXD.PageLayout).PartialRefresh(esriViewDrawPhase.esriViewGraphics, null, null);

[DuncanHornby]

Just to get some clarification, are you trying to load the data into arcmap? If so you want to add the table as a standalone table. Your code suggests you are trying to embed the whole worksheet as some sort of graphic element to page layout. Is that really your intention? 

[JanaMartin]

I'm trying to mirror the same functionality as the  "Insert -> Object" that is built into Arcmap. The basic insert feature class as table, does not give enough style functionality. Adding the Excel as an object will give a nice formatted 'table'.

[DuncanHornby]

Got interested in this problem but failed to get anywhere with it. I developed the following VBA code but it fails in one of two places:

Public Sub test()
 Dim pMXD As IMxDocument
 Set pMXD = ThisDocument
 Dim pPageLayout As IPageLayout
 Set pPageLayout = pMXD.PageLayout
 Dim pGraphicsContainer As IGraphicsContainer
 Set pGraphicsContainer = pPageLayout
 pGraphicsContainer.Reset
 Dim pElement As IElement
 Dim sPath As String
 Let sPath = "C:\Temp\Test1\SCT_OUTPUT\Excel\tblPolygonFeaturesMasterPolygonSingle__ee.xls"
 Dim pBlobStream As IBlobStream
 Set pBlobStream = New MemoryBlobStream
 pBlobStream.LoadFromFile sPath
 Dim pOleFrame As IOleFrame
 Set pOleFrame = New OleFrame
 Dim pPersistStream As IPersistStream
 Set pPersistStream = pOleFrame
 pPersistStream.Load pBlobStream
 pOleFrame.Open ' This line throws an error, if you comment out it gets to the AddElement and bombs out
 Set pElement = pOleFrame
 Dim pElementProp As IElementProperties3
 Set pElementProp = pElement
 Debug.Print pElementProp.Type ' Returns "OLE Frame"
 pGraphicsContainer.AddElement pElement, 0 ' This line throws an error
 pMXD.ActiveView.Refresh
End Sub‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

As a side thought looking at your code you are replacing an existing OLE Frame in your page layout. If you use the GUI then you can only ever CREATE an OLE object, I could see no way of changing the data source. So even if you got the code working I wonder if you can only create rather than replace?

I did a load of searching on the internet and could find zero examples of how one would programatically insert an OLE Frame onto the page layout!  Would be good if someone from ESRI produced an example? The code snippets I found were about accessing an existing OLE object on the page layout, not creating or updating.

Sorry this is the best I could do! 

[JanaMartin]

Thank for taking the time to look at this.

I originally started by adding in a new OLEFrame, setting the envelope etc, but it would be null everytime that I added content to the Frame.

I'm going to open a support ticket with ESRI on this one. Given the API information this should be possible, but like you said there are zero examples of this.

My guess is that there is other non arcobjects operations going on in ArcMap. Whether or not esri will give that information to me is the other question.

I'll post any findings here.Hornbydd‌

[GKmieliauskas]

Hi Jana,

I have found that some functionality is available only using MFC environment:

https://desktop.arcgis.com/en/arcobjects/latest/net/webframe.htm#IOleFrame_CreateOleClientItem.htm

So you need to create VC++ library which can create you IElement of IOLEFrame type. How to do that you could find on GitHub sample:

https://github.com/Microsoft/VCSamples/tree/master/VC2010Samples/MFC/ole/MFCBind

It uses the same Insert object dialog as ArcMap does.

[JanaMartin]

Thank you for finding this information. I never really thought to look in the other apis. I'll take a look into this and post my final solution.

[MaJiayou]

Hi Jana,have you fix it?I m stucked in this today,need help