Printing your survey results to word : Part 3

In this third article on printing to word, we will enhance the scripts that we already have so that it places the respondents answers’ in the template file in their allotted space.
Before we get started with making code changes to our script we would like to make sure that you have run the previous articles script and produced a valid word document. If you have , then we would like to explain what else you could do with it. Looking at the document you should be seeing something like this  

Basic Layout

What you can do , is format this template file even further , for example add you own logos and page formatting. The key thing is that you do not remove the place holders that will be used to place the respondents answers in. And to be honest that is all we care about at this stage , you can change / move what you like , just don’t mess with the place holders. So what you could end up with is a template file for you survey like this,  

Fornatted Template

So once we have format the document the way we want then we can start to add the code to get the answers in. Currently your script will be saying this ,

 ' These two lines will make a template for us.
oWord.Documents.Add()
RunTemplate(oWord,IOM,False)
' These lines will use a template and show results
'oWord.Documents.Open("D:DATASDLibraryPrintToWordTemplateGrids.docx")
'RunTemplate(oWord,IOM,True)

What we want to do now is to flip this around. We have our template all nicely formatted , we now want to use it. Change the above 6 lines of code to this, 

' These two lines will make a template for us.
'oWord.Documents.Add()
'RunTemplate(oWord,IOM,False)
' These lines will use a template and show results
oWord.Documents.Open("c:tempTemplate.docx")
RunTemplate(oWord,IOM,True)

We are also going to uncomment the get answers line in our RunTemplate subroutine, 

 If ( bWithAnswers ) Then
 GetAnswers(oQuestion,"S","",oWord)
Else
 MakeSpace(oWord)
 MakeTable(oWord,oQuestion.Label)
 CreateQuestions(oQuestion,"S","",oWord,0,IOM)
End If

You will have noticed by now that when we call the RunTemplate subroutine this time we are passing in a True value , which tells us that we are looking for answers. To get the answers we need to have a GetAnswer function and this function looks like this, 

 Sub GetAnswers(oQuestion,sType,sLabel,oWord)
Dim oCat,oItem,oOther
 Select Case sType
  Case = "S"
     Select Case oQuestion.QuestionDataType
      Case = DataTypeConstants.mtDate
              FindAnswer(oWord,"X" + oQuestion.QuestionFullName + _
                            "X",oQuestion.Response.Value)
      Case = DataTypeConstants.mtBoolean,DataTypeConstants.mtDouble, _
             DataTypeConstants.mtLong,DataTypeConstants.mtText
      FindAnswer(oWord,"X" + oQuestion.QuestionFullName + _
                            "X",oQuestion.Response.Value)
      Case Else
       For each oCat in oQuestion.Categories
        If ( oCat.Value.Format("a") = oQuestion.Response.Value.Format("a") ) Then
             FindAnswer(oWord,"X" + oQuestion.QuestionFullName + ":" + _
        oCat.Name + "X","Yes")
       Else
            FindAnswer(oWord,"X" + oQuestion.QuestionFullName + ":" + _
        oCat.Name + "X","No")
      End If
           Next
          ' Do we have Other Responses
          For Each oOther in oQuestion.OtherCategories
           FindAnswer(oWord,"X" + oQuestion.QuestionFullName + ":" + _
    oCat.Name + ":OtherTextX",oOTher.OtherQuestion.response.value)
          Next
     End Select
     Case = "G"
  ' Grid code to go here
 End Select
End Sub

As before at this stage we are only looking at simple questions , so when we get one inside this subroutine we again check its real data type. At this stage we have split date times from boolean, double , long and text, but in reality they could be the same. If we can we would like to get some sort of calendar control showed in the document to show dates just to show you what is possible, but that will come in later articles.

 When it comes to the categorical we loop each category and check each one in turn. If the category we are on is the same as the data value entered by the respondent then we pass in a “Yes” as the answer to the findAnswer subroutine and if it is not a match we pass in a NO. Once this is done with the categories we do the same with the other specify if there are any, but of course , we don’t pass in a yes , we pass in the text.

 So what does Find answer look like and do.

 Sub FindAnswer(oWord,sLookingFor,sValue)
         With oWord.Selection.Find
           .Text = sLookingFor
           .Replacement.Text = sValue
           .Forward = True
           .Format = False
           .MatchCase = False
           .MatchWholeWord = False
           .MatchWildcards = False
           .MatchSoundsLike = False
           .MatchAllWordForms = False
         End With
         oWord.Selection.Find.Execute(,,,,,,,,,,1,,,,)
         oWord.Selection.Font.Size = 10
         oWord.Selection.HomeKey(6)
End Sub


When we call findanswer we pass in the word document, the thing we are looking for and what we want to replace it with so because we have a template in place with our markers we can use the word find option, when we have found the marker we are looking for we replace it with the contents we need , set its font size to be ten, and then move back up the top of the document again ready for the next find.

And there you have it , if you get all this working you will end up with code that will loop all your simple answers and place the responses in the template in the correct location. For us , because we formatted our template , our document looked like this in the end.

Template with answers

In our next article we will start to add the code that will handle the complex question types.

Leave a Comment

%d bloggers like this: