Exsead - Excel Data Validation and Named Ranges Example

• Download source code - 12.8 KB

Introduction

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?

var excel=WScript.CreateObject('Excel.Application');
excel.DisplayAlerts=false;

First off, the script creates an Excel application running in RAM. This application is accessible to the script via OLE.

// The GUI window is a wrapper class around IE
// in this script we will just keep adding output to
// the bottom of the content of the window. To make this easy
// we define a string that holds the content. We always make the end
// of the string be <!-- --> . Then when we add content we replace the 
// <!-- --> (html comment) with the new content and a new <!-- -->
var gui=new GUIWindow();
var txt='<h2>Nerds-Central Dept Template Cleaner:</h2>' + 
  '<pre>Loading Template Into Excel...\r\n<!-- --></pre>';
gui.SetHTML(txt);
// Sleep to give the GUI chance to update
WScript.sleep(16);
gui.SetVisible(true);

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.

// Open the workbook to be setup
var wb=excel.WorkBooks.Open(WScript.arguments.item(0));
// Clean out all existing named ranges
while(wb.Names.Count>0)
{
    wb.Names.Item(1).Delete();
}

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:

wb.Names.Add
(
    'Deptarray',
    "'Dept lookup'!$A$2:$D$"+rowDeptL
);
        
wb.Names.Add
(
    'lookupvector',
    "'App Lookup'!$A$2:$A$"+rowAppL
);
        
wb.Names.Add
(
     'resultvector',
      "'App Lookup'!$B$2:$B$"+rowAppL
);
        
 wb.Names.Add
(
    'tablearray',
    "'App Lookup'!$A$2:$B$"+rowAppL
);

Please note how named ranges are stored at the Workbook level, not the worksheet level.

// find which emails are in Dept Lookup but not in
// App Lookup
for(var i=0;i<DeptKeys.length;++i)
{
    if(!appEmails.exists(DeptKeys[i]))
    {
        DeptUnlinked.add(DeptKeys[i],true);
    }
}

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:

// Create humand readable output
txt2='';
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';
for(var i=0;i<DeptDupicates.length;++i)
{
    txt2+='    '+DeptDupicates[i]+'\r\n';
}

txt2+='\r\nEmail Addresses In Dept Lookup But Not App Lookup:\r\n';
for(var i=0;i<DeptUnlinked.length;++i)
{
    txt2+='    '+DeptUnlinked[i]+'\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:

// This loop keeps checking the value of the hidden input
// and responses appropreately. The actual innner listening
// loop is provided inside the WaitOnId method of the GUI object.
// All you have to do is define an ID for the hidden (in the html see above)
// and pass this ID to the method. The method returns with the value
// of the hidden when it changes from the value to which it was set
// when the call was made.

while(true)
{
    var action=gui.WaitOnId('eventKey');
    if(action=='close')
    {
        gui.Quit();
        WScript.Quit(0);
    }
    if(action=='save')
    {
        var wind=gui.GetWindow();
        var toCopy=gui.GetDocument();
        toCopy=toCopy.body;
        toCopy=toCopy.innerText;
        var cbd=wind.clipboardData;
        cbd.setData("text", toCopy); 
    }
}

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:

Summing Up

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!