export excel coloumn to a word report


New Member
Jan 16, 2014
HI guys,

I want to export a excel column(Jointno.) to a word report(Template) which also has coloumn(Jointno.---> Bookmarked Joint).
both the excel and word has exactly 16 rows.

I just want to copy the 16 rows of data and paste it into the bookmarked location in word.

I have go the following Code:-
Sub Create_Letters()
' Note: you will need to add error-trapping
Dim objX As Object
Dim rng1 As Range
Dim rng2 As Range
Dim wb As Workbook
Dim wsControl As Worksheet
Dim wsData As Worksheet
Dim oApp As Word.Application
Dim oBookMark As Word.Bookmark
Dim oDoc As Word.Document
Dim strDocumentFolder As String
Dim strTemplate As String
strTemplate = "C:\Letter Creator\Word Templates\exp.docx"
Dim strTemplateFolder As String

Dim strWordDocumentName As String
Dim cell As Range

Set wb = ThisWorkbook
Set wsControl = wb.Worksheets("Control Sheet")
Set wsData = wb.Worksheets(wsControl.[Data_Sheet].Value)
strTemplateFolder = wsControl.[Template_Folder].Value
strDocumentFolder = wsControl.[Document_Folder].Value

' number of letters required:
' must not have any blank cells in column A - except at the end
Set rng1 = wsData.Range("d2:d17")

'Set oApp = CreateObject("Word Application")
Set oApp = New Word.Application
' Process each record in turn
For Each cell In rng1
    Set oDoc = oApp.Documents.Add
    oApp.Selection.InsertFile strTemplate
    ' locate each bookmark
    For Each oBookMark In oDoc.Bookmarks
        Set objX = wsData.Rows.Find(oBookMark.Name, LookIn:=xlValues, LookAt:=xlWhole)
        If Not objX Is Nothing Then
            ' found
            If Right(oBookMark.Name, 4) = "joint" Then
                    oBookMark.wdGoTo What:=wdGoToBookmark, Name:="joint"
                    With ActiveDocument.Bookmarks
                    .DefaultSorting = wdSortByName
                    .ShowHidden = False
                    End With
                    Selection.PasteAndFormat (wdPasteDefault)
                    Selection.Font.Bold = wdToggle
                    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
            End If
            MsgBox "Bookmark '" & oBookMark.Name & "' not found", vbOKOnly + vbCritical, "Error"
            GoTo Tidy_Exit
        End If
    Next oBookMark
    oDoc.SaveAs "C:\Letter Creator\Documents\exp1.docx"
Next cell
On Error Resume Next
Set oDoc = Nothing
Set oBookMark = Nothing
Set objX = Nothing
Set rng1 = Nothing
Set rng2 = Nothing
Set oApp = Nothing
Set wsData = Nothing
Set wsControl = Nothing
Set wb = Nothing
End Sub
Any help will be seriously appreciated.

Thanks in ADVANCE
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.


Retired Moderator
Aug 27, 2007
Is there a reason for not setting up a link between Word & Excel, so the Word document updates automatically? no code required.

Alternatively, depending on what you're trying to achieve, have you considered a mailmerge? Again, no code required.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back