Friday, August 21, 2009

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

Christina@vbadud.com

Girish Kutty

516 418 6752

Cincinatti

gkutty@vbadud.com

Ravichand Koneru

777 213 213

Boston

rkoneru@vbadud.com

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

4 comments:

  1. Hi,

    How to insert that word template

    ReplyDelete
  2. Great Stuff here. Searched for a while on easy tricks like this. Looks good.

    ReplyDelete
  3. roberto8:13 AM

    Than you very much,
    But this time i've found a solution that worked better for me here:
    http://excelgali.mejorforo.net/t764-copiar-datos-de-excel-a-word#3545

    There's no need to add the reference to Microsoft Word __ Object Library

    Thank you very much in any case,
    Other macros have been very useful for me.

    ReplyDelete
  4. Please help:

    Code for saving the document???? i cant write the file name is Cells(i1, 1)

    thanks

    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.