Automate Courseware Development with XML and Microsoft Excel™

Project management help

What happens when the instructional designer needs a template or graphics file that hasn’t yet been developed? The Graphics team jumps in and does whatever they need to do. The spreadsheet is a good place to include simple flags that communicate clearly what is ready and what still needs doing.

We included the “OK” column after both layouts and components. This column uses a font named “wingdings” which provides nice symbols, and conditional formatting to color the symbol red or green depending on the character. (See Figure 7.)

Figure 7 The OK columns

The Graphics team just looks for red “X” symbols to know what is missing from the training material. Then they can create layouts or media, add them to the course directory, and update the spreadsheet. They can also test their components by opening the course to see if the media looks right within the course context, or if the template places components correctly.

As long as the Graphics designer doesn’t change the file names in the spreadsheet, there will be no need to open the spreadsheet. Just changing the file in the directory and refreshing the training module to reload the media file will show the changes immediately. Once the file is ready, the Graphics designer or project manager may change the OK column to the green check symbol.

As an additional hint, the project manager may use the COUNTIF (Counts cells that have values according to some criteria, for instance “equals X”) function to count the number of “X’s” in the OK columns and gain a feel for how long it will take to finish the module.

If you really feel like making things automatic, you may develop Visual Basic code that looks in the training directory for the file specified in each field and updates the OK column if the file is there. That’s what we did, and the next section describes what it took.

The application

After we got the first spreadsheet ready, the IT group built the Visual Basic program in almost no time. The program works as follows:

1. Read the first column in the table and find a screen name
2. Create the <template> XML file with the screen name (i.e: screen_1.xml)
3. Read the same row to find the template name
4. Write the
5. Read the same row for the first component type and content
6. Write the XML using the type as the tag name and the content inside the tags
7. Check if the row below has a new screen name.
• If it does not, read the next component type and repeat from step 6.
• If it does, repeat from step 2.
8. At the end of the table, close the XML file.

There are a number of ways to do this in Visual Basic. If you have someone on your staff who knows Visual Basic, coding this is really simple. If you don’t, explaining VB syntax and how it works with Excel would be a subject for an article in itself. The right idea here is to work with what you have. I used the software I already had, with the skills my staff had. This is the essential message here!

In order to export the XML files from the spreadsheet, we want a button the user can click that calls the Visual Basic program. You insert a button that calls an application in Excel using the Visual Basic toolbar, which can be enabled through the menu command View | Toolbars | Visual Basic. This toolbar also helps you access the Visual Basic window to insert your code into the spreadsheet.

After pressing the button we get a file for each screen in the module, in the syntax I described earlier. (See Figure 8.)

Figure 8 These are the XML files that the VB application builds from the spreadsheet

Now our e-Learning application is ready to read these XML files, call up the right layouts, text, and media files and assemble those on the screen. In other words, an XML file like the one shown in Figure 9 is rendered to give Figure 10.

Figure 9 XML file generated from the spreadsheet

Figure 10 The rendered screen

There is a separate XML file pointing to the User Interface — a separate .swf file — that the application must fetch. The spreadsheet exports this XML file from a cell. Thus, changing the User Interface in any training is just a matter of changing the swf file name in this spreadsheet cell. This same XML file also

holds the sequence of scene names (generated by traversing the B column from the top to the bottom) so the courseware application will know which screen comes next during the training navigation. We call this XML file the course map. Figure 11 shows an example.

Figure 11 The course map

In this example you see that the screens can be organized in units and lessons (or topics, chapters, thingies, or whatever hierarchical categories you specified in course maps), each one with its title. We found it useful to create a separate spreadsheet with this information as shown in Figure 12. All of the additional information for making the course map XML file is automatically fetched from that spreadsheet.

Figure 12 This spreadsheet provides the information needed to generate the course map.

The results

With all these pieces in place, our development phase worked as follows:

• The Instructional Designer fills in the spreadsheet, choosing the layouts for each scene, filling text boxes with content, and adding any media that comes from the content. After each development cycle, we have a number of templates ready to reuse in future projects. After doing a few modules there will be a lot of reusable templates lying around. All it takes is copying them to the module directory and filling the proper cells with their names — no need to bother designers with the screens that use them.
• The Instructional Designer exports the XML files and tests whatever is ready for the first proofing and navigation adjustments. At this stage the ID may delete or add scenes or components.
• The Graphics Designer looks for “red X’s” and develops the required media, updating the course directories with new files.
• The Instructional Designer gets the updated spreadsheet, exports the new XML files if needed, and checks the final training module. 5. Any adjustments in text or screen order are done directly in the spreadsheet. Adjustments in media are requested from the Graphics team, which only has to handle the specific media file that needs to be corrected, ensuring that no new errors will appear in the other parts of the module during the corrections phase.

We used this process to develop all the training for the ERP implementation I mentioned in the introduction of this article. After the first training module was ready we were able to reuse several template files, the course and units introduction and exercises, and also simple “text_photo” files.

After our fifth module was ready, our templates library was quite functional, and reusability of templates and some media files eventually grew to 80% to 90% of the total templates. That means that after the Instructional Designer fills the spreadsheet at the beginning of the development phase (which would take a maximum of 8 hours), 90% of the module consists of real, finished, automatically-built multimedia, with no manual development whatsoever. Then the Graphics people jump in and build what is missing, just dropping files in a directory, making sure the XML files point to them, and watching the application insert them on the proper screens without any need for programming or opening Flash.

What we saw happening in our project was that the instructional designers focused on getting the best content possible instead of trying to draw screen layouts and explaining to both the client and the designers what they mean. The designers suddenly have much more time to spend on those killer interactions, detailed illustrations, or slicker animations. And the IT people can just stay wherever they usually flee to when you try to get them to work on your courseware. Our modules were always on time, adjusting the ever-changing content was a breeze, and the looks just kept getting better. I can confidently say that all the automation has actually made us more flexible, efficient, and creative than we could ever be using the traditional model. Our development phases required a maximum of 2 weeks and as little as one single day.

Believe it! Automation really does change things dramatically, far and away for the better. Picking tools you may already have in your environment and keeping development to the very minimum can help you achieve superb results.

Comments

Login or subscribe to comment