Testing Access to Excel Workbook

One of the common problems people may run into while offloading a workbook across a cluster is making sure that all the compute nodes can see the workbook. When the workbook is simply inaccessible (not deployed or shared), this is very clear from client error messages. However, if only a subset of the compute nodes cannot see the workbook, then determining which nodes don't have a copy can be more difficult. You end up having to do a bit of trial and error or write a script that tests if the workbook can be accessed from each node. Even this isn't foolproof in the case of corrupt workbooks which exist where they're supposed to but still cannot be opened.

To address this issue, I've put together a powershell script which can be run from any cluster client computer (cluster nodes included) that uses the HPC Services for Excel binaries to open the workbook from each cluster node. To modify this for use with you're workbook, simply replace [WorkbookPath] with the path where you expect to find the workbook (eg \\mycomputer\share\myworkbook.xlsm or c:\temp\myclusterworkbook.xlsm, etc).

     $script = {  
                $workbookPath = "[WorkbookPath]" 
                $asm = [Reflection.Assembly]::Load("Microsoft.Hpc.Excel,Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35");  
                $driver = new-object Microsoft.Hpc.Excel.ExcelDriver;  
                try 
                { 
                    $driver.OpenWorkbook($workbookPath);  
                } 
                catch 
                { 
                    $msg = "Unable to open workbook: " 
                    $msg += $workbookPath 
                    return $msg         
                } 
                finally 
                { 
                    $driver.Dispose() 
                }}       
                 
    $encodedscript = [System.Convert]::ToBase64String([System.Text.Encoding]::UNICODE.GetBytes($script)) 
     
    clusrun powershell -EncodedCommand $encodedscript

This script builds a series of powershell commands and then encodes it such that it can be invoked from CMD. This gets around the inability to invoke complex powershell commands from CMD and difficulty remotely running powershell scripts without lowering security settings. The one downside to this approach is that you have to explicitly write out the workbook path rather than passing it as an argument. This is due to the encoding process which encodes exactly the script you give it without evaluating any contained code. Regardless, the above script works nicely and will give you a rundown of your cluster nodes and whether or not they can open the workbook specified. An example output is below: