VB automated pasting of Excel range into Word document

[Guest]

I'm trying to write VB code to paste several Excel ranges into a Word
document, as required by my employer.

I'm able to do this satisfactorily using manual cut and paste (Office 2003),
but when I automate it, I get "Run-time error 438: object doesn't support
this method". I get this error whether I use Selection. or a variable Dim as
Excel.Range or Word.Range or Object. I also get the error regardless of the
type of paste I use. Pasted images aren't acceptable because the range may
span several pages.

Below is a code example. The errant command is flagged between '*****
comments. Otherwise, the code runs as designed. The GetWordApp is from a
previous post that I found useful (thanks!)

I would appreciate any help you can give.

Thanks,

Dave in Madison

Sub PrintToWordFile()

Const fname = "C:\Test.doc"
Const rname = "EntireBudget"

Dim a As Excel.Range ' print range
Dim g As Boolean ' gridlines flag
Dim o As Object ' word application
Dim w As Word.Document ' document within word application

g = ActiveWindow.DisplayGridlines
ActiveWindow.DisplayGridlines = False
Set a = Range(rname)
a.Copy

GetWordApp o
With o
.Visible = True
.Documents.Open Filename:=fname, ReadOnly:=False
Set w = .Documents(fname)
End With

'*****

a.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=True

'*****

w.SaveAs "C:\Test.doc"
o.Application.Quit

ActiveWindow.DisplayGridlines = g
Set w = Nothing
Set o = Nothing
End Sub

 

[Jon Peltier]

Dave -

Looks like you're trying to paste the range onto itself. Rather than the Excel range
variable a in a.PasteExcelTable, you need a Word range or selection variable.
Incidentally, I've never consciously used PasteExcelTable, just Paste or PasteSpecial.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Guest]

Neither Selection.Paste nor Selection.PasteSpecial nor
Selection.PasteExcelTable work. But when I do the cut and paste manually, it
works. When I record a macro as I'm doing the paste, the recorded macro
reports Selection.PasteExcelTable. To me, it looks like the macro has trouble
converting the Excel range to a Word range, but the manual cut and paste
doesn't have this problem.

BTW, I have the following references available (in order): VBA, MS Excel
11.0 Object Library, OLE Automation, MS Office 11.0 Object Library, and MS
Word 11.0 Object Library

Any more thoughts?

Thanks,

Dave

 

[Jon Peltier]

Dave -

Here is a short excerpt from a current project of mine, which works very nicely.

Notes:
rngRange: the worksheet range in Excel
WdRng: a Word range object, but Selection should work as well

The Excel range is copied, and pasted as an inline RTF table.

' COPY RANGE
rngRange.Copy

' PASTE TABLE
WdRng.PasteSpecial Link:=False, DataType:=1, Placement:=0, _
DisplayAsIcon:=False
'' 1 = wdPasteRTF, 0 = wdInLine

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______