How to Copying information from an excel sheet to a Word document

How to copy information from an excel sheet to a word document

Requirement:

The user is looking to open a word document, transfer information contained in cells from an excel spreadsheet to specific bookmarks in the word document.

Solution:

This has worked for me in the past You need to Excel Names that correspond with the Word Documents Bookmarks, i.e. they must have identical name.

Code:

Sub XlNamesToWdBookmarks()
    Dim objWord As Object, docWord As Object
    Dim wb As Excel.Workbook
    Dim xlName As Excel.Name
    Dim Path As String
    
    Set wb = ActiveWorkbook
    Path = wb.Path & "\MyForm.dot" 'change the name
    
    On Error GoTo ErrorHandler
    
     'Create a new Word Session
    Set objWord = CreateObject("Word.Application")
    
    On Error GoTo ErrorHandler
    
     'Open document in word
    Set docWord = objWord.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 objWord
        .Visible = True
        .ActiveWindow.WindowState = 0
        .Activate
    End With
    
     'Release the Word object to save memory and exit macro
ErrorExit:
    Set objWord = Nothing
    Exit Sub
    
     'Error Handling routine
ErrorHandler:
    If Err Then
        MsgBox "Error No: " & Err.Number & "; There is a problem"
        If Not objWord Is Nothing Then
            objWord.Quit False
        End If
        Resume ErrorExit
    End If
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by royUK.