Embedding Binary Files into JScript and VBScript

Introduction

The object of this post is to embed an image into a script which can then load it into MS Excel!

A few months ago, I would have been totally stumped as to how one could do this. However, it is actually dead easy. The key to messing around with applications like this is to think of the script as a 'brain' that sits in the middle of different applications and data whilst controlling them all.

Step One, Building a JScript Archiver

As I have discussed in previous posts, it is possible to read and write binary files using JScript and the ADODB.Stream object. Once the binary file is loaded into a JScript string, it is then possible to use the Base64 codec (see previous posts) to encode this into a string which can be inserted into a JScript script. All that has the upshot that we can load the image and create a JScript script that when run will regenerate it.

My script 'MakeArchive.js' does just this. All you have to do is 'drag and drop' a binary file on it and it will archive it into a js file with the same file name with '_archive_' postfixed. This is a long script so I will just highlight parts of it here for you:

/** Between the START-ARCHIVE-CODE and ---END-ARCHIVE-CODE--- are the binary file
  * and base64 codec functions which are used by both the MakeArchive script and
  * the script it creates. The MakeArchive script reads it own source and looks
  * for these markers so it can locate the source to include in the output script
  */

//---START-ARCHIVE-CODE---
function BinaryFile(name)
{
   ...
}

//---END-ARCHIVE-CODE---

/** Here we create the TEXT file that will receive the archive script
  */
var fso=WScript.CreateObject('Scripting.FileSystemObject');

/** When you drag and drop onto a script in Windows, the file paths
  * of the files you dropped are passed to the script as arguments.
  * This loop is used to go through those dropped files and make an
  * archive for each.
  */
