In this post, I will use two concepts together which I've already discussed under 'Exsead' separately: communicating with Excel, and using Internet Explorer as a GUI.
The script discussed here comes from a real project. Some of the details have been changed to protect corporate secrets; however, the action and structure of the project is in use right now, every day, for one of the world's largest IT companies. Exsead is not just a 'nice idea', it is being used, to great effect.
Named_Ranges_Setup.js does just that: it sets up named ranges in an Excel workbook. The workbook forms the template of information for a business process which assigns the raised calls to managerial domains. Unfortunately, the mapping is not known in the source data. However, in the source data, we do know the email address of the person to which each application is assigned (the group leader). We also know which group each group leader leads. These two relationships are enumerated in two different spread sheets. The data in the spread sheets is then linked into named ranges. These named ranges are then used further down the work flow to make the mapping.
The real business problem that this code fixes is updating the relationships. When email addresses, managers, or applications change, the template needs to be updated and the named ranges re-created. When this process was left up to humans, it was very tiresome for them to keep resetting the named ranges. It was even harder for a human to ensure that they had the email addresses correctly set in the two relationship spreadsheets. Named_Ranges_Setup.js clears the existing named ranges, then re-creates them. Once it has done this, it uses hash sets to check email relationships between the two spreadsheets and shows the user any inconsistencies.
How Does it Work?
First off, the script creates an Excel application running in RAM. This application is accessible to the script via OLE.
var gui=new GUIWindow();
var txt='<h2>Nerds-Central Dept Template Cleaner:</h2>' +
'<pre>Loading Template Into Excel...\r\n<!-- --></pre>';
Next, the script creates a new Internet Explorer application running in RAM and connected via OLE. In this case, the JScript class
GUI is used. This class is included at the end of the script in the source zip.
The script expects one argument: this is the Excel workbook file. This means that the user can place the script on the desktop and 'drag-and-drop' the Excel file onto it. This approach is much liked by busy project managers who have to use this Exsead code.
Once Excel has opened the file, all the existing names are removed. Then, a straight-forward value checking code is used, which runs over the cell values in the spreadsheets to establish the new dimensions of the named ranges. Once these are known, new name ranges are created:
Please note how named ranges are stored at the Workbook level, not the worksheet level.
Hash tables (another JScript class included in the script) is used to accumulate instances of where an email exists in one spreadsheet and not the other. These are then reported to the end user via the GUI:
txt2+='Duplication Email Addresses In Dept Lookup:\r\n';
txt2+='<i>It is ok to have duplicates, ' +
'but they might be worth checking.</i>\r\n';
txt2+='\r\nEmail Addresses In Dept Lookup But Not App Lookup:\r\n';
Finally, the Excel workbook is saved with new the named ranges. At this point, the IE GUI has a form added to it which allows for user interaction:
Does it Work?
To check it works, you can drag-and-drop the included Excel Workbook onto the script and see the new named ranges. To check for named ranges before and after the script has run, follow the screenshots below:
It might appear to not do very much. However, like so many scripts in business, it save a lot of time, and therefore a lot of money. The other excellent aspect of Exsead scripts like this is that they improve data integrity within the business work flow, which is critical for efficiency of a work flow. The simple addition of the GUI to the script turns it from a 'magical widget' to a real application the end user can use and understand.
As always - for more on Exsead or other similar topics - try Nerds-Central!