Copy/Paste Excel Cell Range to Word Bookmark
 

Hi all.

I am basically looking use my macro to copy a range of cells in excel and
paste them to a bookmarked spot in a word document that my macro opens. I
would like to retain the formatting of the range of cells (so that i am,
effectively, pasting a formatted table into word). I cannot seem to figure
out how to do this, however.

I am using Office 2k3

Copy/Paste Excel Cell Range to Word Bookmark
 

I would try the word groups, as they have much more expertise in that field.

But why not try the macro recorder and so how far you get.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Dan Thorman" wrote:

Hi all.

I am basically looking use my macro to copy a range of cells in excel and
paste them to a bookmarked spot in a word document that my macro opens. I
would like to retain the formatting of the range of cells (so that i am,
effectively, pasting a formatted table into word). I cannot seem to figure
out how to do this, however.

I am using Office 2k3

Copy/Paste Excel Cell Range to Word Bookmark
 

Hi Dan,

Here's some code to get you started:

Sub SendRangeToDoc()
Dim wdApp As Word.Application
Dim WdDoc As String
'Copy range
ActiveWorkbook.Sheets(1).Range("A1:J10").Copy
'Establish link to Word
WdDoc = "C:\My Documents\MyFile.doc"
If Dir(WdDoc) < "" Then
Set wdApp = New Word.Application
wdApp.Visible = True
With wdApp
'open the Word Document
Documents.Open Filename:=WdDoc
With wdApp
Dim BmkNm As String
BmkNm = "xlTbl"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
.Bookmarks(BmkNm).Range.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
.Save
Else
MsgBox "Bookmark: " & BmkNm & " not found."
End If
End With
End With
End With
Else
MsgBox "File: " & WdDoc & " not found."
End If
'Release Word object
Set wdApp = Nothing
End Sub

However, depending on what you're trying to achieve, you may not need a macro at all. Word is quite capable of linking to a
specified range in an Excel workbook, via a LINK field. With this, the embedded object in Word will update to reflect the current
data in Excel. If your data range in Excel is named, and you use that name in the LINK field, then changing the range the name
applies to in Excel will cause Word to display the new range.

Cheers

--
macropod
[MVP - Microsoft Word]


"Dan Thorman" wrote in message ...
| Hi all.
|
| I am basically looking use my macro to copy a range of cells in excel and
| paste them to a bookmarked spot in a word document that my macro opens. I
| would like to retain the formatting of the range of cells (so that i am,
| effectively, pasting a formatted table into word). I cannot seem to figure
| out how to do this, however.
|
| I am using Office 2k3

Copy/Paste Excel Cell Range to Word Bookmark
 

Thanks!!! This line was exactly what i needed:

With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
..Bookmarks(BmkNm).Range.PasteSpecial Link:=False,
DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False

"macropod" wrote:

Hi Dan,

Here's some code to get you started:

Sub SendRangeToDoc()
Dim wdApp As Word.Application
Dim WdDoc As String
'Copy range
ActiveWorkbook.Sheets(1).Range("A1:J10").Copy
'Establish link to Word
WdDoc = "C:\My Documents\MyFile.doc"
If Dir(WdDoc) < "" Then
Set wdApp = New Word.Application
wdApp.Visible = True
With wdApp
'open the Word Document
Documents.Open Filename:=WdDoc
With wdApp
Dim BmkNm As String
BmkNm = "xlTbl"
With ActiveDocument
If .Bookmarks.Exists(BmkNm) Then
.Bookmarks(BmkNm).Range.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
.Save
Else
MsgBox "Bookmark: " & BmkNm & " not found."
End If
End With
End With
End With
Else
MsgBox "File: " & WdDoc & " not found."
End If
'Release Word object
Set wdApp = Nothing
End Sub

However, depending on what you're trying to achieve, you may not need a macro at all. Word is quite capable of linking to a
specified range in an Excel workbook, via a LINK field. With this, the embedded object in Word will update to reflect the current
data in Excel. If your data range in Excel is named, and you use that name in the LINK field, then changing the range the name
applies to in Excel will cause Word to display the new range.

Cheers

--
macropod
[MVP - Microsoft Word]


"Dan Thorman" wrote in message ...
| Hi all.
|
| I am basically looking use my macro to copy a range of cells in excel and
| paste them to a bookmarked spot in a word document that my macro opens. I
| would like to retain the formatting of the range of cells (so that i am,
| effectively, pasting a formatted table into word). I cannot seem to figure
| out how to do this, however.
|
| I am using Office 2k3