Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking (Office 2K SR-1)

    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

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Linking (Office 2K SR-1)

    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.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking (Office 2K SR-1)

    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.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Linking (Office 2K SR-1)

    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.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking (Office 2K SR-1)

    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.

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Linking (Office 2K SR-1)

    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).
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking (Office 2K SR-1)

    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.
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Nov 2001
    Location
    Exeter, New Hampshire, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking (Office 2K SR-1)

    Here's the Excel file.
    Attached Files Attached Files

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Linking (Office 2K SR-1)

    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
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •