Use VBA to copy an Excel range into Word and resize

kkincaid

New Member
Joined
Mar 31, 2005
Messages
7
Hello Everyone,

I've been working on this one for the past few days. I've found some useful threads here but I'm still stuck.

I'm copying a range of cells from excel and pasting into the active word document as an enhanced metafile. I would like to be able to resize the pasted picture in Word with the excel macro.

I've tried to use .selection.InLineShapes(1).height = 577 but keep getting an error message "Run-time error '5941':
The requested member of the collection does not exist."

I have the Word Object Library referenced. My code is as follows:

Sub PasteToWord()

Dim WDapp As Word.Application
Dim WDdoc As Word.Document

Set WDapp = GetObject(, "word.application")
Set WDdoc = WDapp.ActiveDocument

Sheets("TDS 2000").Select
ActiveSheet.Range("a1:h65").Copy
With WDapp
.Selection.PasteSpecial link:=False, DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False
.Selection.InlineShapes(1).Height = 577 '<----- error on this line
.Selection.InsertBreak Type:=wdPageBreak
End With
end sub

I'm assuming that the error is coming from the InLineShapes(1). I don't know if that is referring to the right object. I was hoping I could resize while the object was selected in Word.

Any help is very much appreciated.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
This sort of thing is usually specific to the contents of your personal document. Difficult to answer. Did you record a macro of the import to get the code ?

Try temporarily commenting out that line to see if everything else works.

Record a macro to reset that height. See what code you get.

That error message is pretty specific suggesting that the shape does not exist. At least Word cannot find it.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top