Copy from Excel to bookmarks in Word

[ovemal]

Hello
I'm using the macro below, but I can't get it working the way I
like it. (A macro I found on the net) The first problem happens while
paste in the name bookmark, there it paste the Excel cell with text and
not only the text. Why?
And why do I sometimes have to start the macro 2 times before the word
file will open?

Sub TilMerke()
Dim appWrd As Object
Dim objDoc As Object
Dim FilePath As String
Dim FileName As String
Dim LastRow As Long
Dim Prompt As String
Dim Title As String
'Turn some stuff off while the macro is running
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'Assign the Word file path and name to variables
FilePath = ThisWorkbook.Path
FileName = "WorkWithExcel.doc"
'Determine the last row of data for our loop
LastRow = Sheets("Sykemeldinger").Range("A65536").End(xlUp).Row
'Copy the data from Thisworkbook
ThisWorkbook.Sheets("Sykemeldinger").Range("aa17").Copy
'Create an instance of Word for us to use
Set appWrd = CreateObject("Word.Application")
'Open our specified Word file, On Error is used in case the file
is not there
On Error Resume Next
Set objDoc = appWrd.Documents.Open("C:\test\soknad2.doc")
On Error GoTo 0
'Tell Word to goto the bookmark assigned to the variable
BookMarkRange
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="kommune"
'Paste into Word
appWrd.Selection.Paste
'Copy the data from Thisworkbook
ThisWorkbook.Sheets("Sykemeldinger").Range("t17").Copy
'Tell Word to goto the bookmark assigned to the variable
BookMarkRange
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn"
'Paste into Word
appWrd.Selection.Paste
'Copy the data from Thisworkbook
ThisWorkbook.Sheets("Sykemeldinger").Range("w17").Copy
'Tell Word to goto the bookmark assigned to the variable
BookMarkRange
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="Fnummer"
'Paste into Word
appWrd.Selection.Paste
'Make our Word session visible
appWrd.Visible = True
'Clean up
Set appWrd = Nothing
Set objDoc = Nothing
End Sub

 

[Chris Marlow]

Hi,

Firstly I would early bind to the Word object model. You need to set a
reference through tools-references. You can then do things like;

Dim oWord as Word.Application
Set oWord=New Word.Application

A benefit of this over CreateObject is you can then use intellisense to
navigate the objects.

Regards,

Chris.

 

[ovemal]

Hi
thanks for you reply,
My problem is that I have 3 booksmarks, so I've done something rigth.
But in one of the bookmarks it copys differently than the 2 that works
fine. (It copys 2 cells with only the text and one with text and the
cell from excel.)



Regards
Ove Malde

 

[Chris Marlow]

Ove,

If you just want the text at the bookmark I would replace with something
like this;

objDoc.Range(objDoc.Bookmarks("kommune").Start).InsertAfter_
ThisWorkbook.Sheets("Sykemeldinger").Range("aa17")
objDoc.Range(objDoc.Bookmarks("navn").Start).InsertAfter _
ThisWorkbook.Sheets("Sykemeldinger").Range("t17")
objDoc.Range(objDoc.Bookmarks("Fnummer").Start).InsertAfter _
ThisWorkbook.Sheets("Sykemeldinger").Range("w17")

I've not had time to test ... let me know how it goes.

Regards,

Chris.

 

[ovemal]

hello
thanks again for you help.
I didn't get it working so I for the one with the problems I added this
ThisWorkbook.Sheets("Sykemeldinger").Range("t17").Copy
appWrd.Selection.Goto What:=wdGoToBookmark, Name:="navn"
appWrd.Selection.PasteSpecial , Text = " "
appWrd.Selection.TypeBackspace

regards
Ove Malde