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.