Word Template Question

[dbs179]

Hi all,



I have a quick question about word. I have a template document that contains numerous questions that are answered via check boxes and some user input text. I need to take this template and generate a totally separate "findings" document based on the users answers in the template. I have an answer key document that corresponds to the original template that dictates what "finding" should be used based on the users answers.

Is there anyway with standard word, word add-ons or other third party products to automate this document generation? I was thinking maybe a mail-merge, but from what I've seen I don't think that would work. Any input in the right direction would be greatly appreciated.

Thanks,
Dave

[dbs179]

No one has any ideas? I know I could do what I want with excel, but still stumped with Word. I looked at some document assembly software packages, but even those don't look like they can do what I want. Just in case my original post wasn't clear, I'll give another explanation.

Document A is sent to a person to fill out. It is a form fillable and locked Word Document (fields they need to answer and check boxes obviously are open to edit). They provide their name, address and answer questions 1-94 with yes or no check boxes and return Document A to me.

Document B is for my use only and dictates what responses if any should be placed in Document C based on the name, address and answers from questions 1-94 in document A.

Document C is sent back to the person who filled out Document A originally and gives them recommended actions based on their answers in Document A.

I need to try and automate this process. Right now, I am reviewing Document A, cutting and pasting in the correct responses into Document C from Document B. It is time consuming and prone to errors. There has to be a better way to do this.....

Any suggestions would be very much appreciated.

Thanks,
Dave

[fumei]

It is possible. What kind of checkboxes? In any case it is likely possible - although with 94 questions it would take a lot of code - to simply get the value of a checkbox and action document C appropriately.

Fot example:

If Question 1 = No, then put "blahblah" into document C.

It takes a lot of detail work and you must be absolutely precise in the logic...NO vagueness. It also takes decisions about how the "responses" are put into Document C. Are they boiler plate? Do you write them? Do they already exist in another reference document? Are they bookmarked?

I have done this with 60 questions. It takes a lot of work, but it was for a survey going out to 1200 network admins and there was no way I was going to do all the respnse documents by hand. You are correct, it is time consuming and very prone to error.

[dbs179]

Originally Posted by fumei

It is possible. What kind of checkboxes? In any case it is likely possible - although with 94 questions it would take a lot of code - to simply get the value of a checkbox and action document C appropriately.

Fot example:

If Question 1 = No, then put "blahblah" into document C.

It takes a lot of detail work and you must be absolutely precise in the logic...NO vagueness. It also takes decisions about how the "responses" are put into Document C. Are they boiler plate? Do you write them? Do they already exist in another reference document? Are they bookmarked?

I have done this with 60 questions. It takes a lot of work, but it was for a survey going out to 1200 network admins and there was no way I was going to do all the respnse documents by hand. You are correct, it is time consuming and very prone to error.

Fumei,

Thank you for the response. Yes the logic is clear and the same every time. Also the responses are boilerplate, I will have analysts reviewing the response documents prior to them being sent back, but at least if I can get this to part to work, they are only massaging the boilerplate language that is there. Also the check boxes are legacy form field boxes.

