automate excel chart embedded in word document

[MarcG]

I have an excel chart in a word document. I can reference it by some variant
of:
range.InlineShapes[1]
I want to change it's datatable so that it reflects a set of values that I
have.

I know I'm at the right place because range.InlineShapes[1].ProgID =
"Excel.Chart.8"

I have a declaration...
Microsoft.Office.Interop.Excel.Chart chart;


How do I make the assignment to "chart" so that I can get at chart.DataTable ?

Thx
Marc

 

[Jialiang Ge [MSFT]]

Hello Marc,

From your post, my understanding on this issue is: you wonder how to access
the worksheet data of a excel chart which is copied into a Word document.
If I'm off base, please feel free to let me know.

As we see, the line ThisDocument.InlineShapes(1) returns a InlineShape
object whose prog ID is "Excel.Chart.8":
Set wrdInlineShape = ThisDocument.InlineShapes(1)
MsgBox wrdInlineShape.OLEFormat.ProgID

In order to access its worksheets, we could utilize OLEFormat.Object:
Set oBook = wrdInlineShape.OLEFormat.Object
oBook.Worksheets("Sheet1").Cells(1, 1).Value = "aaa"

wrdInlineShape.OLEFormat.Object returns an Excel.Workbook object if
OLEFormat.ProgID is "Excel.Chart", then we could use oBook to access the
worksheets (oBook.Worksheets), or charts (oBook.Charts) to customize or
change the excel objects in Word.

Please let me know if you have any other concerns, or need anything else.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MarcG]

Jialiang

The problem (in part) is that I'm working in C#, not VB, so I have to handle
types properly.

range.InlineShapes[1].OLEFormat.ProgID does equal "Excel.Chart.8"

range.InlineShapes[1].OLEFormat.Application is
Microsoft.Office.Interop.Word.ApplicationClass
^^^^

Curiously the line

Object obj = range.InlineShapes[1].OLEFormat.Object

gives an InvalidCastException with the message "When casting from a number,
the numbermust be less than infinity"

So, I'm afraid I still have the same question: How do I get the InlineShape,
that I know is an Excel Chart into a typed object so that I can operate on it
as a chart (not a workbook (which doesn't seem to work either))

Please try it in C# and let me know how you make out.

Thx
Marc

 

[Jialiang Ge [MSFT]]

Hello Marc,

I feel sorry that I did not notice 'Interop' in your first messsage. Here
is a C# sample code that demonstrate how to access the worksheets of an
Excel chart in a word document:

Please note that
1. we need to add the reference to Excel object library (using Excel =
Microsoft.Office.Interop.Excel) so that we could automate the workbook
object.
2. we need to call to activate the chart before we get the workbook from
OLEFormat.Object, otherwise we may receive the InvalidCastException
exception.

object missing = Type.Missing;
object filename = "file name";
Word.Application application = new
Microsoft.Office.Interop.Word.Application();
application.Visible = true;
Word.Document document = application.Documents.Open(ref
filename, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref
missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);

Word.InlineShape wrdInlineShape = document.InlineShapes[1];
if (wrdInlineShape.OLEFormat.ProgID == "Excel.Chart.8")
{
// Word doesn't keep all of its embedded objects in the
running state all the time.
// In order to access the interface you first have to
ensure the object is in the running state,
// ie: OLEFormat.Activate() (or something)
object verb = Word.WdOLEVerb.wdOLEVerbHide;
wrdInlineShape.OLEFormat.DoVerb(ref verb);

Excel.Workbook obook =
(Excel.Workbook)wrdInlineShape.OLEFormat.Object;
Excel.Worksheet sheet =
(Excel.Worksheet)obook.Worksheets["Sheet1"];
Excel.Range range = (Excel.Range)sheet.Cells[1, 1];
range.Value2 = "just for test";
}
// quit the word

Please try the code above and let me know the result. If you have any other
question or need anything else, please feel free to let me know.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MarcG]

Jialiang

That was very helpful, thank you. I was trying to cast as the
Excel.Application or Excel.Chart. Casting as the workbook works and from
there I can find the graph object and use it as needed.

I'm running into what must be the usual collection of
ManagedCode/Com/Automation problems.

In my app I have a document template that I use to hold a collection of
excel charts, word tables, etc. I use these to build a document from data I
get from a database (unfortunately the report is to "irregular" for SQL2005
ReportManager).

So, I'm cutting, pasting, and modifying these pieces between the template, a
temporary editing document, and a final report document.

For one thing, I activate each excel graph as you suggested by sending it a
"Hide" via doVerb. I then go and stick values in the table, then cut the
object and paste it in the report. I sned it another Hide at the end of the
chart update (hoping that this would get stuck until Excel was actually done.)

However, the first graph often winds up displaying the initial data, i.e.,
even though my cell setting generates no errors, the graph is not updated to
reflect the new values. All subsequent graphs behave properly.

Also, frequently, Word seems to periodically get into a knot forever running
100% of my CPU.

If I set breakpoints so that I pause between each set of interactions with
Word and Excel, the app works fine. So it very much looks like a
timing/synchronization problem.


Questions:
Are my calls to Word/Excel synchronous? That is, are these apps finished
doing what I told them to when they return to me?

Is there a proper protocol for knowing when Word/Excel are idle, ready for
work, or finished with the last command?

As an alternative to Excel.Chart, I might be able to us MSGraph.Chart, but I
can't find any documentation for this class in my VS2005 or Office developer
docs. Does MSGraph expose its API? If so, where is the documentation and how
do I get VS to know about the classes? Is MSGraph still part of Office? If
so, it's probably safer to use it than having to juggle Excel and Word
together.

Any thoughts or suggestions as to how I should proceed?

Thx
Marc

 

[MarcG]

Jialiang,

Stack trace follows:

System.Xml.XPath.XPathException was unhandled
Message="Function 'qib:questOfIntComplete()' has failed."
Source="WordApp"
StackTrace:
at IccsBenchmarkReportGenerator.Form1.btnTransform_Click(Object
sender, EventArgs e) in C:\Documents and Settings\mgrushco\My
Documents\Visual Studio 2005\Projects\WordApp\WordApp\Form1.cs:line 318
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons
button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd,
Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason,
ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at IccsBenchmarkReportGenerator.Program.Main() in C:\Documents and
Settings\mgrushco\My Documents\Visual Studio
2005\Projects\WordApp\WordApp\Program.cs:line 17
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence
assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

 

[MarcG]

Actually, looks like my "Select" is being rejected...

e.Message
"Call was rejected by callee. (Exception from HRESULT: 0x80010001
(RPC_E_CALL_REJECTED))"
e.InnerException
null
e.TargetSite
{Microsoft.Office.Interop.Word.Selection get_Selection()}
[System.Reflection.RuntimeMethodInfo]:
{Microsoft.Office.Interop.Word.Selection get_Selection()}
base {System.Reflection.MemberInfo}:
{Microsoft.Office.Interop.Word.Selection get_Selection()}
Attributes: FamANDAssem | Family | Virtual | HideBySig |
VtableLayoutMask | SpecialName
CallingConvention: Standard | HasThis
ContainsGenericParameters: false
IsAbstract: false
IsAssembly: false
IsConstructor: false
IsFamily: false
IsFamilyAndAssembly: false
IsFamilyOrAssembly: false
IsFinal: false
IsGenericMethod: false
IsGenericMethodDefinition: false
IsHideBySig: true
IsPrivate: false
IsPublic: true
IsSpecialName: true
IsStatic: false
IsVirtual: true
MethodHandle: {System.RuntimeMethodHandle}
e.Source
"Microsoft.Office.Interop.Word"

 

[Jialiang Ge [MSFT]]

Hello Marc

I am not exact about the following points. Would you help to confirm them
so that I could have a clearer picture of the issue?

For one thing, I activate each excel graph as you suggested by sending it
a "Hide" via doVerb. I then go and stick values in the table, then cut
the object and paste it in the report. I send it another Hide at the end
of the chart update (hoping that this would get stuck until Excel was
actually done.)

How did you stick values and cut/paste object in the report? Are they done
with automation or manually? In your last message, I notice that the
application throws an exception at Application.Selection. Would you let me
know where the Application.Selection is called? Would you paste some code
here to show the process of the operation?

I modified the test code that I posted last time, add
application.Selection.Copy() in the end, and it works well in my side:

object missing = Type.Missing;
object filename = "file name";
Word.Application application = new
Microsoft.Office.Interop.Word.Application();
application.Visible = true;
Word.Document document = application.Documents.Open(ref
filename, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref
missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing);

