Printing your survey results to word : Part 2

Monday, June 28th, 2010 | Custom, Word

This is the second article in our series of producing word documents showing an individual’s survey results. In this article we will see how we create the template that will be used in the final stage to present the results to the user.

By now we should have our metadata setup and the simple code added to the routing section to start to create the document.

Dim oWord,sFileName
Set oWord = CreateObject("Word.Application")
oWord.visible = true
' 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)
 oWord.DisplayAlerts = false
 'sFileName = "c:\TEMP\" + replace(replace(CText(DateNow()),"/","_"),":","_") + ".docx"
 'oWord.ActiveDocument.SaveAs(sFileName)
 oWord.Quit()

next we need to create the RunTemplate Function

Sub RunTemplate(oWord,IOM,bWithAnswers)
Dim oQuestion
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
    End Select
Next
End Sub

So what does this bit if code do , well to start with we have three parameters passed in oWord,IOM and bWithAnswers, so oWord is our Word Object , IOM is the IOM object and bWithAnswers if a Boolean ( true / false ) that will tell the Subroutine how to work.

Next we have a dim statement that declares a variable oQuestion and this will be the variable that we will store our questions in. I am sure you would have seen the next bit of code before, it’s a for each loop and what it is setup to do is to loop each question in your survey and test against its Question Type.

To start with we are only going to work with the simple questions types, these are things like , dates , text , numbers and categorical , we will leave the more complex types for another article. So what happens when we hit a simple question type , well if we bWithAnswers is true ( it’s not for us at this stage ) we will go in and get the answers for the question , and if it is false ( it is in our case ) we will go ahead and start to setup the template.

To setup the template for the simple questions we have 3 functions and they are,

Sub MakeSpace(oWord)
 ' Jump out of table
 oWord.Selection.MoveDown(5,1)
' Insert New line
 oWord.Selection.TypeParagraph()
End Sub

This Subroutine moves out of a table if it is in it and then inserts a Paragraph so that there is a new line created before the next bit of content. In our case , we pass in the document that we have just created and add a blank line to it.

Sub MakeTable(oWord,sLabel)
    oWord.ActiveDocument.Tables.Add(oWord.Selection.range,2,1,1,0)
   With oWord.Selection.Tables[1]
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = False
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = False
        .ApplyStyleRowBands = True
        .ApplyStyleColumnBands = False
    End With
    oWord.Selection.Text = sLabel
End Sub

Next we run the MakeTable Subroutine , passing in our word document, and the question label of the simple question that we are on. This Subroutine creates a table with some pre-set look and feel and then sets the contents of that table to be the text that has been passed into it.

Sub CreateQuestions(oQuestion,sType,sLabel,oWord,iQCount,oIOM)
Dim oCat,oItem,oOther,iCat
 Select Case sType
  Case = "S"
     Select Case oQuestion.QuestionDataType
      Case = DataTypeConstants.mtDate
       If ( sLabel = "" ) Then
        Add1RowToTable(oWord,oQuestion.Label,"","X" + _
                                 oQuestion.QuestionFullName + "X")
     Else
      Add1RowToTable(oWord,oQuestion.Label,"","X" + _
                                       sLabel + "X")
               End If
      Case = DataTypeConstants.mtBoolean,DataTypeConstants.mtDouble, _
             DataTypeConstants.mtLong,DataTypeConstants.mtText
       If ( sLabel = "" ) Then
        Add1RowToTable(oWord,oQuestion.Label,"","X" + _
                                            oQuestion.QuestionFullName + "X")
     Else
      Add1RowToTable(oWord,oQuestion.Label,"","X" + sLabel + "X")
               End If
     Case Else
       iCat = 0
       For each oCat in oQuestion.Categories
           If iCat = 0 Then
             If ( sLabel = "" ) Then
              AddSplitRowToTable(oWord,oQuestion.Label,oCat.Label,"X" + _
                                           oQuestion.QuestionFullName + ":" + _
                                           oCat.Name + "X",1,2)
        Else
         AddSplitRowToTable(oWord,sLabel,oCat.Label,"X" + _
                                 oQuestion.QuestionFullName + ":" + _
                                 oCat.Name + "X",1,2)
        End If
      Else
            If ( sLabel = "" ) Then
             Add2RowToTable(oWord,oQuestion.Label,oCat.Label,"X" + _
                                        oQuestion.QuestionFullName + ":" + _
                                        oCat.Name + "X")
       Else
        Add2RowToTable(oWord,sLabel,oCat.Label,"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
            Add2RowToTable(oWord,oQuestion.Label,oOther.Label,"X" + _
                                          oQuestion.QuestionFullName + ":" + _
                                          oOther.Name + ":OtherTextX")
      Else
       Add2RowToTable(oWord,sLabel,oOther.Label,"X" + _
                                          oQuestion.QuestionFullName + ":" + _
                                          oOther.Name + ":OtherTextX")
      End If
           Next
     End Select
 End Select
End Sub

So this Sub is quite large, and it probably looks more complicated than it really is. If you look closely you will see it is a big Select Case statement which is driven from a parameter that we have passed in called sType. This parameter tells us the question type and depending on what we have set it to will depend on what happens. If it is an “S” the this signifies that we have a simple question type, so basically not a grid,loop,page or block. Next inside this select case we have another select case that determines the true data type of the questions, “date,text,boolean,long, double or categorical, and depending on that we have will depend on what happens. Inside of each of these case statements we check to see if the Value for sLabel is empty if it is , we pass in the QuestionFullName, instead of the sLabel into the function Add1RowToTable. At this stage you will notice that we are passing in more variables than we need, and this will change in the end as we expand on the example. When we have completed this set or articles the full clean code will be added to the codecorner for you to just download and run.

The same sort of functions are repeated for the categorical questions , but with a little bit more code basically if it is a categorical question we loop through each possible response creating rows in the table as we go along for each, notice it does this for other specify questions also. You should also notice that a label is being created and passed in to each Add function. This label is what we will be using to search and replace the document for. We basically make a generic key and place it in the relevant place. The keys look like this.

XSingle:AX
XDoubleQuestioNameX

basically we have 2 X’s and in between these we generate a unique name. If the question has responses then this name includes the responses so we can specifically decided where each response will end up.
As you can also see this Subroutine calls a few other functions and they are,

Sub Add2RowToTable(oWord,sLabel,sAnswerLabel,sAnswer)
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswerLabel
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswer
End Sub

This function assumes that we are in a table that has a row which is split into two columns. The Curser is in the last cell of the table. We pass in the Question text and the Question Answer Label. First off we do a move right , which forces the table to make another row with two cells and then places the Question label in the first cell , moves to the next and places the question answer label in the second. Leaving out cursor in the last cell.

Sub Add1RowToTable(oWord,sLabel,sAnswerLabel,sAnswer)
    oWord.Selection.MoveRight(12)
    oWord.Selection.Text = sAnswer
End Sub

This function assumes that we are in a table that has a single column in it. The Curser is in this cell of the table. We pass in the Question Answer Label. First off we do a move right , which forces the table to make another row with one cell and then places the question answer label in it. Leaving our cursor in the last cell.

Sub AddSplitRowToTable(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

Ok , so that is allot of code to get right and we will leave that with you for the time being , but if you get it to work what you should end up with is a word document that looks something similar to this one.