Populate a word document based on data entered through a form

[Soph1234]

Hi I wonder if any of you could help me,

On a worksheet in a Spreadsheet workbook, I have used the form options to create a form. These include a drop down menu for name, a list box for payment, an option button for yes and no etc. I have created a macro button so that when 'OK' is selected, the data from this form is exported to another worksheet in this workbook.

When 'yes' is selected from the option button (yes is cell linked to L19), I would like the Spreadsheet system to open up a template receipt document from word and to populate the fields such as name, payment etc utilising the data I have entered into the form. When 'no' is selected from the option button (also linked to cell L19), I would like the system to open up a template invoice and populate this with the data entered into the form.

Thanks in advance

 

[bertie]

Hi and welcome to the forum.

Because I don't know the specifics of your project I can only give some general advice. I have provided some code to play about with. This will require a lot of editing on your part.

I Used MSOffice 2007.

Open a new Word document
Insert a Bookmark, name it Name
Insert a Bookmark, name it Payment
Save and Close the Word Documant.

Open a new Excel workbook.
Name Sheet1 Form
Name Sheet2 Invoice

Insert some values into the Form sheet:
Form

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Name</td><td>Payment</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>Bertie</td><td style="text-align:right; ">1234</td></tr></tbody></table>
Place YES or NO values into cell L19.


First of all we have a controlling procedure which determnies what action to take.

[COLOR=darkblue]Sub[/COLOR] WhatToDo()
  [COLOR=darkblue]With[/COLOR] Sheets("Form")
    [COLOR=darkblue]If[/COLOR] UCase(.Range("L19").Value) = "YES" [COLOR=darkblue]Then[/COLOR]
      PopulateWordDoc
    [COLOR=darkblue]Else[/COLOR]
      PopulateExcelInvoiceTemplate
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

PopulateExcelInvoiceTemplate()
We copy and rename the Invoice sheet based on the customer's name, cell A2 in the sample data.

  [COLOR=green]'create a copy of the invoice and rename as client name[/COLOR]
  sheetName = wsForm.Range("A2").Value
  Sheets("Invoice").Copy After:=Worksheets(Worksheets.Count)
  ActiveSheet.Name = sheetName
  [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sheetName)

We then Map the data in the Form sheet into the new worksheet.

  [COLOR=green]'copy the data[/COLOR]
  [COLOR=darkblue]With[/COLOR] wsNew
    [COLOR=green]'==============[/COLOR]
    [COLOR=green]'edit mappings[/COLOR]
    '==============
    .Range("A2").Value = wsForm.Range("A2").Value
    .Range("B2").Value = wsForm.Range("B2").Value
    [COLOR=green]'[/COLOR]
    'etc
    '
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

PopulateWordDoc()
We are controlling MSWord from MSExcel. We need to set up a reference to allow Excel to access Word Objects. Do this manulaly.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateWordDoc()
  [COLOR=green]'=======================================================[/COLOR]
  [COLOR=green]'Requires:[/COLOR]
[COLOR=SeaGreen]   'Tools => References =>Microsoft Word xx Object Library[/COLOR]
  [COLOR=green]'=======================================================[/COLOR]

Here is the code to set up and open the Word document, Edit where highlighted:

  [COLOR=darkblue]Dim[/COLOR] appWord [COLOR=darkblue]As[/COLOR] Word.Application
  [COLOR=darkblue]Dim[/COLOR] docWord [COLOR=darkblue]As[/COLOR] Word.Document
  
  [COLOR=darkblue]Set[/COLOR] appWord = CreateObject("Word.Application")
  [COLOR=darkblue]Set[/COLOR] docWord = appWord.Documents.Open("[COLOR=Red]C:\My Documents\Word\Soph1234 Word Doc.docx[/COLOR]")
  appWord.Visible = [COLOR=darkblue]True[/COLOR]

