TestComplete and Excel

You will often need to interact with Excel, either as an AUT or to bash out a quick report of the test results. Abandon any idea of interacting with Excel via the GUI and learn to embrace the OLE interface.

What is the OLE interface? If you open the object browser and enter excel.application as the Sys.OleObject value, you will see all the automation objects available in Excel. See MDSN for full details (I won't link as it moves all the time).

Using the OLE interface, you can activate and interact with workbooks, worksheets and cells.

You can, for instance, use ActiveSheet.Cells(19, 69) to refer to a specific cell but who knows when that cell's going to move? Much more robust to refer to cells by name (assuming the spreadsheet creator is dedicated enough to name important cells...):

Example: Finding cells by name in Excel

  function SetCellValueInExcel(TestWorkbook, TestSheet, TestField, TestValue) {
    var MSexcel = Sys["OleObject"]("excel.application");
    MSexcel["Visible"] = true;
    MSexcel["Workbooks"]["Open"](TestWorkbook);
    MSexcel["Workbooks"](TestWorkbook)["Activate"]();
    MSexcel["Workbooks"](TestSheet)["Activate"]();
    //For ActiveX objects in the sheet
    //check for CheckBox and ComboBox names
    //(use Design mode in Excel to see the names)
    if (TestField.indexOf("CheckBox") > -1) {
        MSexcel["ActiveSheet"][TestField]["Object"]["Value"] = true;
        MSexcel["ActiveSheet"][TestField]["Value"] = TestValue;
    } else {
        if (TestField.indexOf("ComboBox") > -1) {
            MSexcel["ActiveSheet"][TestField]["Value"] = TestValue;
        } else {//Named cell
            MSexcel["Names"](TestField)["RefersToRange"]["Value2"] = TestValue;
        }
    }
}

Note that I'm using subscript rather than dot notation here - it just makes it easier to pick out the keywords in the script.

Reduce, Re-use, Recycle

The spreadsheet may contain macros or add-ins involved in legacy testing of the AUT.

There's no need to duplicate this functionality in TestComplete since you can call the macros directly:

Example: Calling a method in an Add-in or macro

  function ExcelAutoTest() {
    var MSExcel = Sys.OleObject("excel.application");
    MSExcel["Visible"] = true;
    MSExcel["WindowState"] = -4137;//maximised
    //can't do anything until there's at least one sheet open
    MSExcel["Workbooks"]["Add"]();
    //Reactivate the add-in
    MSExcel["AddIns"]["Item"]("My Add In")["Installed"] = false;
    MSExcel["AddIns"]["Item"]("My Add In")["Installed"] = true;
    //call the method, passing in any needed parameters
    MSExcel["Run"]("MyAddIn.xla!TestMethod", "Parameter 1", "Parameter 2", "", "Parameter 4");
}

TestingMisconfused