Copy Value in Excel to Word (Excel 2003)

Topic

New Reply

Hi

I have this macro which supposed to copy the specific cell’s value to certain location in Word, but it doesn’t seem
to be working as expected. The “xlvalue2” and “xlvalue3” doesn’t seem to be paste into the Word at all.
There is no error message. I am not sure what goes wrong here. Pls assist what need to be change in the
following codes.

Sub exceltoword()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bmk As Word.Bookmark
Set doc = objWord.Documents.Open(“C:My DocumentsMyfile.doc”)
doc.Bookmarks(“xlvalue1”).Range.Text = Range(“A1”).Value
doc.Bookmarks(“xlvalue2”).Range.Text = Range(“N3”).Value
doc.Bookmarks(“xlvalue3”).Range.Text = Range(“A7”).Value
objWord.Visible = True

End Sub

TIA.

regards, francis

Reply | Quote

Replies

The code itself is OK.
Are you sure that the target document contains bookmarks xlvalue2 and xlvalue3?
Are you sure that cells N3 and A7 contain a non-blank value?

Reply | Quote

Hi Hans

You are right, the bookmarks are not there. Thank for pointing this out.

If there is an instance of Word already open, running the macro will produce 2 message stating that
‘ Word cannot save this file because it is open elswhere’ which I need to click OK twice.

Is there a way not to show this message box?

Further, I understand that I can use Link in Word to the cell that I want, and the Word .doc will update every time if the
cell value change. Is there such a feature in Word and how do I do it.

Thanks

Regards, francis

Reply | Quote

1) Instead of

Dim objWord As New Word.Application

you can use

Dim objWord As Word.Application

On Error Resume Next

Set objWord = GetObject(, “Word.Application”)
If objWord Is Nothing Then
Set objWord = CreateObject(“Word.Application”)
If objWord Is Nothing Then
MsgBox “Can’t start Word.”, vbCritical
End If
End If

On Error GoTo 0 ‘ or to an error handler if you have one

2) You can create a link to one or more cells as follows:
– Select the cell(s) in Excel.
– Copy them to the clipboard (select Edit | Copy or press Ctrl+C).
– Switch to the Word document.
– Place the insertion point where you want the link.
– Select Edit | Paste Special…
– Click Paste Link…
– Select an option in the list of formats.
– Click OK.

Reply | Quote

Hi Hans

The macro work great. I have multiple Word templates ( approximately 12 ) in the folder. Eg. Myfile, Hisfile, Herfile……
Do I need to create 12 macro to do this or can I do this in a macro? The cell’s positions and its values and for each Word doc are different and the location of the bookmarks are also different.

Thanks and thanks for the tip on the Linkage

regards, francis

Reply | Quote

You can create a Word.Application object once, and open the documents in that instance. You’d probably want to save and close each document after it has been processed.

Reply | Quote

Hans

apology, I am lost here. How do I open all the necessary documents at once? and having open all these documents, how
do I codes the bookmarks given that each document will have different values fron the cells and different locations of bookmarks.

need your help here.

thanks

regards, francis

Reply | Quote

You shouldn’t open all documents at once, open them one by one.

An you’ll have to write out the code for each document separately to specify the cell locations.

Reply | Quote

Hi Hans

Thanks, I will try to do this within a macro ans will seek your guide if I encounter any problem.

regards, francis

Reply | Quote