Word and Excel Macros

[gray78]

I currently have a word document in which I want to run a macro which will open a specific excel workbook, go to a specific worksheet, copy a range within that worksheet,then paste the copied range into the word document and then format it to fit the page in the word document.

The problem I am having is that once I save and close the files, I get an error the next time I try to run the macro. The current is posted below.

Any suggestions?

code:
Sub ot_18()
'
' ot_18 Macro
' Macro recorded 4/7/2006 by dkotcher
'
Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False
Selection.TypeBackspace
Selection.InlineShapes(1).Fill.Visible = msoFalse
Selection.InlineShapes(1).Fill.Solid
Selection.InlineShapes(1).Fill.Transparency = 0#
Selection.InlineShapes(1).Line.Weight = 0.75
Selection.InlineShapes(1).Line.Transparency = 0#
Selection.InlineShapes(1).Line.Visible = msoFalse
Selection.InlineShapes(1).LockAspectRatio = msoTrue
Selection.InlineShapes(1).Height = 337.7
Selection.InlineShapes(1).Width = 558#
Selection.InlineShapes(1).PictureFormat.Brightness = 0.5
Selection.InlineShapes(1).PictureFormat.Contrast = 0.5
Selection.InlineShapes(1).PictureFormat.ColorType = msoPictureAutomatic
Selection.InlineShapes(1).PictureFormat.CropLeft = 0#
Selection.InlineShapes(1).PictureFormat.CropRight = 0#
Selection.InlineShapes(1).PictureFormat.CropTop = 0#
Selection.InlineShapes(1).PictureFormat.CropBottom = 0#
End Sub

 

[PHV]

Any suggestions?
Yes, say us at least the minimum, i.e. which error message and where in the code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gray78]

When I run the macro I get the following error:
Run-time error '5342'
The specified data type is unavailable.

 

[xlbo]

....and on which line???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[gray78]

the error occurs at:

Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement _
:=wdInLine, DisplayAsIcon:=False

 

[PHV]

Seems that at the time the error raises the clipboard doesn't contain OLE object anymore ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gray78]

Thanks, now how can I correct the problem. I want to be able to run a macro from the word document which will open the excel workbook, go to the particular worksheet, highlight a range within that worksheet, copy the range of cells, go back to the word document, paste or paste special the copied cells into the word document, save the word document then close the excel workbook.

Do you think this is possible?

Thanks