Populating Word Template from Excel Userform

[mechzombie]

Hi, as the title suggest I am trying to populate a word template from a userform in Excel.

I have created 3 worksheets in excel (CustomerDetails, OrderBrief, OrderDetails) and a Userform with a listbox and a command button.

Listbox is populated with a Named Range consisting of Customer Names, and Command Button is to Generate Invoice by populating detail in the Word template and saving as another file.

Word Template (Invoice.docx) comprises of Labels and a Table. Table basically has 4 columns (Serial No., Item No., Quantity, Total Cost), rows are variable and will keep increasing.

I am able to populate the Label part easily but I have no idea how to populate the table. I have tried looking around the web and didn't find anything understandable.

Would love any help on this, I am a mechanical engineer and runs a small machine shop with zero idea about VBA. So far I was able to create this after going through various tutorials and examples. The idea of this to help me document my Client details, order details etc in an Excel Sheet and be able to generate a bill by filling a word template.

Below are the links to the required documents.
Link to Excel File: Trial1.xlsm - Google Drive
Llink to Word Template: Invoice.docx - Google Drive

 

[Roxxien]

For your table problem, you should use tabulation between each field.
You can try that yourself my starting your macrorecorder and going in the last cell then starting to enter something, use tab, write something, use tab ...
It will automatically create new line so a variable number of row.

This should be a syntax like this in your code

.Goto what:=wdGoToBookmark, Name:="table"
For each row in Invoice
.MoveRight unit:=wdCell
.TypeText Text:=Serial No.
.MoveRight unit:=wdCell
.TypeText Text:=Item No.
.MoveRight unit:=wdCell
.TypeText Text:=Quantity
.MoveRight unit:=wdCell
.TypeText Text:=Total Cost
Next

By using a word table, this is the easiest way I have found yet to populate everything with only 1 bookmark.

As you can use the macrorecorder in Word (same button at the bottom of your screen or in the developer section), you will be able to get the majority of your code like this.

 

[mechzombie]

For your table problem, you should use tabulation between each field.
You can try that yourself my starting your macrorecorder and going in the last cell then starting to enter something, use tab, write something, use tab ...
It will automatically create new line so a variable number of row.

This should be a syntax like this in your code

.Goto what:=wdGoToBookmark, Name:="table"
For each row in Invoice
.MoveRight unit:=wdCell
.TypeText Text:=Serial No.
.MoveRight unit:=wdCell
.TypeText Text:=Item No.
.MoveRight unit:=wdCell
.TypeText Text:=Quantity
.MoveRight unit:=wdCell
.TypeText Text:=Total Cost
Next

By using a word table, this is the easiest way I have found yet to populate everything with only 1 bookmark.

As you can use the macrorecorder in Word (same button at the bottom of your screen or in the developer section), you will be able to get the majority of your code like this.

Hi, thank you the advice here, but I am still not able to figure out how to use it.

I had replaced the "table" (.Goto what:=wdGoToBookmark, Name:="table") with the bookmarked table name in Invoice.docx.

What should the Invoice (For each row in Invoice) be replaced with? I get an error 13/ Type mismatch with the above code.

Link to the screenshot of whole code - 1.PNG - Google Drive

 

[Macropod]

Instead of the table, you might do better to insert a DATABASE field into the Word template, using an SQL statement there to retrieve your data. Presumably the customer name would be the key variable for the DATABASE field to use. You could either have your code edit the DATABASE field's code (in the new document) or, more simply, create a Document Variable in the template and have your Excel code update that. Then, all you'd need to do as a one-off for the DATABASE field is to insert a DOCVARIABLE field into it at the appropriate point. For a demonstration of the DATABASE field's use, see:
http://answers.microsoft.com/en-us/...g-tables/8bce1798-fbe8-41f9-a121-1996c14dca5d
and:
Mail merge into different coloumns
The first one describes the set up for use with a mailmerge; the second functions as a stand-alone field.

 

[Roxxien]

Hi, thank you the advice here, but I am still not able to figure out how to use it.

I had replaced the "table" (.Goto what:=wdGoToBookmark, Name:="table") with the bookmarked table name in Invoice.docx.

What should the Invoice (For each row in Invoice) be replaced with? I get an error 13/ Type mismatch with the above code.

Link to the screenshot of whole code - 1.PNG - Google Drive

I created a loop like that to show you what you would need to populate your table. On how to create a loop, you have multiple solution available.

The way to create the loop depent on how your data is organized.