VBA Paste Excel Chart to Word--Unreliable Chart Sizes

[NigelTufnel]

I'm developing an application that pastes a large number of Excel 2007 charts into a Microsoft Word 2007 document (each chart replaces a specific Word bookmark). After pasting each Excel chart into Word, I execute a command to re-size the chart.

I'm having a frustrating time as the macro seems to re-size the chart correctly most of the time. However, there are numerous occasions where the chart does not get resized at all--not even a default re-sizing to the document margins that usually happens when you paste a large chart into a Word by hand.

And here's the kicker--the offending charts seem to size perfectly fine when I manually step through the code.

Has anyone experienced anything like this? Any ideas what I might try? I tried inserting a Excel VBA delay before and after pasting, but that didn't help.

For whatever it's worth, here's a rough cut of my code (dWidth is a variable that represents the desired width of the chart, about 6 inches on the page):

Sub InsertChart(ByVal sBookMark As String, ByVal sChartType As String, ByVal dWidth As Double)
  Sheets(sChartType).ChartArea.Copy
  wrdDoc.Bookmarks(sBookMark).Range.Select
  wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _
     Placement:=wdInLine, DisplayAsIcon:=False
  wrdApp.Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
  wrdApp.Selection.InlineShapes(1).LockAspectRatio = msoTrue
  wrdApp.Selection.InlineShapes(1).Width = Int(dWidth * iPointsPerInch)
End Sub

Thanks for any help you can provide me.

 

[Jon Peltier]

"[it works] perfectly fine when I manually step through the code."

This means you might get some satisfaction if you insert "DoEvents" between operations:

  wrdApp.Selection.InlineShapes(1).LockAspectRatio = msoTrue
  DoEvents
  wrdApp.Selection.InlineShapes(1).Width = Int(dWidth * iPointsPerInch)

What happens if you resize the chart object in Excel prior to copying it? I've usually felt that it's more reliable doing it this way.

 

[NigelTufnel]

Jon--

Thanks for your response and all your contributions to the Excel community. I've learned much from your website and postings.

First, to answer your question, some of my charts may be pasted into multiple documents, and may need to be sized differently. Consequently, I thought it would be easier for me to create them all in a standard size on separate Excel chart pages. (Frankly, sizing Excel charts confuses me. When I paste a standard Excel chart into Microsoft Word, it says the original picture width is over 15"--makes no sense to me.)

With respect to my sizing problem, I tried the DoEvents suggestion, but had no luck.

I then tried manually stepping through the macro again and got some problems. The chart paste code is in a subroutine, and immediately after Excel/Word executes that paste (yet before the re-size command) it exits the subroutine and returns to the Excel routine that calls it. There is no programmatic reason for it to do so. I fear that my workbook has become corrupt.

Then, shortly after this, my Excel session froze when I tried to relocate the offending chart to a worksheet. Fortunately, I saved my work prior to doing this. Unfortunately, when opening my saved workbook, Excel said there was some "unreadable" material in it.

It looks like I need to re-create this workbook. If you have any suggestions regarding the best method for doing so, I'd welcome them.

The workbook has 19 Worksheets, 75 Charts, and 162 defined names. It could be a challenge.

 

[RSXchin]

question.... Are all of these charts ones already in the word documents? Just older versions?

 

[Jon Peltier]

Nigel -

In terms of the charts themselves, it really doesn't matter where they get resized. You could resize the chart in Excel, copy and paste into Word, resize it back to the standard size.

If the chartswere originally created in 2003 or earlier and now you're working in 2007 or later, it is not uncommon to have workbook instabilities. Recreating the charts is often enough.

rsxchin has a good point. He's going to suggest you paste in the chart but linked to the original in Excel. Then whenever they are updated, next month or whenever, the linked charts in Word will update. I think Word 2007 lets you paste as a metafile which is linked to the original chart (I think PowerPoint does not). Of course, if you circulate the Word doc, then it won't find the links on other people's computers. I guess after updating the charts, you can save the Word doc using another name, break all the links in the renamed document, and circulate that one, saving the one with links to be updated next month.

 

[RSXchin]

Powerpoint DOES allow linked charts. I worked with charts in powerpoint more than I care to talk about...

 

[Jon Peltier]

Of course PowerPoint allows linked charts which are charts, but not linked pictures (metafile/bitmap) of charts, which Word allows. That's the distinction I was trying to make.

 

[NigelTufnel]

Jon and rsxchin--

Thanks for your comments and suggestions.

A little background: I'm doing some work for a consulting company that recently developed and distributed a survey on behalf of a client. When the data returns, they need to dissect the data into 100 different groups and generate customized reports, which will be in Word, but ultimtely delivered to the client in PDF format.

Linking the charts is an intriguing idea, especially in the report development phase, where the client may decide to make changes to how the charts look. But it's probably not something that is required to develop the reports for the client, unless this technique fixes the chart sizing problem.

I created the original workbook and all the charts/macros in Excel 2007. The workbook is pretty straightforward, though it's got a couple of non-standard (but reasonable) characteristics, such as dynamically-linked chart axis titles and text boxes.

I've been working on re-creating the file using VBA in the following steps:

1) For each worksheet in the original workbook, create a new worksheet, and populate it with the the formulas and formats from the original (PasteSpecial)
2) Create new range names that have the same definition as the range names in the original workbook
3) Export chart templates for the original charts. Then, for each original chart, create a new blank chart in the new workbook, copy the series formulas, and apply the relevant chart template

Everything seems to work fine. That is, until after Step 3.

I can save the new workbook, but apparently, if I try to do a "Save As..." with the new workbook, I get a message saying that there is corruption in the new workbook that can't be fixed.

I'll continue to work on it. My guess is the corruption is in one of the chart templates.

Thanks again for your time and suggestions.

 

[Jon Peltier]

I was going to so suggest just copying the workbook, and populating the copy. All the copying of the pieces sounds complex, and a likely source of problems.

Do you get this corruption if you manually copied the workbook in Windows Explorer?
Is the workbook in 2003 or 2007 format? Was it ever a 2003 workbook?

I see a workbook reconstruction in your future.

 

[NigelTufnel]

Jon--

I'm not sure I understand what you mean by "...just copying the workbook, and populating the copy...."

I have a corrupt workbook, that is open-able and editable. I'm trying to reconstruct it using a fresh workbook, by copying elements that are as small/elemental as possible from the corrupt workbook each time, in an effort to avoid copying something that is itself corrupt. For example, I could simply copy charts wholesale, formatting and all, from the old workbook to the new workbook, but what if some aspect of the chart itself is corrupt (e.g., embedded links)? That's why I'm re-creating the charts by copying over only the series formulas, then the format template, etc. In my way of thinking, this is a safer way to copy.

But I'm open to other approaches...especially from someone with as much Excel experience as you.

It's possible that I inadvertently saved the initial versions of the workbook in Excel 2003 format, though the workbooks have never been edited using Excel 2003.