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
Danny OsborneSenior IT and Technical Specialist, ConsultancyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy

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


errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

0
Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor 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?

Danny
0
sekrinCommented:
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...

Sheet1.Range("A1:G7").Copy
doc.Bookmarks(strBookmark).select
Selection.Paste
0
Danny OsborneSenior IT and Technical Specialist, ConsultancyAuthor Commented:
Thanks all, this is working now!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.