Linking (Office 2K SR-1)

[Biffarama]

I have reports I do every month, I use Word for them. I use Excel to generate charts which I link into Word so that I can easily update them by just updating the data in Excel. Works great. But I have this one report which gives me a headache.

In this particular Word report I have two charts per page. I shrink them down to 70% so that they can both fit nicely together. When I start working on a new month's report I just save the previous month's Excel and Word files as the new month and update the links. Only problem is when I do this all the charts in the new Word doc go back to being sized 100% and I'm left with a mess moving them all back into place. Sometimes I even lose pages if some of the charts pile on top of each other.

Does anyone know of a way to fix this problem? Is there a step I'm missing that will keep my charts sized at 70% when updating from month to month?

Thanks in advance for any help,
Troy

[macropod]

You could try re-setting the charts to their desired sizes and positions via a macro. I've attached one that I've used. A couple of points about the macro-
1. you need to know the object Nos. that Word has assigned to them. You can find that out by using the macro recorder and selecting each chart.
2. You also need to know the required sizes (in points) and positions. Again use macro recorder as you do this manually.

Once you've done this, running your macro will restore the charts as you'd like them. While you're at though, you might want to use a macro to update the links too ... before restoring the charts.

PS: you may/may not want to use the 'lock anchor' function.

[Biffarama]

I appreciate the effort, but this appears to be a little over my head. I'm having trouble with the macro recorder part, mostly because I've never used it before.

[macropod]

OK, Here's how to go about it in Word 2000 (earlier versions may be a bit different):

1. Open the document and scroll to the page with your first chart.
2. From the menu, select Tools/Macro/Record New Macro.
3. Give the macro a name
4. Click on the chart.
5. Using the menu (Format/Object), position and size the chart as you want it to be.
6. Stop the macro recorder.

If you now go into Tools/Macro/Macros, select your macro and click on "Edit", you'll be able to see the code. The first line should read something like:
ActiveDocument.Shapes("Object 2").Select ,
where "Object 2" is the tag Word has assigned to your chart. Looking through the rest of the macro you'll find commands similar to the ones in the attachment to my last message for sizing the chart, like:
Selection.ShapeRange.Height = ####
Selection.ShapeRange.Width = ####
and positioning it, like:
Selection.ShapeRange.RelativeHorizontalPosition = wdRelativeHorizontalPositionColumn
Selection.ShapeRange.RelativeVerticalPosition = wdRelativeVerticalPositionPage
Selection.ShapeRange.Left = CentimetersToPoints(##)
Selection.ShapeRange.Top = CentimetersToPoints(##) ,
though the positioning commands will differ, depending on whether you're using absolute or relative positioning, and what the reference points are, and you may see "Inches ToPoints" instead of "CentimetersToPoints" if your not using metric.

Use the same process for the second chart (giving the 2nd macro a different name from the first one).

Delete the first "End Sub" the second "Sub" line and you're in business - once macro to resize & position both charts.

The code I gave you doesn't have a lot of the unnecessary stuff you get using macro recorder, and has statements like:
With Selection.ShapeRange
"CODE"
End With
instead of
Selection.ShapeRange
on each line. These measures speed the macro up and makes it easier to read & maintain.

[Biffarama]

I followed your instructions. Still having some trouble. When I created the macro the first line didn't look like: ActiveDocument.Shapes("Object 2").Select. The first line is: Selection.ShapeRange.Fill.Visible = msoFalse. I tried typing the line in myself but I can't seem to find the tag that Word assigned to the chart, so the line isn't working. Any more ideas?

I went through and recorded a macro for each chart and then deleted the "End Sub" and "Sub" lines so they would all work together.

Thanks again for your help. I'm sure when I've got it it will be a huge help for me.

[macropod]

I suspect you started recording your macro after you'd selected the chart. You need to start the macro recorder before selecting it.
If you could attach your document, I could have a look at it. Then I could give a clearer response. Strip out anything sensitive first, and remember to keep the file size under 100k (zip is OK for me too).

[Biffarama]

I tried it again and recording the macro worked better, but when I ran it the charts all came out resized but all over the place in the document. The placeholders didn't work. I've attached the Word file here and I'll follow with the linked Excel file because even zipped up they're over 100k together. I appreciate any help you can give me with it.

[Biffarama]

Here's the Excel file.

[macropod]

Here's an updated copy of your word file, with a couple of reformatting macros in-situ for you to experiment with.

The first macro (Reformat1) goes though selecting each chart in turn and resizing and positioning it. Although the most flexible, in terms of being able to individually manage each chart, doing so is unnecessary in this document because they