Clearscript: Trying to use Excel's COM API, only getting [object HostDelegate] for cell value

[JohnLudlow]

We have a requirement to be able to use ClearScript to read in a data file (in particular, an Excel spreadsheet). We have an instance of the ExtendedHostFunctions class and are using its COM support like this:

let excel = ExtendedHostFunctions.newComObj("Excel.Application")
excel.Visible = true
let book = excel.Workbooks.Open("d:/test/test.csv")
let sheet = book.Sheets(1)
sheet.Activate()

let cellValue = sheet.Cells(1, 1).Value,

I was expecting cellValue to have a number or a string, but it always contains a HostDelegate. I've tried a few variations.

[ClearScriptLib]

Hi @JohnLudlow,

Excel's Value property requires an argument of type XlRangeValueDataType.

Here's one way to invoke it:

let enums = ExtendedHostFunctions.typeLibEnums(excel);
let XlRangeValueDataType = enums.Microsoft.Office.Interop.Excel.XlRangeValueDataType;
let cellValue = sheet.Cells(1, 1).Value(XlRangeValueDataType.xlRangeValueDefault);

In your case the following is also likely to work:

let cellValue = sheet.Cells(1, 1).Value(10);
// 10 is the integer value of XlRangeValueDataType.xlRangeValueDefault

A third possibility is to use Value2, which has no parameters:

let cellValue = sheet.Cells(1, 1).Value2;

Good luck!

[JohnLudlow]

@ClearScriptLib
Thanks that did the trick. It's been aeons since I last did any Excel automation, and the documentation is kinda lacking (particularly from the JavaScript side).

Thanks

[JohnLudlow]

@ClearScriptLib A further question on this subject

I wrote a script to do stuff in our application based on data in an Excel document (in this case, a spreadsheet), but found that an Excel process was still running after executing that script. Referring to this article, we need to clean up.

Consider this slightly naive implementation:

function Release(object) {
    let Marshal = ExtendedHostFunctions.type("System.Runtime.InteropServices.Marshal");
    Marshal.ReleaseComObject(object);
    object = null;
}

function Using(object, f) {
    f(object);
    Release(object);
}

Using(ExtendedHostFunctions.newComObj("Excel.Application"), excel => {
    excel.Visible = false

    Using(excel.Workbooks, workbooks => {
        Using(workbooks.Open("d:/test/test.csv"), book => {
            Using(book.Sheets(1), sheet => {
                // do stuff with sheets
            });
            book.Close();
        });
        workbooks.Close();
        excel.Quit();
    });
});

As soon as I add Using(book.Sheets(1), sheet...) I get a lingering Excel process. If I comment out that bit, I don't. I can't see why - are sheets special in a way I'm just not seeing?

[ClearScriptLib]

Hi @JohnLudlow,

Your issue is here:

Using(book.Sheets(1), sheet => {
    // do stuff with sheets
});

It may not look like it, but the expression book.Sheets(1) is actually a "double dot". You're not releasing the sheet collection. Try this instead:

Using(book.Sheets, sheets => {
    Using(sheets(1), sheet => {
        // do stuff with sheet
    });
});

Good luck!

[JohnLudlow]

Hi again @ClearScriptLib,

We've been testing with this and eventually realised that we were getting confused because we expected to be using the same API as these examples (note the differences such as .Value rather than .Value2): https://www.activexperts.com/admin/vbscript-collection/msoffice/excel/

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3

Rather than the .NET interop one.

This feels like a silly question but is there a way to create the Excel.Application as seen in the example above?

[ClearScriptLib]

Hi @JohnLudlow,

Unfortunately we don't have a good answer for you.

Most documentation sources list Excel's Range.Value as a property with an _optional_ parameter, and it's likely that VBScript supports convenient syntax for invoking such a property without an argument. Many COM automation APIs were, after all, designed to accommodate VBScript as a first-class consumer.

JavaScript on the other hand recognizes no difference between normal properties, properties with parameters, or methods. When JavaScript code accesses the Value property, V8 gives ClearScript no indication whether the code intends to use the property value directly or invoke it with an argument.

In other words, Excel's Range.Value is just a _bad case_ for JavaScript, or at least for V8. JScript might actually have a rare advantage in this instance, as its extension mechanism is oriented around COM automation, whereas V8's is strictly aligned with JavaScript semantics. Nevertheless, we haven't tested this scenario, and we certainly would _not_ recommend switching to JScript on that basis.

Sorry!

[JohnLudlow]

No problem!

The main reason for asking was because the lingering objects issue I mentioned earlier - we had a half baked theory that it wasn't an issue in the other API.

We'll look into options.

Thanks