Advice on Filling Form with Data from Excel

[Shred Dude]

I'm new to this board, and new to Word VBA. Thanks for taking the time to read my post.

I'm tasked with populating a document with data that is presently housed in Excel. The Word document is 17 pages, containing over 900 Textboxes, mostly contained within the 56 Tables in the document, and close to 400 Checkboxes, half of which will need to updated. (Yes/No scenario).

Yesterday, I cleaned up the receiving document's problems, and now have a document with a consitent nomenclature with regards to it's bookmarks.

Before proceeding with the larger task at hand, I'm seeking a liilte advice on the best method to achieve my goal.

Attached is a .zip file containing a small snapshot of the Excel file, and a small Word file. The Excel file shows one section of Data Entry, and how that data is stored into an Excel Range. The Word file shows two tables; these tables are to be populated with the Excel data.

I've written two routines for the Word file that both work. The first, ExcelDatatoTables, uses .tables(x).rows(x).cells(x).range.text= data(x) statements to populate the Word Table's fields with the values as captured into an Excel.Range variable "data". The second macro, ExcelDatatoBookmarks, accomplishes the same task with .bookmarks("XXX").range.text = data(x) statements. (Note, you'll need a reference to the Excel Object Library)

As this is my first attempt to do soemthing like this with Word, I'm looking for a little advice as to the best methods to employ.

Am I on the right track? Is there a better way? What about procesing efficiency? What if I kept the Excel Data Input tempate in Word, instead of Excel. Would that make the job any easier?

Thanks for taking the time to provide some feedback. Ater reading many posts on this board over the past couple of days, it's ovbvious there are some true experts here.

[fumei]

You have formfields in the cells. So, two things.

1. .tables(x).rows(x).cells(x).range.text= data(x)

is dangerous to use. If any other text is in the cell, it will be replaced. Further, making range.text of the cell a value will delete the formfield. If you go this route (making the cell range.text = Value), I assume you would NOT have a formfield there.

2. " .bookmarks("XXX").range.text = data(x) "

There is misunderstandings regarding formfields and bookmarks. The above code will indeed put data(x) at the location in the cell. However, it too will delete the formfield. That is because the code is NOT using any property of the formfield; it is using the property of the bookmark.


ActiveDocument.FormFields("XXX").Result = data(x)

wil put data(x) into the formfield, and retain the formfield.

My point is that if you are using formfields, unless there is a real requirement to do so, use the formfield itself NOT the bookmark.

I assume that the naming is just for your testing purposes. I strongly recommend you name things with explicit and meaningful names.

TBox1004 does not mean anything. I know it is a pain in the butt to name things, but in the long run it is always worth it.

So....you get get the data into a cell with that cell's range.text. In which case, you do not need the formfield.

OR....if you are going to use a formfield, use .Result (of the formfield), rather than Bookmark.Range.Text.

With ActiveDocument.FormFields
'Table 1

.Item("tbox1003").Result = Date
.Item("tbox1004").Result = data(1)
.Item("tbox1006").Result = data(2)
.Item("tbox1005").Result = data(15)
.Item("tbox1007").Result = data(20)
.Item("tbox1009").Result = data(9)
.Item("tbox1010").Result = data(3)
.Item("tbox1011").Result = data(4)
.Item("tbox1012").Result = data(7)
.Item("tbox1015").Result = data(5)
.Item("tbox1016").Result = data(6)

'Table 2

.Item("tbox1017").Result = data(17)
.Item("tbox1020").Result = data(8)
.Item("Check1001").CheckBox.Value = -1
End With
Notice that in your code you use FormFields("Check1001") for the checkbox formfield, but Bookmarks("TBox1004") for the text formfields.

What can I suggest? Well...it depends. Getting values OUT of formfields is very easy. So if you have any need to get data OUT of the Word doc, formfields are handy. Plus they are not affected by possible changes in location.

.Rows(1).Cells(4).Range.Text

is very specifc regarding location. ANY future changes that affect that location will require you to change these values. This can be a pain to maintain.

Both Bookmarks (real bookmarks, not formfield bookmarks), and Formfields can resolve this as their location is a property. .FormFields("yadda").Result = "blah" will put "blah" in the formfield "Yadda" no matter where it is. It can be moved anywhere and it does not matter. .Result will always work.

Real bookmarks (and a Sub that replaces its contents) would also work very well.

Also, if you are going to use Table code, I would suggest making a bookmark of the table, and then setting a Table object for it.

ActiveDocument.Tables(1) ALWAYS points the first table in the document. What if your table - because of some requirement later on - is no longer Table(1)? You have to go through your code and change the index.

However, if you bookmark the table (say your General Information table) and name the Bookmark "GenInfo", then you can:
Dim GenInfoTable As Table
Set GenInforTable = ActiveDocument.Bookmarks("GenInfo") _
.Range.Tables(1)

Voila! GenInfoTable IS that table. No matter where it is. You can move it, add/delete rows or columns to it, add another table above...it does not matter. Setting a table object for it (using the table collection within the bookmark) allows full table functionality for THAT table.

GenInfoTable.Rows(1).Cells(4).Range.Text = data(17)

[Shred Dude]

Fumei:

Thanks for the distinction between Bookmarks and Formfields. I had not gotten to that level of clarity yet in my new attempts to absorb the Word Object model.

I had noticed that my use of the .range.text on the bookmark was deleting the bookmark. I as OK with that, as I anticipated just saving the document after my routine ran, and I'd be done.

But I certainly like the use of the FormField's Result property much better.


Your distinctions between formfields and bookmarks cleared up another question for me. In my document bookmarks.count is greater than formfields.count. Makes sense, as I now understand formfields to be a subset of bookmarks.

Thanks for the pointer, I get it now.

As for naming the formfields, I agree that my nomenclature is cryptic, but it serves my need for the moment. I certainly didn't want to go through and manually name 931 textboxes! In my routine that named the textboxes, I can turn on the code that places the name in Red next to the field. My intention was to use the resulting document in one window, as I coded the connections between my Excel Data and the receiving formfield. I think I'll also capture the formfield name back in the Excel range as a cross reference for future editing.

One follow up question...I think this reflects a lack of a good grasp on my part of the role a "template" plays in Word. Should my Word document that receives the data be saved as a .dot file? And for that matter, might it be advisable to set up the "form" I have in the Excel file as a Word Template file instead, formatting the entry fields as appropriately named formfields? You mentioned that is very easy to get data OUT of the formfields. Would that be easier than doing what I'm doing via Excel now?

The business application is one where data is entered into that form I have in Excel, and then needs to be used to populate several different documents similar to the Word Document I supplied. Imagine ten different variations of the "Their Form.doc" file I supplied. All ten will need a subset of the data stored in the Excel file. Would it be better if the source data began in Word?

Thanks again for sharing your expertise.

[fumei]

"Makes sense, as I now understand formfields to be a subset of bookmarks."

Aaaaaccck!!!!

No, no, no.

Formfields are NOT a subset of Bookmarks. Not!

The creation of a formfield also creates a bookmark with the range of that formfield. They are two independent objects. Formfields are not a subset of bookmarks in any way at all. It would be very incorrect to think they are.

I am not totally following what you are asking, but:

1. using a template may be a very good idea.

2. Word should not be used as a data source. It CAN be used, but that is not its forte, nor its purpose. Word is a word processor. It makes documents.

[Shred Dude]

So Formfields are not a subset of Bookmarks because a FormField can exist without having an associated bookmark. It just so happened that all of the formfields in my document had bookmarks, but that didn't necessarily have to be so. Got it.

Thanks, I'm learing quite a bit here.

[fumei]

Correct. A formfield can exist without having a bookmark. This is IMO a buggy terminology within Word. In the dialog for a formfield, there is the field Bookmark. This is in fact, NOT a bookmark really. It is the Name of the formfield, that is ALSO used (when you click OK on the dialog) to make a bookmark with the same name.

The bookmark itself can be deleted, and the Name (showing as "Bookmark" in the formfield dialog) will remain.

Go figure.