logo domkeconsulting

Beratung und Entwicklung - Mehr Produktivität für Microsoft Office

logo domkeconsulting

Consultant and Developer - Get more productivity from Microsoft Office

Word 2010: Company-specific File-New-Dialog

Issue: The built-in File New function in Office 2010 backstage view is very often critizised for the following reasons: a) it is very difficult to show company-related templates in a handy and organized manner; b) it contains elements which cannot be hidden and this part of the backstage cannot be customized, c) cannot show comments or explications for the listed templates.

Request: Create a File New function showing only the company templates, grouped by a tree view. Optional Comments should be possible. A search function and a "favorites" function is equally desired.

Solution: A Word 2010 add-in was developed using the Visual Studio Tools for Office runtime (VB.NET). This is what the user sees:

The File New button in the backstage (of course also Ctrl+N and the FileNew button in the toolbar are catched and redirected to the FileNew replacement):

File New button in backstage view

The File New main dialog with a tree view for the template structure, a list view for the templates of the selected node, and the comments field for the selected template:

File New main dialog

The user's favorite templates:

Favorites

The search function returns templates having the search criteria in the name or the comment:

File New Search

Technical notes

  • The default Word FileNew button is set to visible = false (there's no way to remove it).

  • A backstage button "New" is added, which starts the FileNew Replacement dialog shown in the screenshots.

  • The FileNew user interface shows a subdirectory structure in which the template files resides (not physically – see below). On clicking on an entry, a description is shown in the lower part of the dialog. The user can mark templates as "Favorites" (right-click on an entry), and he has a "Recent" node.

  • The information for Favorites and Recent nodes is saved in xml files in the user's AppData folder.

On the lower level, all template files (dotx, dotm) are put into one root folder on the hard drive. A subdirectory structures exists on the hard drive representing the tree nodes shown in the dialog; the structure is read in a backgroundworker process during the first start of the dialog). The subdirectories contain small xml files holding the filename and the description of the template.

Example:

<?xml version="1.0" encoding="ISO-8859-1"?>
<document-element>
<doc-name>NY Memo</doc-name>
<doc-desc>NY Style Memo template with dialog to adjust the document
for Language, Office and Personal Data</doc-desc>
<filename>WC_Memo.dotm</filename>
</document-element>

At the time of the development of the add-in my client found this organization useful with regard of the deployment of new or changed template content. You can put *all* templates just in one folder, and you define through the subdirectories and the xml definition files the content the user sees in the FileNew dialog.

However, it is also possible to put all information just in one XML file, holding the tree structure together with the template details in one place (that's how I realized it in another project).

Excel: Tool to copy eDocs DM links

Mission

The customer uses eDocs DM as document management system. DM has a "DM Linking" function, which allows to redirect links in Excel worksheets to files saved in the DMS. An add-in from Open Text updates these links if necessary.

The Open Text add-in works well and as designed. However, the link formula contains the "absolute" address of the source cell:

=PwDMoniker.PwDMoniker.1|'PCDOCS://SKMA/171893/R'!'Tabelle1!Z6S2'

where PCODCS://SKMA stands for the DMS library, 171893 for the document number, "R" for the recent version, "Tabelle1" for the worksheet in the document, and "Z6S2" in the German version stands for "row 6 column 2".

So it is not possible to use the "fill down" and "fill right" commands in Excel, with which the user can copy normally the Excel link formula to cover a range. There is no possibity either to switch the reference format from relative to absolute through the F4 key as normally in Excel.

The customer wishes a sort of "tool" to fix this.

Solution

I created an Excel add-in with the help of the Add-in Express RAD tool (seehttp://www.add-in-express.com/add-in-net/index.php).

The add-in adds two commands to the context menue of the worksheet. The commands work only if multiple cells are selected, and if the first cell contains a DM link formula.

In this case the source formula is analyzed, the number of cells to fill out is checked, and new link formulas are created adding the row or column information as necessary.

Selection of the source cell and the destination range:

Result:

Cost - and some words about estimations

For research and how-to-do-it checks with some VBA macros, prototyping the add-in using C# in Visual Studio, developing a smart class to calculate the new cell references, finalizing and bugfixing the add-in after the first tests, creating a MSI setup for deployment on the Terminal Servers, and at the end write some words to explain how to use this tool, I used 20 hrs or 2,5 days.

This shows clearly that also for a "small" solution some overhead is necessary. You should never think that the cost of the final product has something to do with the time you need to "check if something is possible", i.e. with the small VBA macro you created in the beginning to explore the possibilities. The final product must work in all circumstances, it must have a proper errorhandling, if possible a log function to trace errors rapidly, a setup which have to be tested too, and above all you can encounter issues for which you haven't been prepared.