So could you give me a quick example or sample of code, and possibly explain how I would link the two documents, where I would put the code (I'm thinking it would have to be in document C).

Thanks so much, I do appreciate the help. Like you I have a total of about 9800 of these to do. I didn't pick the format to send them out in either, or I would have recommended/researched out a better option prior to starting the project.

Thanks,
Dave

[fumei]

I didn't pick the format to send them out in either, or I would have recommended/researched out a better option prior to starting the project.

Bummer. I am willing to bet that it could be done better.

Thee are a lot of factors, and I have to start with some assumptions.

ALL of the returning documents use the SAME logic. That is, a document from Yogi Bear that answers Yes to question 33 gets the SAME response as a docment from Bugs Bunny that also answers Yes to question 33.

ALL of the boilerplate chunks are in ONE document, and those chunks are bookmarked.

ALL of the boilerplate chunks are text, no graphics.

The response documents are identical in that they are built (being the only difference) from the boilerplate chunks. Identifying elements can be built into this.

The working environments in Word will be only the template with the code and the incoming question document. Note if this is the case, and the boilerplate document contains just text, the boilerplate document does not have to be open.This method uses INCLUDETEXT fields, which simply act as pointers.

There are issues with this simple method, and there are certainly other means to achieve what you want, but this may be a start.

BTW, what version of Word are you using

[fumei]

Fortunately for my project like this, it was all mine. I developed the questionairre and all development post-return was settled BEFORE anything went out. Trying to work things out afterwards may be a time sensitive problem for you.

[fumei]

More questions for you.

These are legacy formfields (which BTW is good for me as I most familiar with them), and you say they are yes and no. Does that mean you have one formfield for yes, another for no. THAT means you could have BOTH. Is this the case?

[Charles Kenyon]

Originally Posted by dbs179

Fumei,

Thank you for the response. Yes the logic is clear and the same every time. Also the responses are boilerplate, I will have analysts reviewing the response documents prior to them being sent back, but at least if I can get this to part to work, they are only massaging the boilerplate language that is there. Also the check boxes are legacy form field boxes.

So could you give me a quick example or sample of code, and possibly explain how I would link the two documents, where I would put the code (I'm thinking it would have to be in document C).

Thanks so much, I do appreciate the help. Like you I have a total of about 9800 of these to do. I didn't pick the format to send them out in either, or I would have recommended/researched out a better option prior to starting the project.

Thanks,
Dave

You could store your variable language in AutoText in your template.
Each checkbox should be assigned a bookmark name. Your code tests for its value, and based on the value inserts code.

See Doug Robbins' answer in this thread for sample vba code for an on-exit macro for your checkboxes. http://social.technet.microsoft.com/...do-not-display

Rather than a protected form or in addition to it you might want to consider using a UserForm which has the advantage of not needing to have your checkboxes in the final document. Create a Simple Userform
Create & Employ a Userform

[dbs179]

Originally Posted by fumei

Bummer. I am willing to bet that it could be done better.

Thee are a lot of factors, and I have to start with some assumptions.

ALL of the returning documents use the SAME logic. That is, a document from Yogi Bear that answers Yes to question 33 gets the SAME response as a docment from Bugs Bunny that also answers Yes to question 33.

ALL of the boilerplate chunks are in ONE document, and those chunks are bookmarked.

ALL of the boilerplate chunks are text, no graphics.

The response documents are identical in that they are built (being the only difference) from the boilerplate chunks. Identifying elements can be built into this.

The working environments in Word will be only the template with the code and the incoming question document. Note if this is the case, and the boilerplate document contains just text, the boilerplate document does not have to be open.This method uses INCLUDETEXT fields, which simply act as pointers.

There are issues with this simple method, and there are certainly other means to achieve what you want, but this may be a start.

BTW, what version of Word are you using

I know it could be done better. I think we could have used Survey Monkey or something similar with much better results, but I have to live with it. The template has gone out already.

The rest of your assumptions are correct. If question 1 on any response is no it will always get the same exact boilerplate language, so I think all of your assumptions and what you talk about with the response document (what I called document B, in my example) not needing to be open is exactly how I would like to do this.

And finally, I'm on Office 2013 at home, Office 2007 at work, but the templates were all built using Office 2007.

Thanks again for your help, I appreciate it.

Dave

[dbs179]

Originally Posted by fumei

More questions for you.

These are legacy formfields (which BTW is good for me as I most familiar with them), and you say they are yes and no. Does that mean you have one formfield for yes, another for no. THAT means you could have BOTH. Is this the case?

They are legacy formfields. All questions have both yes and no, but only one would ever get a response. Also as I mentioned if both are checked, I do have analyst that are going to be comparing Document A and the hopefully auto-generated Document C for any issues and massaging the language as needed.

Also, there are some questions that say to check all that apply, in these cases, the logic varies, but I think if you can show me a quick sample of yes and no and get me started I can figure those all out.

[dbs179]

Originally Posted by Charles Kenyon

You could store your variable language in AutoText in your template.
Each checkbox should be assigned a bookmark name. Your code tests for its value, and based on the value inserts code.

See Doug Robbins' answer in this thread for sample vba code for an on-exit macro for your checkboxes. http://social.technet.microsoft.com/...do-not-display

Rather than a protected form or in addition to it you might want to consider using a UserForm which has the advantage of not needing to have your checkboxes in the final document. Create a Simple Userform
Create & Employ a Userform

Charles,

I'll look into the on-exit macro you mentioned and thanks for the idea about the Userform, but like I said, I didn't develop the template and it has already been sent out.

Thank you for the idea though, I'm going to check out that link as well and perhaps it will come in handy for the next revision of this project.

Dave

[fumei]

The use of on-exit macros, and userforms, is that these create the response document on the fly as the user fills out the questions (on-exit), or as an alternative to the formfields.

BTW in my opinion a properly designed userform is one of the better ways to go.

HOWEVER, we are not talking about a redesigned initial survey document. We are talking a process to deal with returned documents completely by the user. They have already checked the formfields. Therefore on-exits are not useful. They will never fire as YOU are not going to be tabbing in and out of the formfields. You only want to deal with the existing values.

So. You need a new template that will hold the code. It will create a new document and in THAT process start validating the existing formfield values. I have my hands full for the next few hours but I will hopefully be able to give you a sample chunk of code that may get you started shortly. Or, Charles may post something. He knows his stuff and has the experience to do so.

As you note, those links are VERY useful for future projects where you can design a better starting point, but they will not help with your after-the-fact current situation.

[dbs179]

Fumei and Charles,

After discussing this with both of you, I decided to dig a bit into VBA, bookmarks and a few other things. I actually have code working that will pull my responses from Document B, based on answers in Document A and place them automatically in Document C. I do have a few questions though.

1. How can I auto-increment what bookmark location I need to place the next set of "findings". For example, question 1 will always go at bookmark F1, but Question 2 could go at either bookmark F2 or bookmark F1, if no response is needed for question 1. Is this as simple as having a variable that I increment each time I find a true statement? And if so, I've included BKMK as a variable in my code, but don't know how to increment or use that variable to indicate the correct bookmark position.

2. All of my coding is reliant on file location, which right now is on my desktop, however in the future, it may or may not be, and I may even have other users running this code. What is an easy and effective way to handle that? An option I have used in the past with other coding projects I have undertaken is placing all of the working documents in the same folder and then being able to use the directory of that folder once a file is opened. For example, I've used AutoIT in the past to automate a different process. Once the .exe I created was launched it was able to determine the path the .exe was located at and used that as a variable.

3. All of my "findings" or responses need to be numbered. Right now in document c I have the numbering replaced with a "*" and formatted in red. When the script runs to bring over the response, the formatting is removed. I would like to be able to auto-number these as well.

Here is what I have come up with so far and sorry if there is a code box on the forum to put this in, I didn't see one if there is. Also, if this should be moved to the VBA section, could a Mod please move it and please accept my apologies, as I didn't even know VBA could do what I needed so I had no idea where to create this thread other than the general word section.

Thanks again for your help so far, just discussing it and picking your brains has helped tons.

Dave



Sub Auto_Populate()

'_________________________________________________ _______________________
'declares variables

Dim doc_a As Document
Dim doc_b As Document
Dim doc_c As Document
Set doc_a = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document A.docx")
Set doc_b = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx")
Set doc_c = Documents.Open(FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document C.docx")
Dim Q1NO As Boolean
Dim Q2NO As Boolean
Dim Q3NO As Boolean
Dim BKMK As Integer
BKMK = 1
'end of variable delcaration
'_________________________________________________ _________________________


'_________________________________________________ _________________________
'opens Document A

Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document A.docx", ReadOnly:=True

'_________________________________________________ _________________________
'sets variables based on Document A checkboxes

Q1NO = ActiveDocument.FormFields("Check2").CheckBox.Value
Q2NO = ActiveDocument.FormFields("Check4").CheckBox.Value
Q3NO = ActiveDocument.FormFields("Check6").CheckBox.Value

'end of setting variables based on Document A checkboxes
'_________________________________________________ _________________________



'_________________________________________________ _________________________
'sets variable based on Document A checkboxes

If Q1NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F1").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F1").Range.Text
End If

If Q2NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F2").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F2").Range.Text
End If

If Q3NO = True Then
'Opens Document B
Documents.Open FileName:="C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx", ReadOnly:=True
'Activates Document C
doc_c.Activate
'Inserts finding at the first bookmark
ActiveDocument.Bookmarks("F3").Range.Text = Documents("C:\Documents and Settings\dbs\Desktop\Word VBA Test\Document B.docx").Bookmarks("F3").Range.Text
End If

End Sub

[dbs179]

Originally Posted by dbs179

Fumei and Charles,





2. All of my coding is reliant on file location, which right now is on my desktop, however in the future, it may or may not be, and I may even have other users running this code. What is an easy and effective way to handle that? An option I have used in the past with other coding projects I have undertaken is placing all of the working documents in the same folder and then being able to use the directory of that folder once a file is opened. For example, I've used AutoIT in the past to automate a different process. Once the .exe I created was launched it was able to determine the path the .exe was located at and used that as a variable.


End Sub

More progress, I have all of my issues worked out except the one still above. I'm going to do more research, but any input would be great.

Thanks,
Dave

[fumei]

I will look at your posts in detail in a bit, I just do not have time right now. A glance tells me that "normal" problems at at issue. File locations and ordering of inserted bookmarks...

Here is a sample chunk of code. I will explain after.

Sub GetResults()
Dim oDoc As Document
Dim oDocAnswers As Document
Dim oDocResult As Document
Dim docFF As FormFields
Dim oFF As FormField

Set oDocAnswers = Documents("Answers.doc")
Set oDoc = Documents("Survey.doc")
Set oDocResult = Documents(1)
Set docFF = oDoc.FormFields
' if Q1 has Yes and not No
If docFF("Q1_Yes").Result = True And _
   docFF("Q1_No").Result = False Then
   oDocResult.Range.InsertAfter _
      oDocAnswers.Bookmarks("Q1_Yes").Range.Text
End If
' if Q2 has Yes and not No
If docFF("Q2_Yes").Result = True And _
   docFF("Q2_No").Result = False Then
   oDocResult.Range.InsertAfter _
      oDocAnswers.Bookmarks("Q2_Yes").Range.Text
End If
' if Q3 has Yes and not No
If docFF("Q3_Yes").Result = True And _
   docFF("Q3_No").Result = False Then
   oDocResult.Range.InsertAfter _
      oDocAnswers.Bookmarks("Q3_Yes").Range.Text
End If
End Sub
 

The code is in a template (BuildResults.dot). The template creates a new blank document. The document Answers.doc has the bookmark chunks that will be used - it is OPEN. The document Survey.doc is a sample survey return document - it is OPEN.

The code in the template makes a document object of Answers, a document object of Survey.
The object docFF contains ALL the formfields in Survey.

The code tests formfields for the first question. There are both yes and no and they are named Q1_Yes and Q1_No. Pre-planning anyone....

If Q1_Yes is TRUE (checked) and Q1_No is FALSE (NOT checked), then grab the text for the appropriate bookmark (Q1_Yes) and put it into the new blank document.

Do the same for Q2 , then Q3 etc.