[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1542
  • Last Modified:

vba copy excel range to bookmark in word


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")
         With docWord
         End With
         Set docWord = Nothing
   Set appWord = Nothing
End Sub

2 Solutions
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello DannyJOsborne,

see the code example below. It's not adapted to your variables, but it is a good example for how to copy several different items, including Charts, to Word. You should be able to see how it can work for your situation.

Important to note:

Dim your bookmarks as Excel. Range
Set the variable to the cell reference

Then use 'InsertAfter' to assign the variable value to the bookmark.

cheers, teylyn
Sub createWordReport()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim companyName As Excel.Range
Dim yearEnd As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Set myDoc = wdApp.Documents.Add(Template:="T:\Templates\test\Accounts Report.dot")

Set companyName = Sheets("Data").Range("A7")

Set yearEnd = Sheets("Data").Range("C7")

Worksheets(" Charts").ChartObjects("Chart 12").Activate

With myDoc.Bookmarks
.Item("CompanyName").Range.InsertAfter companyName
.Item("YearEnd").Range.InsertAfter yearEnd
End With

Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub

Open in new window

Helen FeddemaCommented:
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

DannyJOsborneAuthor Commented:
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?

The error is because TypeText is expecting a string value, and in the second example you're passing it a range.

I don't have Word and Excel handy at the moment, so I'm working from memory here, but try the following...

DannyJOsborneAuthor Commented:
Thanks all, this is working now!!

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now