Word.InlineShape wrdInlineShape = document.InlineShapes[1];
if (wrdInlineShape.OLEFormat.ProgID == "Excel.Chart.8")
{
// Word doesn't keep all of its embedded objects in the
running state all the time.
// In order to access the interface you first have to
ensure the object is in the running state,
// ie: OLEFormat.Activate() (or something)
object verb = Word.WdOLEVerb.wdOLEVerbHide;
wrdInlineShape.OLEFormat.DoVerb(ref verb);

Excel.Workbook obook =
(Excel.Workbook)wrdInlineShape.OLEFormat.Object;
Excel.Worksheet sheet =
(Excel.Worksheet)obook.Worksheets["Sheet1"];
Excel.Range range = (Excel.Range)sheet.Cells[1, 1];
range.Value2 = "just for test";
}
Console.Read(); // at this point, I select the chart manually in word
application.Selection.Copy()
// quit the word

System.Xml.XPath.XPathException was unhandled
Message="Function 'qib:questOfIntComplete()' has failed."
Source="WordApp"

What is the qid:quesstionOfIntComplete()? Is it a user defined function in
Excel?

Now, for your questions:

Are my calls to Word/Excel synchronous? That is, are these apps
finished doing what I told them to when they return to me?

Yes, it is synchronous. When the method returns, the operation is done.

Is there a proper protocol for knowing when Word/Excel are idle, ready
for work, or finished with the last command?

As far as I know, Word object model does not expose a method for busy
status. See also:
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=m
icrosoft.public.word.vba.addins&tid=fa0f62d4-ea9d-49d9-9c4d-702796d2ea5a&p=1
Generally speaking, Work is in busy state when there is displaying a
message box. Word will not accept external communication when it is showing
a modal dialog.

As an alternative to Excel.Chart, I might be able to us MSGraph.Chart,
but I can't find any documentation for this class in my VS2005 or Office
developer docs. Does MSGraph expose its API? If so, where is the
documentation and how do I get VS to know about the classes? Is MSGraph
still part of Office? If so, it's probably safer to use it than having to juggle
Excel and Word together.

To use MSGraph, we need to reference Microsoft Graph *.* Object library.
Here are some KB articles about how to edit an MS graph Object
programmatically:
http://support.microsoft.com/kb/190239
http://support.microsoft.com/kb/244589

The complete introduction of Microsoft Graph object model can be found at
http://msdn2.microsoft.com/en-us/library/Aa198537(office.10).aspx

Regards
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[MarcG]

Jialiang

Thanks for the information about MSGraph.

I now have the app working and you might be interested in knowing the
solutions.

First, in trying to track down the automation synchronization issues, I
found the following...
http://msdn2.microsoft.com/en-us/library/ms228772(vs.80).aspx
that deals with synchronizing OLE Automaton traffic.

Once I implemented that, the problem with the first graph not being updated
went away.

I was still left with the problem of Word getting tied up and not
responding. You'll remember that my app has three components: my C# control
code, MSWord, and Excel. Word is hosting the document that contains a number
of Excel.Chart objects.

In my code, I copy pieces of the document from the template, and paste them
into a temporary document where I modify them based on data that I have, and
the cut them to the clipboard and then paste them into the report document.
The template pieces are found and selected based on the name of the enclosing
bookmark.

One bookmark contains a mix of text, Word tables, and an Excel.Chart. In the
code, I fixed up the text, set values in the chart, and then fixed up the
Word tables. Then the whole thing was cut and pasted into the report. This
always worked.

Once I had this going, I went on to deal with the next piece of the template
and here the bookmark contained only an Excel.Chart. My logic was the same:
copy from template, paste to temp document, update the chart, select the
range containing the chart, cut, and paste to the report.

In the application, it alternates between the first bookmark content and the
second. The app would run for a while and then hang.

It finally occurred to me that the only logic difference between the first
and second case was that in the first case, I operated on text after I fixed
up the chart and then did the select/cut/paste and in the second case I
didn't move away from the chart before I did the select/cut/paste.

I modified the code so that after it updated the chart, it simply went and
selected the temp doc's end-of-document bookmark. I then continued and
selected the chart and did the cut/paste. The app now works.

I think that the answer lies in the fact that Excel and the chart were still
activated when I did the cut/paste. By moving to the endOfDoc bookmark, I
deactivated the ole object. You can see the effect by replicating the
situation manually. Activate the chart by double clicking on it. Play with
the data sheet, and then click elsewhere in the document.

If my model is correct, the whole problem lies in the fact that the OLE
object was active when I moved it and then activated another one. For some
reason, you can get away with this for a while, but eventually Word will come
crashing to a halt.

If that's the case, the whole problem is just another example of having to
live with the COM crap.

Thanks for the help.
Marc