asked on

vba copy excel range to bookmark in word

Hi,

Can anyone help me with some vba code to copy and excel range to bookmark in word?

I have the following but it xthrows a runtime error at ".Bookmark("Paste_table").Selection.Paste"

Private Sub WordExpcmd_Click()
Dim appWord As Object, docWord As Object
Dim lngRow As Long, lngRowCount As Long
Set appWord = CreateObject("Word.Application")
Set docWord = appWord.Documents.Open("C:\Documents and Settings\taqdjo\Desktop\Main work tree\Statistical reports\Weekly Manually created reports\test delete.doc")
Range("A1:G98").Select
With docWord
.Bookmark("Paste_table").Selection.Paste
.Close
End With
Set docWord = Nothing
appWord.Quit
Set appWord = Nothing
End Sub

Danny

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helen Feddema

The bookmark is part of the Bookmarks collection, so you need Bookmarks (not Bookmark) in the line that writes to the bookmark. You might also want to check that the bookmark exists before attempting to write to it, as in the code below:

strBookmark = "Address"
If doc.Bookmarks.Exists(strBookmark) = True Then
   .GoTo What:=wdGoToBookmark, Name:=strBookmark
   .TypeText Text:=strAddress
End If

Open in new window

Danny Osborne

ASKER

Hi Teylyn,

Thanks for this!! It kinda works, but I can only get one cell rather than the range I require to display in the word document. If I set the range as follows -

Set Paste_table = Sheet1.Range("A1")

It works fine. However, if i increase the range of cells as below, or even just to a range of a single row with 5 cells, I get a runtime error (13): type mismatch

Set Paste_table = Sheet1.Range("A1:G7")

Why might this be?

Danny

SOLUTION

sekrin