Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation for pasting from clipboard (2000 / SR-1)

    Hi All,

    I regularly create reports in Word which contain several charts from Excel. My preferred method of putting the charts into the reports is as follows:

    1) First, I always create each chart on a separate worksheet in Excel. I then copy the chart to the clipboard.
    2) In Word, I choose, Paste Special and then select "Picture (Enhanced Metafile)" as my preferred pasting method.

    Here's the rub. EVERY time I paste in a chart picture this way, it always get inserted as a floating object and it's huge (in other words, beyond the page margins). I then have to select the graphic, choose Format Picture, change the layout to "Inline with text" and then reduce the size to a width of 6.5" to fit within the report template. This process is really annoying, because each time I paste in the next chart, it bumps ahead of the previous chart instead of behind it.

    Is there anyway of automating the paste process in such a way that it will always size the picture correctly (6.5" wide) and always inline with text?

    I have done some VBA work in Excel, but each time it is a struggle getting it right. I've never done any VBA work in Word, and I am assuming that some VBA will be necessary to accomplish my task.

    Any help that someone can provide would be MOST appreciated!

    Thanks in advance,

    Drew

    UPDATE -------

    I just found the following post concerning this very same subject:
    Pasting Metafiles
    However, when I tried the macro provided by Hans, it didn't work for me either.

    Drew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation for pasting from clipboard (2000 / SR-1)

    Something like this:

    Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile, Placement:=wdInLine
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.InlineShapes(1).LockAspectRatio = msoTrue
    Selection.InlineShapes(1).Width = InchesToPoints(6.5)

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation for pasting from clipboard (2000 / SR-1)

    Hi Hans,

    I get the following error:

    <pre>Run-time error '5941':

    The requested member of the selection does not exist.
    </pre>


    What results is still a floating image. I even tried the other code you provided for another user in the same regard.

    Any further insights?

    Drew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation for pasting from clipboard (2000 / SR-1)

    The only explanation I can think of is that I'm using Word 2002. The code works OK there. Perhaps someone else with Office 2000 has a better idea.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation for pasting from clipboard (2000 / SR-1)

    It is probable that wdPasteEnhancedMetafile is new in Word 2002, it has a higher numeric value than most of the other constants for DataType. Glad you found a solution.

  6. #6
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation for pasting from clipboard (2000 / SR-1)

    Hi Hans,

    What's really odd is that the wdPasteEnhancedMetafile method is in fact listed in Word 2000 as a viable option. However, when that method was used, the other options (re: wdInline, etc. ) did not have any effect.

    Anyway, I got exactly what I was looking for now.

    THANKS AGAIN!

    Drew

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation for pasting from clipboard (2000 /

    Edited by Phil Rabichow to reduce size of pretagged code to prevent scrolling
    Hans,

    Here's what ended up working for me!!!

    <pre>Sub PasteAsMetafile()
    Selection.PasteSpecial DataType:=wdPasteMetafilePicture, _
    Placement:=wdInLine
    <font color=448800>'Selection.PasteSpecial DataType:=wdPasteEnhancedMetafile, _
    Placement:=wdInLine</font color=448800>
    Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    Selection.InlineShapes(1).LockAspectRatio = msoTrue
    Selection.InlineShapes(1).Width = InchesToPoints(6.5)
    End Sub
    </pre>


    Take note of the difference between the first line of code and the commented line below it. I simply tried using "MetafilePicture" instead of "EnhancedMetafile". Now, even the last two lines of code work and the picture is sized correctly!!

    Thanks for your help. Your exact code may not have worked right the first time, but it gave me something to work with. That was still a big help.

    Drew

Posting Permissions

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