none
Sizing issue with embedded Excel charts in Word 2013 RRS feed

  • Question

  • We've got an Office add-in compiled against the Office 2013 PIAs.  We have a "graph template" doc file that has an embedded Excel chart that is the correct size.  The add-in adds a button which allows the user to insert the graph into their own Word document.  It does this by opening the "Graph Templates.doc" file, accessing the graph via the InlineShapes collection, copies the graph to the clipboard and then does a PasteSpecial into the target document.  The result is a graph that is only a fraction of the height of the original graph.  I've reduced the code down to the following  (VB.NET in VS2008).

    The code relies on two doc files and the template file must be in the location specified in the code (or edit the path in the code).

    Graph Templates.doc (I guess I can't have links until my account is verified?  Let me know and I will email them.)

    TestTemplate.doc

    These documents are opened in compatibility mode, and I thought perhaps it may have something to do with the bad behavior, but after up-converting I still had the same behavior.  I've pretty much run out of ideas, so any help would be appreciated.

    Thanks,

    Terry

    PS - How do I go about getting my account "verified" so I can post images and links?

    Code below:

        Private Sub InsertGraphToReport2()
            Dim graph_template_full_name As String
            Dim doc As Word.Document
            Dim inline_shape As Word.InlineShape
            Dim work_book As Excel.Workbook
            Dim current_selection As Word.Selection
    
            Dim docs As Word.Documents
            Dim temporary_file As String
    
            current_selection = m_WordApp.Selection
    
            graph_template_full_name = "C:\ProgramData\Test\Graph Templates.doc"
            If (File.Exists(Utility.GetStandardPath(graph_template_full_name)) = False) Then
                Exit Sub
            End If
    
            temporary_file = graph_template_full_name.Substring(0, graph_template_full_name.LastIndexOf(".")) & ".tmp"
            docs = m_WordApp.Documents
            doc = docs.Add(Template:=graph_template_full_name.ToString(), Visible:=False)
    
            inline_shape = doc.InlineShapes.Item(1)
            inline_shape.OLEFormat.DoVerb(Word.WdOLEVerb.wdOLEVerbHide)
            work_book = CType(inline_shape.OLEFormat.Object, Excel.Workbook)
    
            inline_shape.Width = doc.PageSetup.PageWidth - doc.PageSetup.RightMargin - doc.PageSetup.LeftMargin
            inline_shape.Height = inline_shape.Height + 1
    
            doc.SaveAs(temporary_file.ToString())
    
            inline_shape.Select()
            doc.Application.Selection.Copy()
            current_selection.PasteSpecial(Link:=False, DataType:=Word.WdPasteDataType.wdPasteOLEObject, _
                     Placement:=Word.WdOLEPlacement.wdInLine, DisplayAsIcon:=False)
    
            doc.Close(SaveChanges:=Word.WdSaveOptions.wdDoNotSaveChanges, RouteDocument:=False)
    
            CleanUp(work_book)
            CleanUp(current_selection)
            CleanUp(inline_shape)
            CleanUp(doc)
            CleanUp(docs)
    
            Clipboard.SetDataObject("")
            GC.WaitForPendingFinalizers()
            GC.Collect()
    
            File.Delete("C:\ProgramData\Test\Graph Templates.tmp")
        End Sub
    
        Sub CleanUp(ByVal obj As Object)
            If (obj Is Nothing) Then
                Exit Sub
            End If
    
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            Catch
            Finally
                obj = Nothing
            End Try
        End Sub
    

    Friday, May 9, 2014 8:16 PM

All replies

  • Hi Terry,

    Based on the description, you want to resize an Excel chart in Word document.

    >> The result is a graph that is only a fraction of the height of the original graph.  <<

    Did you mean that resizing width of the chart was not working? I resized the Excel chart in Word 2013 like code below:

    Sub ResizeChart()
    
    '    ActiveDocument.InlineShapes(1).Height = ActiveDocument.InlineShapes(1).Height / 2
    
    '    ActiveDocument.InlineShapes(1).Width = ActiveDocument.InlineShapes(1).Width / 2
    
    ActiveDocument.InlineShapes(1).Height = ActiveDocument.InlineShapes(1).Height * 2
    
    ActiveDocument.InlineShapes(1).Width = ActiveDocument.InlineShapes(1).Width * 2
    
    End Sub
    

    The code works well for me. I suggest you getting the original width before you resizing it. And set a different value then see whether it could be changed.

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 12, 2014 7:01 AM
    Moderator
  • Hi Terry

    Which version of Word are you working with? What's the "currentSelection"?

    Have you checked the LINE SPACING of the target Range where the graph is being pasted? If that's set to an "exact" height then an InlineShape object will not be able to "grow" outside that setting? That's one very obvious factor that could be causing the graph to appear differently in the target document.

    Or are you pasting into something, such as a table cell or text box that could be limiting the size?


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, May 12, 2014 1:42 PM
    Moderator
  • We support all versions of Office back to 2003, so all of our "template" documents are in Office 2003 format.  So, the source, the doc file that contains the chart being copied, is 2003.  I am adding/verifying support for Office 2013, which led me to this problem.  In the above code sample, the target of the PasteSpecial() is a Word 2013 document.  That seems to be the root of the problem, copying a 2003 chart and pasting it into a 2013 document.

    In the above code, current_selection is empty.  It's just the cursor location where the user wants to insert the new chart.

    Line spacing is set to the default of "single".


    Monday, May 12, 2014 2:26 PM
  • Is there some way I can get my account "verified" so I can post images and links?
    Monday, May 12, 2014 2:28 PM
  • Hi Terry

    The selection has to be SOMETHING, it can't be "empty". I'll assume from your answer it's a regular paragraph, not in any kind of special "thing".

    Next question: If you imitate the actions (open the document with the chart, copy, paste special into the target document) do you get the same result as with your code, or a different one?

    I notice that you're using OLEObject as the paste data type. What kind of program generated these charts? What other selections do you see in the Paste Special list? Do any of them yield the correct/expected result?


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, May 12, 2014 2:31 PM
    Moderator
  • I tried a copy/paste operation in Word and the chart looked OK after pasting.  I changed the code to call Paste() instead of PasteSpecial() and it displays OK after that.  This is not my code - I inherited it, so I'm not sure where the need for PasteSpecial() came from.  The source of the chart is Excel 2003.

    What is the default type when just calling Paste() rather than specifying OLEObject?

    Monday, May 12, 2014 3:54 PM
  • Could you elaborate on what you mean by "What other selections do you see in the Paste Special list?".  (Sorry, I'm very new to Office development.)
    Monday, May 12, 2014 4:10 PM
  • Hi Terry

    If you look at the very left of the Word "Home" tab you should see a big Paste button. At the bottom of the button is an arrow that displays a menu. One entry is the menu is "Paste Special". Selecting that will display a dialog box with options for pasting, including the formats that Word and the Clipboard have in common for the type of data stored on the Clipboard.

    As you may know, OLE = Object Linking and Embedding. This requires a host and a client application. Word, in this case, is the client application - the consumer; the host application is an unknown (at least, for me).

    Usually, a chart in Word will have been generated using Excel's charting engine. The charting engine changed in Office 2007. Prior to that, Word might have used the Excel tools or MS Graph to generate the chart. Both use the same engine, but the information stored in the object will differ. If it's an OLE object, it will be looking for a different set of APIs in order to allow you to edit the chart.

    It's possible that this set of APIs hasn't been installed on the 2013 machine (but they'd be available on the installation medium for reasons of backwards compatibility). If the host application cannot be found, inserting as an OLE object can have "unexpected" results as Word simply does not know what to do with the object (no information is available).

    If the object does not need to be edited in the target document you're probably best off pasting as a PICTURE.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, May 12, 2014 4:31 PM
    Moderator
  • I see - I thought you were talking about in the code.  

    The Paste Special dialog shows

    Source: Microsoft Excel 97-2003 Worksheet
                 Microsoft Word 97-2003 Document

    Paste options are: 

    Microsoft Excel 97-2003 Worksheet Object
    Formatted Text (RTF)
    Bitmap
    Picture (Enhanced Metafile)
    HTML Format (this is the one that is selected by default)
    GIF
    PNG
    JPEG

    Unfortunately, pasting as an image isn't an option.  This document is intended to be opened by another process which updates the Excel charts with data from medical studies recorded by yet another application.

    Monday, May 12, 2014 4:44 PM
  • HI Terry

    With Word, the object model follows the UI very closely. So if you have a problem like this it's usually simpler to solve it in the UI first, then transfer that to code...

    So, if you use the first option, for a Worksheet object, what happens?

    The HTML format, while it may look fine, isn't going to be editable by the other process. Ideally, you should update everything for Office 2007 and later to use the new charting engine. That would allow both you and the other process to embed the information without needing to automate the Word application, using the new file format Office Open XML.


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, May 12, 2014 4:59 PM
    Moderator
  • The pasted chart displays properly for all Paste Special types except the Excel Worksheet option.

    I also discovered that if I right-click on the chart in the source document and select "Chart Object->Edit", that alone causes the height of the chart to collapse.  It then looks just like it does when pasting it in.

    Thanks for helping, Cindy!  Hopefully you can help me get to the bottom of this.

    Monday, May 12, 2014 6:44 PM
  • As a test, I converted the embedded Excel worksheet to Office 2013 format and saved it off as a .xlsx file.  Opened it in Excel 2013, selected and copied the worksheet and then pasted it into the Word document and it still displayed incorrectly.  Although, now that I think about it, the Word document was still a .doc file open in compatibility mode, so maybe it wasn't a valid test.

    I have the source document available here: https://dl.dropboxusercontent.com/u/17010956/Office2013/Graph%20Templates.doc

    if you want to take a look at it.  (Sorry, I still can't post links)

    Update:  Still happens if I PasteSpecial into a new, blank Word 2013 document.  So, it appears there is something with the chart itself that is making Word 2013 unhappy.
    Monday, May 12, 2014 9:40 PM
  • Hi Terry

    Well, at this point this actually becomes a question for an end-user forum where you'll find a lot more people who may have experience with this problem.

    From my POV...

    If you double-click the chart are you able to edit it? I'm guessing not - that the charting engine (OLE Server) is not installed on the machine.

    FWIW I am able to double-click and activate the chart and can see that Word sees it as an "MS Graph" chart.

    If that's not the case with you, then I recommend running a "custom install" (change the installation via control panel) and making sure the components for MS Graph are installed on the machine.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, May 13, 2014 10:24 AM
    Moderator
  • If I double-click on it, the chart collapses vertically and I get a tab on the bottom that says "Chart1".  It appears to be Excel 2013.  In TaskManager I can see the Excel.exe process open and if I right-click and select "Open File Location" it opens the Office15 folder. How can I tell what Word sees the chart as? 
    Tuesday, May 13, 2014 2:01 PM
  • Hi Terry

    You're really getting outside my area of experience, here, and you may need to take this to

    http://answers.microsoft.com/en-us/office/forum/word

    However, I'd say that Office 2013 canNOT find MS Graph, as I surmised, and is thus trying to convert to the newer graphics engine, which is an issue because these are *.doc files...

    My recommendation is still to try installing MS Graph on the machine and see if that makes a difference.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, May 13, 2014 2:59 PM
    Moderator
  • I just checked and I do have Microsoft Graph installed.  

    For what it's worth, we found if we completely rebuild the charts by hand, embed it in Word and then save it as a .doc file (Office 2003 format), we don't see the issue. 

    I'll try posting to the Word forum as you suggest.

    Thanks,
    Terry

    Wednesday, May 14, 2014 10:19 PM
  • Hi Terry

    That's interesting. My first reaction to this would be "document corruption", but the fact that I don't see the problem behavior on my installation doesn't support that...


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, May 16, 2014 2:36 PM
    Moderator