Solved: From excel goto bookmark in word

[jazzyt2u]

I tried looking through the boards for the following and couldn't get the answer.

From another post I tried the following to open word from excel and to go to a bookmark to then paste the data from excel. It all works except when I get to the bookmark.

I get a run-time error "5101":
The bookmark doesn't exist.
When I click on the word document and check, it's there....
I don't know if the coding is directing it to look in the correct place.

Please help.

BTW once I get it to go to the bookmark I plan to paste the data from excel.

Also how do I copy my coding into this forum like it looks in VBE? LOL :dunno


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/3/2008 by ISR
'
'
Dim wdApp As Object
Dim wdDoc As Object
Dim BMRange As Range
Range("B5").Select

Dim rngT10 As Range
Dim rngD10 As Range
Dim rngTD10 As Range

Set rngT10 = Selection
ActiveCell.Offset(2, 0).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 8).Select
Set rngD10 = Selection
Set rngTD10 = Range(rngT10, rngD10)

Set wdApp = CreateObject("Word.Application") 'Create an instance of word
wdApp.Application.Visible = True
Set wdDoc = wdApp.Documents.Open("S:\TashaA\TestAvon.doc") 'Open word file

rngTD10.Copy

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="PlaceHolder1")

Selection.PasteExcelTable False, False, False
Set wdDoc = Nothing
End Sub

Thanks,
jazzyt2u: pray2:

[MOS MASTER]

Hi & Welcome to VBAX jazzyt2u, :hi:


Also how do I copy my coding into this forum like it looks in VBE? LOL :dunno

When you create your Forum post you see on the Top right (editing toolbar) a green/white button (VBA)

Just select your code and press this button and the code will be formatted as per VBIDE. :yes

I've taken the liberty to fully rewrite your code, You could use something like this:

Option Explicit
Sub CopyActiveCellUsedRangeToMsWord()
Dim rUsedRange As Excel.Range
Dim oWord As Object
Dim oDoc As Object

Set rUsedRange = _
Range(ActiveCell, ActiveCell.SpecialCells(xlCellTypeLastCell))
rUsedRange.Copy

Set oWord = CreateObject("Word.Application") 'Create an instance of word
oWord.Visible = True
Set oDoc = oWord.Documents.Open("S:\TashaA\TestAvon.doc") 'Open word file

oDoc.Bookmarks("PlaceHolder1").Range.PasteExcelTable False, False, False

Set oDoc = Nothing
Set oWord = Nothing
End Sub

The code assumes that the bookmark: PlaceHolder1 exists in the document. (and you select the top left cell in your usedrange in Excel) ;)

HTH

[jazzyt2u]

Thank you so much. Where have you been all of my life. Well, programming life...LOL :bow:

Not only were you rocking with the bookmark code but you've saved me heart ache with selecting all cells from point of active cell...even with spaces.

Please recommend a book where I could get more coding like that...

[MOS MASTER]

Hi Jazzy, :hi:

You're most welcome... and I've been right here In Breda (NB / Netherlands) :rofl:

I wish I could recommend you a book that would teach you effective VBA coding across application boundries but I can't.

I've learned it from various things:
* Books
* Online content
* Answering other peoples problems (that gives you real live experience)
* And most of all: Just do it! a lot. :yes

But we do have a Book review forum:
http://vbaexpress.com/forum/forumdisplay.php?f=75

There you can read other people's experiences with books.

Happy coding and I'll see yah again.