Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Value in Excel to Word (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    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.

  9. #9
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Value in Excel to Word (Excel 2003)

    Hi Hans

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

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •