Sunday, May 06, 2012

Update Word Document with Excel Information using VBA


Excel Range to Word Template using VBA

Most often we maintain list of contacts in Excel workbook and it needs to be transferred to Word document (made from some template). Here is a simple snippet that can help:

The code is used to copy the content from Excel range shown below to a Word document:

<><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><> <><>

Name

ContactNo

Address

Email

Christina

516 418 1234

Cincinatti


Girish Kutty

516 418 6752

Cincinatti


Ravichand Koneru

777 213 213

Boston



Sub CopY_Data_To_Word()


Dim oWA As Word.Application

Dim oWD As Word.Document




Set oWA = New Word.Application


Set oWD = oWA.Documents.Add("C:\Users\comp\Documents\Doc2.dot") ' Replace with your template here


For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row

oWD.Bookmarks("Name").Range.Text = Cells(i1, 1)

oWD.Bookmarks("ContactNo").Range.Text = Cells(i1, 2)

oWD.Bookmarks("Address").Range.Text = Cells(i1, 3)

oWD.Bookmarks("Email").Range.Text = Cells(i1, 4)


'Code for saving the document


Next i1



' Releasing objects etc

End Sub

Bookmarks are added to the Word template and whenever a new document is created from the template, the document has those bookmarks.

The code above places the information from the Excel sheet to the specific Bookmark ranges

Excel to Word using VBA




1 comment:

  1. Anonymous4:19 PM

    This seems to work ok until here: Set oWD = oWA.Documents.Add("C:\Users\comp\Documents\Doc2.dot")

    It then crashes and does not open the Word doc.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.