macro to generate a word document from data in excel

I want to generate a word document from data in particular column in excel. such that i will have N page word document for N records in Excel. I also want to customize the position of that data in the word. For example, say first data from excel should be present on 1st page of word document at position 10cm from top and 5cm from left. i have tried a macro and it generates a word with data from particular cell of excel. But was unable to find to next cell data on new page and customize its position..

Option Explicit Sub Create_New_WordDoc() Dim wsApp As Word.Application Set wsApp = CreateObject("Word.Application") With wsApp .Visible = True .Documents.Add End With Set wsApp = Nothing End Sub 

Welcome to SO, please add what you have tried so far as part of the question, also add what problem you are facing.

You could try formatting the information in Word by placing textboxes rather than the main document body. It would give you more control over their position etc. You will need to create page breaks for the other pages to exist first. A good practice when controlling another program would be to record some test macros in the other app (Word) first, to see what the objects are called, etc.

1 Answer 1

You could create word "template" with key text to find and replace. Here is the Macro from excel, your word doc would contain the text between > and you would search and replace

Sub Button5_Click() Dim wApp As Word.Application Dim wDoc As Word.Document Set wApp = CreateObject("Word.Application") FNameQ = Range("E24").Value Dim FnameP As String FnameP = " C OF C" Dim FName As String FName = FNameQ & FnameP wApp.Visible = True Set wDoc = wApp.Documents.Open("LOCATION OF FILE\XXXX.doc") With wDoc .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E22") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E23") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E24") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E25") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E26") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E27") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E28") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E29") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E31") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E32") .Application.Selection.EndOf .Application.Selection.Find.Text = ">" .Application.Selection.Find.Execute .Application.Selection = Range("E29") .Application.Selection.EndOf .SaveAs2 Filename:=FName, _ FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False End With End Sub