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]

I may have misunderstood. You said that the copied workbook was labeled "corrupted" when you tried reopening it. So I wondered if making a copy in Windows Explorer (independent of Excel) also led to the corrupted file message. I don't know why I asked, sometimes the question and answer can suggest a solution.

I also misunderstood that your usual practice was to create a new report was to painstakingly construct the workbook piece by piece, like your current remediation process. For routine use, doing a Save As and inserting the new data should be reliable.

The corrective actions you're taking are sensible. I'd go one further and not make a template from a chart in the corrupted file. It means rebuilding charts in the new workbook, but if it's the chart or template that's hinky, it's safer that way.

 

[RSXchin]

You might even want to make a macro that will recreate the workbook...

 

[NigelTufnel]

Here's the latest update. It has turned into quite a soap opera. At this point, you may be interested just for entertainment value.

I did indeed put together a set of macros to re-create the workbook. The re-creation was successful...until I tried to save the workbook under a new name. Excel gave me the more errors about not being able to save the workbook correctly.

By process of elimination I traced the problem to one of the chart templates. There was a single template which, when excluded from the re-create routine, allowed me to save the workbook to a new name without any complaint.

Victory! (Or so I thought....)

So I proceed with my project, happy to have this ugly episode behind me. Then, after another hour or two of additional work to get my workbook back to where it was originally, I get the same error while saving the workbook.

AAAAAAARRRRRRRGGGGGGGHHHHHHHHH.

At this point, I figure I have to do a re-think of the entire situation. I just don't have this kind of time to waste, and I'm getting farther behind schedule with each passing day. I started to think that maybe the problem is not with workbook, but with my computer (error-prone hard drive?), or maybe Excel. Maybe I need to diagnose my hard drive, or repair my Excel installation...or perhaps disable my add-ins.

So...I decided to do what I should have done much earlier in the process, and that is to disable most of my Excel add-ins. The add-ins I disabled were:

--Andy Pope's Pattern Fill add-in (Just my luck, I'm using Excel 2007 and the client needs to use pattern fills in the charts!)

--Rob Bovey's XY Chart Labeler add-in

--ASAP Utilities

I re-ran the workbook creation utility and....it seemed to work fine.

I have now been using, manipulating, and programming the new workbook for many, many hours and I have not had a single issue with it since.

I'm inclined to conclude that the apparent corruption problems with my workbook were in fact some kind of incompatibility among the aforementioned add-ins and my workbook.

I don't have time now to track down which particular add-in is the culprit (if just one), but if I were a betting man, I'd probably wager on the Pattern Fill add-in. I've used it extensively on the project.

This is potentially worrisome, as I need to use patterns on most, if not all of my charts. Perhaps I can use the add-in and then un-install it before I try to save the file....

Of course, none of this has addressed the original problem I had...that being the problem with my charts not re-sizing properly in Microsoft Word when pasted.

Even after disabling the add-ins, I still had the picture pasting problem.

With both my patience and time wearing thin, I decided to try a different tack with that problem--treatment rather than prevention.

Now, for every chart I paste into the Word document, I set a bookmark. After I'm finished creating the entire Word document I go through the document, re-select each bookmark, and then re-size the corresponding chart. This seems to work, and I can move onto other issues.

If I diagnose anything else, I'll let you know.

Thanks to both of you for your interest, your patience, and assistance

P.S., Again Jon, sorry if my explanations are lacking, and doubly sorry if I don't understand you, but to be clear: I do not re-create the workbook each time I run the report (as I understand you thought from your last post). I have a workbook that contains all of the charts linked to the data for the current report. For each new report, I look up the new data in a database, paste the new data into the Excel workbook, and then copy the updated charts into Microsoft Word. I don't even need to save the workbook between reports.

 

[Jon Peltier]

Nigel -

That's and extensive and detailed troubleshooting effort. I can say I'm not surprised the problem is chart-related, but I would be surprised if the pattern add-in is the culprit. Or maybe I shouldn't say I'd be surprised, because this is Excel, after all. But the other two add-ins are potentially more intrusive.