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

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

Hi Hans,

I get the following error:

Run-time error '5941':

The requested member of the selection does not exist.

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hi Hans,

I get the following error:

Run-time error '5941':

The requested member of the selection does not exist.

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

Reply | Quote

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

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

Sub PasteAsMetafile()
    Selection.PasteSpecial DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine
   '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)
End Sub

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Sub PasteAsMetafile()
    Selection.PasteSpecial DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine
   '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)
End Sub

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

Reply | Quote

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)

Reply | Quote