And here is the code to transfer the data from the Excel Form into the Word bookmarks we created earlier.

  [COLOR=green]'transfer the data[/COLOR]
  [COLOR=darkblue]With[/COLOR] wsForm
    docWord.Bookmarks("Name").Range.Text = .Range("A2").Value
    docWord.Bookmarks("Payment").Range.Text = .Range("B2").Value
    [COLOR=green]'[/COLOR]
    'etc
    '
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Here is the full code for you to play about with. I have commented out the "tidy up" process in the PopulateWordDoc() procedure for testing.

The code would be placed in the ThisWorkbook module.

[COLOR=darkblue]Sub[/COLOR] WhatToDo()
  [COLOR=darkblue]With[/COLOR] Sheets("Form")
    [COLOR=darkblue]If[/COLOR] UCase(.Range("L19").Value) = "YES" [COLOR=darkblue]Then[/COLOR]
      PopulateWordDoc
    [COLOR=darkblue]Else[/COLOR]
      PopulateExcelInvoiceTemplate
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateExcelInvoiceTemplate()
  [COLOR=darkblue]Dim[/COLOR] wsForm [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] wsNew [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] sheetName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]   [COLOR=green]'name of the new worksheet[/COLOR]
  
  [COLOR=darkblue]Set[/COLOR] wsForm = Sheets("Form")
  
  [COLOR=green]'create a copy of the invoice and rename as client name[/COLOR]
  sheetName = wsForm.Range("A2").Value
  Sheets("Invoice").Copy After:=Worksheets(Worksheets.Count)
  ActiveSheet.Name = sheetName
  [COLOR=darkblue]Set[/COLOR] wsNew = Sheets(sheetName)
  
  [COLOR=green]'copy the data[/COLOR]
  [COLOR=darkblue]With[/COLOR] wsNew
    [COLOR=green]'==============[/COLOR]
    [COLOR=green]'edit mappings[/COLOR]
    '==============
    .Range("A2").Value = wsForm.Range("A2").Value
    .Range("B2").Value = wsForm.Range("B2").Value
    [COLOR=green]'[/COLOR]
    'etc
    '
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  
  [COLOR=green]'tidy up[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wsNew = [COLOR=darkblue]Nothing[/COLOR]
  [COLOR=darkblue]Set[/COLOR] wsForm = [COLOR=darkblue]Nothing[/COLOR]
  
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateWordDoc()
  [COLOR=green]'=======================================================[/COLOR]
  [COLOR=green]'Requires:[/COLOR]
  'Tools => References =>Microsoft Word xx Object Library
  [COLOR=green]'=======================================================[/COLOR]
  [COLOR=darkblue]Dim[/COLOR] wsForm [COLOR=darkblue]As[/COLOR] Worksheet
  [COLOR=darkblue]Dim[/COLOR] appWord [COLOR=darkblue]As[/COLOR] Word.Application
  [COLOR=darkblue]Dim[/COLOR] docWord [COLOR=darkblue]As[/COLOR] Word.Document
  
  [COLOR=darkblue]Set[/COLOR] appWord = CreateObject("Word.Application")
  [COLOR=darkblue]Set[/COLOR] docWord = appWord.Documents.Open("C:\My Documents\Word\Soph1234 Word Doc.docx")
  appWord.Visible = [COLOR=darkblue]True[/COLOR]
  
  [COLOR=darkblue]Set[/COLOR] wsForm = Sheets("Form")
  
  [COLOR=green]'transfer the data[/COLOR]
  [COLOR=darkblue]With[/COLOR] wsForm
    docWord.Bookmarks("Name").Range.Text = .Range("A2").Value
    docWord.Bookmarks("Payment").Range.Text = .Range("B2").Value
    [COLOR=green]'[/COLOR]
    'etc
    '
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
  
  [COLOR=green]'tidy up[/COLOR]
  '[COLOR=SeaGreen]docWord.Close SaveChange:=True[/COLOR]
  [COLOR=green]'Set docWord = Nothing[/COLOR]
  [COLOR=green]'appWord.Visible = False[/COLOR]
  [COLOR=green]'Set appWord = Nothing[/COLOR]
  
  
End [COLOR=darkblue]Sub[/COLOR]

Hope this helps,
Bertie.