Inside VSTS Manual Tests - Excel anyone?

I've been working with many different companies on Application Lifecycle Management with Team System and, more recently, doing a lot of work with quality assurance.  I've also been teaching SQE's Visual Studio Team System tester class.  In all cases I receive common questions about whether you can change the included Microsoft Word template or if you can use another format such as Excel.  The answer to both questions is 'Yes', but it isn't simply clicking a button. 

Here is some background to help you understand how Manual Tests are stored which then leads to how you can adapt them...

What is an MHT file?

Anyone who has created a manual test in Visual Studio will recognize the icon and the editor.  The file created appears to be a standard Microsoft Word document.  However it uses a less known files extension: mht.  For those of you not familiar with it, this is not some bizarre proprietary Microsoft Word-only format.  In fact, it is probably better known in its relationship to web browsers such as Internet Explorer.  An MHT file is an archived web page; unlike a standard HTML file, all of the content required to render the page is embedded within the MHTML document Itself.

So why does this matter in terms of testing?  This little fact reveals how the manual test runner in Visual Studio operates: the portion of the screen that displays the manual test content is basically just a web browser. This means that most things that can be displayed in a web browser can be displayed in the Visual Studio manual test window.  It also opens the door for us to create our own MHT files with other tools - such as Excel.

Just add Properties...

So an MHT file is basically just a web page, right?  Right! And I can create an MHT file with just about any Microsoft Office application, right?  Right! So to create my own MHT template I just need to save it as an MHT and I'm done, right?  Well, almost...

There is still a some magic information that is required to convert an MHT into a Manual Test.  In specific, you need to add the fact that it is a manual test and an unique identifier for that specific test.  If your MHT editor is Microsoft Office, you can find this in the document properties.  For Microsoft Word 2007, accessing document properties is as easy as 1.. 2.. 3..  First you access click the Microsoft Office button in the corner, then select Prepare and finally Properties.  If you are using Office 2003 (Word or Excel) it is even easier - just select "Properties" under the File menu. The properties that allow Manual Tests to operate are all custom properties. You can view them in an existing document by selecting the Custom tab on the document properties window. The two custom properties we are interested in are: TestType - This property must contain the value "Manual Test". TestID - This property must contain a GUID value that is unique across the solution. In fact, by it's definition, a GUID should be globally unique. If you have multiple tests with the same GUID you could confuse Visual Studio! If you don't already know about GUIDs, let me point you to a utility built into Visual Studio that allows generation of GUIDs.  To access it, simply pull down the "Tools" menu and access "Create GUID".  If you use this method to generate a GUID, you can directly copy it using the 'Copy' button.  Use the radio button marked '' to make sure you are selecting the correct format. One additional note: the additional Manual Test properties, such as Associated Work Items and Priority, are also stored as custom document properties.

Summary steps for creating an Excel 2003-based Manual Test...

Since many people have existing Microsoft Excel-based test cases that they'd like to convert for use with Visual Studio 2005 Team Edition for Software Testers (or in the very near future, Visual Studio 2008 Test Edition), I've summarized the above information into the following steps converting spreadsheets into Manual Tests.

Open or create your Excel spreadsheet using Microsoft Excel. Access the document properties as described above. Select the "Custom" tab. Create a new property named "TestType" (no space), with a type "Text" and the value "Manual Test" (including a space).  Click the "Add" button to add it. Create another new property named "TestID" of type text. Create a new GUID; you can use your favorite tool or Visual Studio as described above.  Paste it in as the value for your "TestID" property.  Note that the GUID should be in 'Registry format' without the curly braces (e.g., "9C352DB0-BCE1-4D8C-854E-D890BDE19573").  Remember to click the "Add" button. Click OK to exit the properties dialog. Save the spreadsheet as a different file (i.e., use the "Save As" feature from the menus).  Make sure to select "Single File Web Page (*.mht, *.mhtml)" as the format as shown to the right. "Add" the new test case to your test project.  From within Visual Studio with a test project already and visible in Solution Explorer, right-click on the destination project and select Add > Existing Item...  This is also shown in a capture to the right. Browse to and select the MHT file that you just saved.

What about Excel 2007?

Unfortunately the Excel MHT rendering engine for 2007 has been modified.  I'm sure it is better in many ways, but unfortunately it strips off the required document properties.  You can still achieve the desired results, but it take a little more work.  And unfortunately once you do the conversion, you won't be able to use Excel to modify your Manual Test spreadsheet as doing so will strip out the required custom properties.

If you still want to go ahead with it, perform the steps above with the exception of 2-7,  Once you've saved it as an MHT, you can open it using a text editor.  If you are using Visual Studio, you can right-click on the file, select "Open With..." and then choose the Source Code editor, the XML Editor or even the HTML editor.  Once it is open, scroll to the bottom of the file and locate the xml tag using the Microsoft Office schema.  You need to modify that block to include a "CustomDocumentProperties" tag with the required document properties.  The following XML snippet shows a sample with the TestType and TestID properties added.

<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
<o:MainFile HRef=3D"../SampleExcelTest.htm"/>
<o:File HRef=3D"stylesheet.css"/>
<o:File HRef=3D"tabstrip.htm"/>
<o:File HRef=3D"sheet001.htm"/>
<o:File HRef=3D"sheet002.htm"/>
<o:File HRef=3D"sheet003.htm"/>
<o:File HRef=3D"filelist.xml"/>
<o:CustomDocumentProperties>
  <o:TestType dt:dt=3D"string">Manual Test</o:TestType>
  <o:TestID dt:dt=3D"string">18caa06a-0a91-4070-ab62-2123769277d4</o:TestID>
</o:CustomDocumentProperties>
</xml>

A slightly better alternative would be to Publish the individual worksheet you need instead of the entire workbook.  When you do this, the resulting MHT file can be modified in Word 2007.  This includes adding the custom properties through the user interface, as described in the first section of this post, or manually as described in this section.  If you do in manually, there will not be an existing xml node as shown in the above sample.  Instead, you will need to add the <xml... tag, all of the boldfaced custom property information as shown above, and the closing xml tag, as shown below:

<xml xmlns:o=3D"urn:schemas-microsoft-com:office:office">
<o:CustomDocumentProperties>
  <o:TestType dt:dt=3D"string">Manual Test</o:TestType>
  <o:TestID dt:dt=3D"string">18caa06a-0a91-4070-ab62-2123769277d4</o:TestID>
</o:CustomDocumentProperties>
</xml>

Hey, My Spreadsheet opens in Word!

If you were using Excel 2007 and only published the single worksheet, this is  the best case.  But if you are still in Excel 2003 and want to continue using Excel as your editor, there is hope!  When you right-click on a file in Visual Studio to open it with an alternative editor, you have the ability to "Set as Default".  With a little luck, you will have the "2003 Microsoft Office Component" in your list of options.  This editor is capable of opening an MHT file with Excel if it was generated in Excel or with Word if it was generated in Word.

This concludes this post for the moment.  However I do hope to publish a follow-up...  If you noticed my list of steps and thought "Gee - that could be scripted" you are right.  I am going to do some cleanup and post an approach for mass migration of existing Excel spreadsheets into Manual Tests.  I also plan to file an enhancement request with to Office to to see if we can get the custom properties back!

Cheers!