vba - 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

1 Answer

  1. Randall- Reply

    2019-11-14

    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 >> 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 = "<<CUSTNAME>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E22")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<PONUM>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E23")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<PARTNUM>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E24")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<DRAWNUM>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E25")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<DRAWREV>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E26")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<PARTDESC>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E27")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<MATERIAL>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E28")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<SHIPDATE>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E29")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<NAME>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E31")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<TITLE>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E32")
        .Application.Selection.EndOf
    
        .Application.Selection.Find.Text = "<<SHIPDATE>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("E29")
        .Application.Selection.EndOf
    
        .SaveAs2 Filename:=FName, _
        FileFormat:=wdFormatXMLDocument, AddtoRecentFiles:=False
    End With
    
    
    
    End Sub
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>