smarter Dimensions

Printing your survey results to word : Part 1

Monday, June 21st, 2010 | Custom, Word

Ever wanted to produce a word printout of your survey results? You might have been told in the past that this is hard to do or perhaps not possible, but trust us , with some simple bits of code you can produce some great results. So what do you do first, well let’s see…

In our example we are going to start off by creating an MDD that has the basic questions types, that is , categoricals,longs,doubles , text , grids, dates, and other specifies. So , the metadata would look like this.

Metadata(en-AU, Question, label)
SingleWithOther "Single Question with an Other Specify"
    categorical [1..1]
    {
        A "A",
        B "B",
        C "C",
        _Other "Specify Other" other
    };
    Single "Single Question"
    categorical [1..1]
    {
        A "A",
        B "B"
    };
    Multi "Multiple Response Question"
    categorical [1..]
    {
        A "A",
        B "B",
        C "C",
        D "D",
        E "E"
    };
  ToDaysDate "Date Question"
    date;
    DoubleQuestioName "Double Question"
    double;
   LongQuestionName "Long Question"
    long;
    TextQuestionName "Text Question"
    text;
   GridQuestion "Grid Question" loop
    {
        Sub1 "Sub 1",
        Sub2 "Sub 2",
        Sub3 "Sub 3",
        Sub4 "Sub 4",
        Sub5 "Sub 5"
    } fields -
    (
        SingleCategoricalQuestionName "Grid Sub Categorical"
        categorical [1..1]
        {
            A "A",
            B "B",
            C "C",
            D "D",
            E "E"
        };
    ) expand;
End Metadata

Next we are going to create the routing. For our testing we are going to make our lives easier by not asking the questions , but setting them to values that we will be able to verify in the final output. So the first bit of our code does just that.

' Set the survey Answers
Single = {A}
Multi = {A,B}
ToDaysDate="04/06/2010"
GridQuestion[{Sub1}].SingleCategoricalQuestionName = {A}
GridQuestion[{Sub2}].SingleCategoricalQuestionName = {B}
GridQuestion[{Sub3}].SingleCategoricalQuestionName = {C}
GridQuestion[{Sub4}].SingleCategoricalQuestionName = {D}
GridQuestion[{Sub5}].SingleCategoricalQuestionName = {E}
DoubleQuestioName = 12.12
LongQuestionName = 122
TextQuestionName = "This is a text question"
SingleWithOther._Other = "hello"
SingleWithOther = {_other}

 Next we need to start to create the word document. Now in our case we are going to have two main features in this script. The first feature is create the template. The idea here is that we will create a new word document and put all the MDD information into it that we want to be used. Once this is done , then you can manually format the document with a nice look and feel , then comment out the create template code as we only want to run this once and then create a function that then takes that template and puts the users results into it. So how do we start to create the template , first type this code in.

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()

So what is this doing , well first off we declare two variables oWord and sFileName, these are of course going to be used as the word object itself and the filename of the final word document. Next we have the line of code that actually creates the word object in memory. Please note you must have word on the machine that is running this mdd file , if you don’t , it will not work. We tested this code with word 2007 but it will work in 2003. Once we have created the object we set its visible property to true, when we are done this will be set to false as we don’t want the user to see what goes on and it actually slows the whole process down, but for debugging purposes it is a must so we can see what is happening.

the next line of code does as it describes creates a new word document , and this in turn is followed by some code that is commented out which will in the future create the template. You will notice that in this function we pass in three things, the word document, the IOM object and then a true or false parameter. If this is parameter is set to “false” then we are in create template mode. If it is set to true we are in get results mode.

The final bit of code we use to save the document. which at this stage is commented out. We don’t mind about this at present because the document is being created in front of us and we will be prompted to save the document if we want to keep it.

ok so that is it for this article and in the next one we will start to add in the code that creates the actual Template.

4 Comments to Printing your survey results to word : Part 1

Leave a comment