Consulting

Results 1 to 4 of 4

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

  1. #1
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    2
    Location

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

    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

  2. #2
    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
    Attached Files Attached Files
    Last edited by gmayor; 12-12-2019 at 02:33 AM.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    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-...ps-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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    VBAX Newbie
    Joined
    Dec 2019
    Posts
    2
    Location
    Thank you so much for your replies! I truly appreciate it.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •