Printing your survey results to word : Part 4

Sunday, July 25th, 2010 | Custom, Word

By now you will have a script that will produce a template and fill it in with your own formatting for the simple question types. In this article we will add the remaining code in to deal with the complex questions like grids and loops.

First off we will deal with creating the space for the answers so make sure that you have your main code setup like this so that we can create a new template file.

' 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:\temp\TemplateGrids.docx")
'RunTemplate(oWord,IOM,True)
 First off we are going to add the following formatting functions
Sub AddGridSplitRowToTable(oWord,sLabel,sAnswerLabel,sAnswer,iRows,iCols)
    oWord.Selection.MoveRight(12)
    oWord.Selection.Cells.Split(iRows,iCols,True)
    oWord.Selection.Text = sAnswerLabel
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswer
End Sub

Sub AddGridRowToTable(oWord,sAnswer)
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswer
End Sub
Sub AddNextGridRowToTable(oWord,sLabel,sAnswer)
   oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sLabel
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswer
End Sub

These functions are similar to the other formatting ones, but they have, in places a few other pieces of code to make a more complex word table to house the grid question.

Next we need to look at the RunTemplate subroutine and expand the case statement to look like this,

For Each oQuestion in IOM.Questions
 Select Case oQuestion.QuestionType
 Case QuestionTypes.qtSimple
  If ( bWithAnswers ) Then
   GetAnswers(oQuestion,"S","",oWord)
  Else
   MakeSpace(oWord)
   MakeTable(oWord,oQuestion.Label)
        CreateQuestions(oQuestion,"S","",oWord,0,IOM)
        End If
        Case QuestionTypes.qtLoopCategorical, QuestionTypes.qtLoopNumeric
         If ( bWithAnswers ) Then
   GetAnswers(oQuestion,"G","",oWord)
  Else
        MakeSpace(oWord)
   MakeTable(oWord,oQuestion.Label)
   CreateQuestions(oQuestion,"G","",oWord,0,IOM)
  End If
        Case QuestionTypes.qtBlock, QuestionTypes.qtCompound, QuestionTypes.qtPage
         If ( bWithAnswers ) Then
   GetAnswers(oQuestion,"G","",oWord)
  Else
               MakeSpace(oWord)
   MakeTable(oWord,oQuestion.Label)
   CreateQuestions(oQuestion,"G","",oWord,0,IOM)
  End If
    End Select
Next

Again there is nothing really new here , the main thing is that we are passing in the letter “G” to the createQuestions subroutine so that we can identify simply that we are passing it a grid question. So now let’s look at CreateQuestions,

  Case = "GS"
   'MakeTable(oWord,oQuestion.Label)
     Select Case oQuestion.QuestionDataType
      Case = DataTypeConstants.mtDate
      Case = DataTypeConstants.mtBoolean,DataTypeConstants.mtDouble, _
             DataTypeConstants.mtLong,DataTypeConstants.mtText
      Case Else
       iCat = 0
       For each oCat in oQuestion.Categories
        If iCat = 0 Then
       If ( iQCount = 2 ) Then
             If ( sLabel = "" ) Then
              AddGridSplitRowToTable(oWord,oQuestion.Label,oCat.Label,"X" + _
              oQuestion.QuestionFullName + ":" + _
           oCat.Name + "X",1,oQuestion.Categories.count+1)
        Else
         AddGridSplitRowToTable(oWord,sLabel, oIOM.Questions[oQuestion.ParentQuestion.ParentQuestion.QuestionName].categories[oQuestion.ParentQuestion.QuestionName].label,"X" + _
        oQuestion.QuestionFullName + ":" + _
                 oCat.Name + "X",1,oQuestion.Categories.count+1)
        End If
       Else
        'At This stage I need to know
        'oIOM.Questions["GridQuestion"].categories["Sub1"].label
       If ( sLabel = "" ) Then
              AddNextGridRowToTable(oWord,oIOM.Questions[oQuestion.ParentQuestion.ParentQuestion.QuestionName].categories[oQuestion.ParentQuestion.QuestionName].label,"X" + oQuestion.QuestionFullName + ":" + _
           oCat.Name + "X")
        Else
         AddNextGridRowToTable(oWord,oIOM.Questions[oQuestion.ParentQuestion.ParentQuestion.QuestionName].categories[oQuestion.ParentQuestion.QuestionName].label,"X" + oQuestion.QuestionFullName + ":" + _
                 oCat.Name + "X")
        End If
       End If
      Else
            If ( sLabel = "" ) Then
             AddGridRowToTable(oWord,"X" + oQuestion.QuestionFullName + ":" + _
          oCat.Name + "X")
       Else
        AddGridRowToTable(oWord,"X" + oQuestion.QuestionFullName + ":" + _
                oCat.Name + "X")
       End If
      End If
      iCat = iCat + 1
          Next

          ' Do we have Other Responses
          For Each oOther in oQuestion.OtherCategories
           If ( sLabel = "" ) Then
            AddSimpleTable(oWord,oQuestion.Label,oOther.Label,"X" + _
            oQuestion.QuestionFullName + ":" + _
         oOther.Name + ":OtherTextX")
      Else
       AddSimpleTable(oWord,sLabel,oOther.Label,"X" + _
             oQuestion.QuestionFullName + ":" + _
               oOther.Name + ":OtherTextX")
      End If
           Next
     End Select
     Case = "G"
          For each oItem in oQuestion
      iQCount = iQCount + 1
   If ( Find(oItem.ParentQuestion.QuestionFullname,"{") > -1 ) Then
           CreateQuestions(oItem,"GS",oQuestion.ParentQuestion.Label,oWord,iQCount,oIOM)
         Else
           CreateQuestions(oItem,"G",oQuestion.Label,oWord,iQCount,oIOM)
      End If
      Next

This code is added to the main Select case statement in the function and deals with two additional cases “GS” and “G”. Looking at the “G” case first , what we do is , it if it is a grid style question we loop round each sub question. At this stage the code has only be used on a simple grid question so there may be other code that we have to put in place , but this is enough now to get going with. What happens is that a check is made on the child question and if it is the true child then we call the CreateQuestions function again but this time pass in the letters “GS” or GridSimple, otherwise we pass in “G”

When it comes to the “GS” case what is really happening here is that we are doing exactly the same as the “S” case , but because we are a grid we are using different functions. Some of you might say that we should really just modify the “S” code to cope with grid simple questions and we will , when we have sorted out all the bugs etc. It’s easier to have two cases to start with until we have designed everything. This would be V1 of the script. Version 2 would be the more streamlined and faster version with less code.

Next we need to look at the getansewrs, all we need to do is to add a select case that deals with the grid style questions.

Case = "G"
   For each oItem in oQuestion
    If ( Find(oItem.ParentQuestion.QuestionFullname,"{") > -1 ) Then
        GetAnswers(oItem,"S",oQuestion.ParentQuestion.Label,oWord)
       Else
        GetAnswers(oItem,"G",oQuestion.Label,oWord)
 End If
    Next

This code is straight forward. We loop all the questions in the grid, and if they are a child then we run the getAnswers function , passing in the Parent Label and if they are a complex question we pass in the complex question label.

And there you have it , when you run the code now , you will , if everything is setup correctly end up with a template file that looks like this for the grid questions.

Template With Grids 2 pages

as you can see we have the grid split across the pages and the cell contents is larger than it needs to be. To adress this we add some code to change the font size of the cell contents ,

Sub AddGridSplitRowToTable(oWord,sLabel,sAnswerLabel,sAnswer,iRows,iCols)

    oWord.Selection.MoveRight(12)
    oWord.Selection.Cells.Split(iRows,iCols,True)
    oWord.Selection.Text = sAnswerLabel
    oWord.Selection.MoveRight(12)
    oWord.Selection.Font.Size = 2
    oWord.Selection.Text = sAnswer

End Sub

Sub AddGridRowToTable(oWord,sAnswer)

    oWord.Selection.MoveRight(12)
    oWord.Selection.Font.Size = 2
    oWord.Selection.Text = sAnswer

End Sub

Sub AddNextGridRowToTable(oWord,sLabel,sAnswer)

    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sLabel

    oWord.Selection.MoveRight(12)
    oWord.Selection.Font.Size = 2
    oWord.Selection.Text = sAnswer

End Sub

so when we re run the script it ends up looking like this,

Template With Grids

This will be more relasitic to the formating of the document. Next add the look and feel to the template file and then re-run the script to place the respondent answers in and you will end up with something like this,

Template With Answers

And there you have it, version one of our Print to word code. In version two we will try and come up with some methods to get the logic out displayed in the document.

The full version One code can be found in the smarter dimensions code corner.