VB automated pasting of Excel range into Word document

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

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/
_______

ardvk wrote:

> 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
>

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" wrote:

> 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/
> _______
>
> ardvk wrote:
>
> > 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
> >
>
>

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/
_______


ardvk wrote:

> 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" wrote:
>
>
>>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/
>>_______
>>
>>ardvk wrote:
>>
>>
>>>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
>>>
>>
>>