Strange problem running Word from Excel

[Guest]

I have a routine that opens an existing Word Doc, pastes (special) a metafile
picture from Excel, and attempts to modify the properties of the picture.
After the paste the picture is sized at 89%. The code is actually selecting
the pasted picture, but none of the methods have any effect on the size and
orientation of the picture. My goal is to size the picture to 100% and
center it in the Word doc. (Interestingly, when I paste an Enhanced
metafile, the picture is sized at 100%, but the result is a huge file.) The
code being executed from Excel, which follows, does work when executed from
Word!

Here is the guts of the code:

WordApp.Visible = True
On Error Resume Next
With WordApp.Selection
.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _
Placement:=1, DisplayAsIcon:=False
End With
ActiveDocument.Shapes.SelectAll
With Selection.ShapeRange
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
Selection.ShapeRange.Height = 606.25
Selection.ShapeRange.Width = 483.85
Selection.ShapeRange.Left = wdShapeCenter
End With
ActiveDocument.SaveAs Filename:=SaveDocName,
FileFormat:=wdFormatDocument, _
LockComments:=False, Password:="", AddToRecentFiles:=True,
WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False,
SaveAsAOCELetter:= _
False

 

[Guest]

' NOTE here you are using WordApp:
WordApp.Visible = True
On Error Resume Next
With WordApp.Selection
.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafilePicture, _
Placement:=1, DisplayAsIcon:=False
End With

' But below, you stop referring to WordApp
ActiveDocument.Shapes.SelectAll
With Selection.ShapeRange
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
...

Once you stop referring to WordApp, VBA assumes you are referring to the
active Application, i.e. Excel. The ActiveDocument actually errors (I am
guessing) because it is specific to Word; but your OnError statement will
supress the errors here and elsewhere. In other places, i.e. in referring to
Selection, VBA thinks you mean the Excel selection. But if you run the code
in Word, where the active Application is Word, it would work OK. If you use
WordApp consistently (i.e. WordApp.ActiveDocument, WordApp.Selection, ...), I
think the code will run OK.

 

[Guest]

I was having the problem earlier with the code activating the shapes on the
spreadsheet, but I resolved that. I step through the code to make sure that
the WordApp is selected, and the the shape in the doc is selected. To test
it I changed the code to reference WordApp.ActiveDocument, etc., and am still
having the same problem. I wonder if the code for the Word methods is
incompatible with Excel. I am using all 2000 versions.

 

[Guest]

I don't think compatibility should be an issue. When you use the Word
methods you are using the Word object library (.olb file) and the library
does not care what app is calling it. I still think it is necessary to use
WordApp before any Word properties or methods - it is not the Windows
"active" app that matters, it is the application that is actually running the
code that determines the "default" properties and methods of the Application
object. So without specifying, VBA will use the Excel methods, not Word.

The problem, then, is elsewhere in the code. Stepping through it will not
really resolve it since you still bypass error checking with the OnError Goto
Next statement. Without the OnError statement, does the code error out, and
if so where and what is the error message/code?

There is another thing I often do to test code: put a break point in right
after inserting the shape, then try the other lines one by one by typing them
in the immediate pane. You can try many variations of this or you can test
the object properties as you go to see what is really happening.

 

[Dick Kusleika]

Robin

I don't understand Word and its fascination with the Selection object, but
this works. You only need to get a proper reference to the shape. If you
know it's the only shape, the Shapes(1) will work. If not, you'll need to
count the shapes before you paste, then add 1.

The constant you used in the DataType argument didn't exist in my Word 9.0
Object Library.

Sub PastePicture()

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Add

wdApp.Visible = True

Sheet1.Pictures(1).Copy

wdApp.Selection.PasteSpecial link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=1, _
DisplayAsIcon:=False

With wdDoc.Shapes(1)
.RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
.Height = 606.25
.Width = 483.85
.Left = wdShapeCenter
End With

End Sub

 

[Guest]

Thanks to both Dick K and K Dales. It works!!