for(var i=0;i<WScript.arguments.count();++i)
{
    var chunks;
    var fn=WScript.arguments.item(i);
    var bf1=new BinaryFile(fn);
    var b64=new Base64();   
    chunks=b64.encode(bf1.ReadAll()).split('\n');
   
    var ts=fso.CreateTextFile(fn+'_archive_.js',true);
   
    ts.WriteLine('function UNArchive(name){');
    ts.WriteLine('var encArray=new Array();');

    for(var j=0;j<chunks.length;)
    {
        var inner='';
        for(var k=0;k<8 && j<chunks.length;++j,++k)
        {
            inner+=chunks[j];
        }
        ts.WriteLine('encArray.push("'+inner+'");');

   ...

    var fso=WScript.CreateObject('Scripting.FileSystemObject');
    var ts2=fso.OpenTextFile(WScript.ScriptFullName,1,false);
    while(true)
    {
        var l=ts2.ReadLine();
        if(l=='//---START-ARCHIVE-CODE---')
        {
            break;
        }
    }

    while(true)
    {
        var l=ts2.ReadLine();
        if(l=='//---END-ARCHIVE-CODE---')
        {
            break;
        }
        ts.WriteLine(l);
    }
    ts2.close();
    ts.close();

Once the archiver has run, you can take the output file and use the source code in it to embed binary data into a new script. Below is an outtake of the script I created to place the WebNerd image into Excel. Once it has opened Excel and inserted the image, it also adds a command button to the spreadsheet. The script listens for the 'Click' event of this button and upon receiving it, closes Excel and then itself. The script segment below is well commented, so please read the comments to understand how it works.

var excel=RunExcel();
// This is the scripting equivalent of an event loop.
// What happens here is that the script effectively
// 'goes to sleep' but can still process any incoming
// events created by connected objects - line the CommandButton
// that we connected using WScript.ConnectObject.
//
// A lot of research over a long time indicates that setting the time
// to 20 milliseconds is optimal for Windows systems
//
while(true)
{
    WScript.Sleep(20);
    try
    {
        // the next line will fail if Excel dies
        // which ensures that if someone closes
        // the connected Excel application then the
        // script will shut down as well.
        var test=excel.WorkBooks.Item(1).name;
    }
    catch(e)
    {
        WScript.Quit(1);
    }
}

// This function is where the bulk of the work running Excel and
// placing objects into it occurs
function RunExcel()
{
    // Create a new Excel in the usual way
    var excel=WScript.CreateObject('Excel.Application');
    // Make it visible - this is visually appealing but has the
    // disadvantage that if the user  interacts with it during
    // the script run it can cause a crash of the script.
    // I would suggest leaving it visible for all debug work but
    // leaving it invisible till the end of the script for production
    excel.visible=true;

    // Now we get a workbook and then the first worksheet.
    // We get rind of the other worksheets as they are not required
    var wb=excel.WorkBooks.Add();
    var ws=wb.WorkSheets.Item(1);
    ws.name="Image Page";
    wb.WorkSheets.Item(2).Delete();
    wb.WorkSheets.Item(2).Delete();

    // Set the binary form of the embedded image to a temporary fil
    var tempf=GetTempFile();
    tempf=tempf+'.png';
    UNArchive(tempf);
   
    // The image will be inserted with the top left hand
    // corner in the selected cell, so this is the best way
    // of placing the insert of an image
    ws.Cells(1,1).Select();
    ws.Pictures.Insert(tempf);   
       
    // This is how we add command objects like list boxes or
    // command buttons to an excel spread sheet
    var objContainer = ws.OLEObjects.Add
    (
        "Forms.CommandButton.1",
        undefined,              // File Name
        false,                  // Link
        false,                  // Display As Icon
        undefined,              // Icon File Name
        undefined,              // Icon Index
        undefined,              // Icon Label
        400*0.75,               // Left
        190*0.75,               // Top
        80,                     // Width
        20                      // Height
    );
   
    // Excel places the new command object in a container
    // objects called a OLEObject. To get to that actual
    // command object with which we want to interact, we
    // use the Object property of the OLEObject
    var button1=objContainer.Object;

    // Now we set the caption to whatever we want
    button1.caption='Click To Close';

    // WScript.ConnectObject causes OLE events generated by the
    // object to be routed to the script. The first argument
    // is the object to which we want to listen. The second
    // is the prefix of the functions within the script that
    // will receive events. So this call will mean that a click
    // event raised by the button will be sent to a function
    // called Button1Event_Click (see below)
    WScript.ConnectObject(button1,'Button1Event_');

    return excel;
}

// Because of the ConnectObject call above, this function will
// receive Click events from the button which is embedded in Excel
function Button1Event_Click()
{
    // This just shuts down Excel and exits
    // the try block is there to ensure that
    // the script is still exited even if the
    // call to quit Excel fails for some reason
    try
    {
        excel.DisplayAlerts=false;
        excel.Quit();
    }
    catch(e){}
    WScript.Quit(0);
}

Storing Binary Data, Loading an Image Into Excel, and Reacting to Excel Object Events

So what have we achieved here? In the face of it, not much possibly. The reality is that we have made a huge step towards creating a framework in which enterprise level, distributed applications can be built from MS Office and Windows Scripting Host. If we couple this work with the previous work on creating management reports using Excel and AJAX, we have removed the boundary between Web Applications and Thick Clients. What we have achieved is a full integration of the familiar MS Office interface with the modern Service Oriented Architecture approach for enterprise applications.

Let us review what we have so far in support of the above statement. We can link MS Office with a remote Web Service and create complex reports. We can drive binary data into MS Office applications. We can respond to MS Office events. This means we can create complex MS Office forms with first rate graphics using images and OLE objects. We can then listen to events created by those forms and from those events trigger communication to Web Services. We can then use Web Services to drive back first rate management information and reports. That sounds like a 21^st first century enterprise application interface! But this time it is 100% familiar to the end user, already on their computers and easy to code up.

Exceed Expectations With Exsead

All this put together, along with a few new tricks to fill in the gaps is what I am now calling Exsead - EXtreme Scripting Enterprise Application Development. I shall be covering more on this subject over the coming weeks.

For more like this from me, see Nerds-Central (my blog).