asked on

Populate Word Template From Excel Table, Save Each New File As Individual File

Allrighty,

I have a Word 2007 file that contains a bunch of Active X textboxes and checkboxes, the "Template". I have an excel workbook that contains a table with client names and values that correspond to each textbox and checkbox on the word document. I would like to be able to click a command button in excel, and have Excel open one instance of word, populate the textboxes and checkboxes with the corresponding values from excel, save the word file in a new folder on the user's desktop, and the proceed to create a new word document and do the same thing all the way down the list. Please refer to the attached files. The crappy code I have been able to write so far is located in the Excel file. Thanks for your help. 2011-04-19-Group-Notes-Automatio.xlsm Group-Note-Template-Auto-Backup-.docx

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GrahamSkan

It would make it easier if the header names matched the control names.

However this looks like a candidate for mail merge which does not require any coding, so, if you were starting from scratch, I would definitely recommend that option.

In case you want to pursue it with this project, here is some code that replaces the each control with a merge field named after the label (or control caption) in the table cell.

Sub MakeMailMergeMainDoc()
    Dim ilsh As InlineShape
    Dim rng As Range
    Dim cl As Cell
    Dim strText As String
    
    
    Set ilsh = ActiveDocument.InlineShapes(1)
    Do Until ilsh Is Nothing
    If ilsh.Type = wdInlineShapeOLEControlObject Then
        Set rng = ilsh.Range
        If rng.Tables.Count = 1 Then
            If ilsh.OLEFormat.ClassType = "Forms.CheckBox.1" Then
                strText = ilsh.OLEFormat.Object.Caption
                rng.Delete
                rng.Text = strText
                rng.Collapse wdCollapseEnd
            Else
                strText = GetCellText(rng.Cells(1))
                rng.Delete
            End If
            ActiveDocument.Fields.Add rng, wdFieldMergeField, Replace(Replace(strText, ":", ""), " ", "_")
        Else
            Exit Do
        End If
    End If
    Set ilsh = ActiveDocument.InlineShapes(1)
    Loop
End Sub

Function GetCellText(cl As Cell) As String
    Dim rng As Range
    
    Set rng = cl.Range
    rng.MoveEnd wdCharacter, -1 'drop cell formatting
    GetCellText = rng.Text
End Function

Open in new window

Paddy_Boy_Floyd

ASKER

Thank you very very much, this got me on the right track!

SiddharthRout

Paddy_Boy_Floyd: You rated a "B" to my post. Was it lacking something? The reason why I am asking is because I took time to write that code and test it and it is working just fine.

Sid