Can you write the contents of an Excel Named Range to Word?

[Anne]

Hi: I am looking for any assistance to write the contents of an Excel
Named Range to a Word doc at a book marked location. I have been able
to write code to write a single cell to a bookmark, but when I try to
write the contents of a range, I don't get any data, although I don't
get any errors either; just no text.

I am using code to create the named range and it seems to be set up
correctly in Excel after the routine runs.

I am wondering if my approach is wrong and I should just copy and
paste the data from Excel to Word instead, although I thought using a
named range might be more efficient instead of using the clipboard.

The data I am copying is not going to be more than probably 500 cells.

I have written a few macros, but still very much new to this. Any
help would be greatly appreciated!

Anne

 

[Barb Reinhardt]

It would help if you posted the code you already have.

 

[Anne]

Thanks Barb. I wanted to simplify what I was doing. I removed the
steps to set up the named range and look for book marks. I did find a
way to write the data from the named range to a new Word doc with this
code, although I noticed even with a simple example of 3 columns and 3
rows, it still takes a few seconds to copy the data from Excel to
Word.

My ultimate goal is to have a spreadsheet with several tabs of data,
used for tracking project information (budget, status, risks, etc.),
then I want to generate a Word status report from the information in
the workbook. Since to accomplish my final project, I will need to
copy several tables from Excel to the Word doc, I want to make sure I
am doing it efficiently in terms of memory usage.

Here is the code I have that just opens a new doc and pastes in a
Named Range that I have set up manual in Excel.

Any advice to improve the process to get the table and pull it into
Word would be much appreciated.
---------------------

Sub CopyXLSDataToWord()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document

' Create a Word document
Set WordApp = CreateObject("Word.Application")

' Make the newly created Word instance visible
WordApp.Visible = True

' Create a new document
Set WordDoc = WordApp.Documents.Add

' copy the range, assumes current excel active workbook
Range("TESTRANGE").Copy

' Paste as formatted text.
WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteRTF,
_
Placement:=wdInLine, DisplayAsIcon:=False

'This line also seems to work into paste the data - don't know if
one is better to use
'WordApp.Selection.PasteExcelTable False, False, True

' Clean up
Set WordDoc = Nothing
Set WordApp = Nothing
End Sub