0
votes

I'm trying to copy the values from a named range in Excel to a bookmark in Word. I found this code on the web that does it in Excel VBA, but I'm getting an Error 13. Set pappWord = CreateObject("Word.Application")

Set docWord = pappWord.Documents.Add(Path)

 'Loop through names in the activeworkbook
For Each xlName In wb.Names
     'if xlName's name is existing in document then put the value in place of the bookmark
    If docWord.Bookmarks.Exists(xlName.Name) Then
        docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
    End If
Next xlName

 'Activate word and display document
With pappWord
    .Visible = True
    .ActiveWindow.WindowState = 0
    .Activate
End With

I know that the line that is causing the error is:

docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)

What am i doing wrong? Also, how & where would I code so that I can export the doc to PDF?

Thanks in advance.

Note: I've already selected the reference to the Microsoft Word (version number 14) Object model in Excel

2

2 Answers

1
votes

so I use it to accomplish this task but taking an image from formatted Excel table.

Sub FromExcelToWord()
Dim rg As Range

For Each xlName In wb.Names
    If docWord.Bookmarks.Exists(xlName.Name) Then

        Set rg = Range(xlName.Value)

        rg.Copy

        docWord.ActiveWindow.Selection.Goto what:=-1, Name:=xlName.Name

        docWord.ActiveWindow.Selection.PasteSpecial link:=False, DataType:=wdPasteEnhancedMetafile, Placement:= _
                                0, DisplayAsIcon:=False

    End If
Next xlName

End Sub
1
votes

Just curious... Why are you adding a document rather than opening the relevant doc which has the bookmarks? Try this code (I usually test the code before posting but I haven't tested this particular code. Just quickly wrote it)

Also I am using Late Binding so no reference to the Word Object Library is required.

Sub Sample()
    Dim wb As Workbook
    Dim pappWord As Object, docWord As Object
    Dim FlName As String
    Dim xlName As Name

    FlName = "C:\MyDoc.Doc" '<~~ Name of the file which has bookmarks

    '~~> Establish an Word application object
    On Error Resume Next
    Set pappWord = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
        Set pappWord = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0

    Set docWord = pappWord.Documents.Open(FlName)

    Set wb = ActiveWorkbook

    For Each xlName In wb.Names
         'if xlName's name is existing in document then put the value in place of the bookmark
        If docWord.Bookmarks.Exists(xlName.Name) Then
            docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName).Value
        End If
    Next xlName

     'Activate word and display document
    With pappWord
        .Visible = True
        .ActiveWindow.WindowState = 0
        .Activate
    End With
End Sub

EDIT

Changed

Range(xlName.Value) 

to

Range(xlName).Value

Now the above code is TRIED AND TESTED :)