Creating MS Word letter each time I'm adding new row in Excel.

[OlgaSCW]

Hello.

I have a data base in Excel on one Sheet. Each time new row is added (customer information like first name, last name, address etc.) I need to create 'form' in MS Word with these details + insert a picture and print it out. Each row on new page. I`m wondering if there is any option to automate creating letter in MS Word or to automate adding a row in Excel from Word. Right now I simply copy in Excel and paste in Ms Word. It does not take lots of time but maybe there is a better way to do it? Excel is successfully updated each day and I need to print out only new entries. I also need to keep MS Word file on my computer to keep on track.

Any advise is more than welcomed.



Thank you,
Olga

[gmayor]

It is simple enough to create a Word document from Excel, or to create an Excel entry from Word, but you have provided insufficient information to do either. I assume that you have a form template where you add the data? How many columns are there in the data sheet and what are the column titles? If all the information is in the Word form, then I would start from Word. If only some of it is to go in the form I would start from Excel.

Attached is a simple Word form template. To use it, create a Workbook with a header row with four columns: ID, Name, Address, Image

Save it as workbook.xlsx in the folder C:\Path\ You can change the workbook name and path as appropriate in the ModMain VBA module of the template.

Create a new document from the template. Complete the form and when you click the last content control, the workbook is updated.

You can use this as a guide to creating a similar process for your own template.

If you want to reverse the process the following Excel macro will use the example template attached to create a form from the last entry in the same worksheet


Option Explicit'Graham Mayor - https://www.gmayor.com - Last updated - 12 Dec 2019
Private Const strPath As String = "C:\Path\Forums\WordToExcel.dotm" 'the path of the Word template
Private Const strDocPath As String = "C:\Path\Forums\" 'the folder to save the document
Private Const strMsg As String = "Ensure printer is switched on and has paper." & vbCr & vbCr & _
"The process will be faster if Word is already running."


Sub FillWordForm()
Dim wdApp As Object
Dim wdDoc As Object
Dim LastRow As Long
Dim xlSheet As Worksheet
Dim strID As String
Dim strName As String
Dim strAddress As String
Dim strImage As String
Dim bStart As Boolean
MsgBox strMsg
ActiveWorkbook.Save
Set xlSheet = ActiveSheet
With xlSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
strID = .Cells(LastRow, 1)
strName = .Cells(LastRow, 2)
strAddress = .Cells(LastRow, 3)
strImage = .Cells(LastRow, 4)
End With
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
bStart = True
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Add(Template:=strPath)
wdApp.Visible = True
With wdDoc
.UpdateStylesOnOpen = False
.AttachedTemplate = "Normal"
.SelectContentControlsByTitle("Update Excel")(1).Delete
.SelectContentControlsByTitle("CustomerID")(1).Range.Text = strID
.SelectContentControlsByTitle("Name")(1).Range.Text = strName
.SelectContentControlsByTitle("Address")(1).Range.Text = strAddress
.SelectContentControlsByTitle("Picture")(1).Range.InlineShapes.AddPicture strImage
End With
wdDoc.PrintOut
wdDoc.SaveAs strDocPath & strID & ".docx"
Beep
MsgBox "Document saved as " & wdDoc.FullName
wdDoc.Close 0
If bStart Then wdApp.Quit
lbl_Exit:
Set xlSheet = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
Exit Sub
End Sub

[macropod]

I'd be inclined to use a mailmerge for this, for which you'd simply set up a mailmerge manin document with the required layout & merge fields, then employ the macro found under Run a Mailmerge from Excel, Sending the Output to Individual Files in the Mailmerge Tips & Tricks thread at: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html. As coded, that macro automatically processes all records. For your purposes, you could simply change the VBA code line:

For i = 1 To .DataSource.RecordCount
to:

For i = .DataSource.RecordCount To .DataSource.RecordCount

[OlgaSCW]

Thank you so much for your replies! I truly appreciate it.