WebLink: Sending Data from Pro/ENGINEER to Microsoft Excel with JavaScript

Occasionally data is needed in Microsoft Excel from Pro/ENGINEER, whether it is geometry values, parameter values, or BOM table contents. Data from any of these data sources can be sent directly to Excel with Pro/WebLink, without writing any external CSV files and without running any other applications.

Technically speaking it is JavaScript functionality, or JScript as Microsoft likes to call it, and not really Pro/WebLink at all. To be useful though, it will be running within the context of a Pro/WebLink application.

Listed below is a function that takes an array of (number or text) values and writes the data in a new Excel workbook. You'll probably find many examples on the Internet using Visual Basic having the same basic steps.

The most important part is getting the handle to an Excel session. The "new ActiveXObject()" call will start a new session of Excel, while the GetObject() call will obtain a handle to an existing Excel session. Depending on your Internet Explorer security settings (i.e. "Initialize and script ActiveX controls not marked safe for scripting"), you may have to use one or the other, but ideally both should work. Using an existing session is definitely more useful when sending data from Excel to Pro/ENGINEER.

After the handle is obtained, the session is setup to be visible with a new workbook (.xls file). A reference is then obtained to the active sheet. Using the "Value" property of a specific cell in the active sheet, we can put data into the cell, in this case from the array passed to the function.

function arrayToExcel ( array ) {
    var oXL;

    try {
        oXL = new ActiveXObject("Excel.Application"); // Use new session
        // oXL = GetObject("","Excel.Application"); // Use existing session
    }
    catch (e) {
        alert("Excel must be running!");
        return;
    }

    try {
        oXL.Visible = true;
        var oWB = oXL.Workbooks.Add();
        var oSheet = oWB.ActiveSheet;
    }
    catch (e) {
        alert("Problem creating new workbook.");
        return;
    }

    for (var i=0; i < array.length; i++ ) {
        oSheet.Cells(i+1, 1).Value = array[i];
    }
}

Here is some example code that populates an array and calls the "arrayToExcel()" function:

var array = new Array();
array.push(1.11);
array.push(2.22);
array.push(3.33);
arrayToExcel(array);

As always, comments and questions are welcome.