Script Extensions and Scope

I'm not entirely certain what problem you are having.  As far as I know, if it's done properly, you should be able to do exactly what you're asking through the Script Extension.  It's a matter, I believe, of defining properties on your RunTime object along with functions.  



So, for example, you would have a method for "OpenExcelSheet" which would do exactly that, but it would then assign the Workbook object to a property within your run time object.  You would then have a "Get" method for your property to get the sheet object that you opened.



If you would care to zip up your code, I'd be happy to help you with this.

Thanks, Robert!



The extension is really pretty simple:

function excel(state, path, worksheet)

{

    if(state=='open')

    {

        var Excel;

        Excel = Sys.OleObject("Excel.Application") ;

        Excel.Workbooks.Open(path).Worksheets(worksheet).Activate;

    }

    if(state=='close')

    {

        Excel.displayalerts = false;

        Excel.SaveWorkspace();

        Excel.displayalerts = true ;

        Excel.quit();

    }

}



And in the script, you would do like so:

global.excel('open', 'F:/Spreadsheets/mySpreadsheet.xlsx', 'Worksheet_5') ;

Excel.Cells(10,15).Interior.ColorIndex = 0 ;    //  Set Cell background color to WHITE

global.excel('close') ;



Test Complete generates an exception when setting the background color of the cell in the spreadsheet:  'Excel' is undefined



So, at this point, I figured it was a scope issue.

Yeah, it's a scope issue.  TestComplete is not aware of the Excel variable.  You need to move the declaration of the variable outside of the scope of the function.  You would then, also, need to wrap that variable with a Get and Set method if you want to be able to access it from within your automation.  I would also rename your function from "excel" to something else like "ChangeExcelState".  The end result would be that you would have a runtime object with a read/write property called "Excel" and a function called ChangeExcelState.  Your code in your automation would then read:

global.ChangeExcelState('open', 'F:/Spreadsheets/mySpreadsheet.xlsx', 'Worksheet_5') ;

global.Excel.Cells(10,15).Interior.ColorIndex = 0 ;    //  Set Cell background color to WHITE

global.ChangeExcelState('close') ;

Take a look at this article:  http://smartbear.com/support/viewarticle/11960/



Specifically, follow the link to the section on creating object properties.

Thanks for the help, Robert!  This is definitely the fix.  But I can't quite wrap my brain around this.  I keep trying to impose standard class/object syntax to this, which is not quite what we're doing.  Plus, the VBScript is hard for me to keep track of.  I understand that we are manipulating the TestComplete interface to a generic class that we can customize.



According to your code snippet, global.ChangeExcelState() would call the global.Excel methods, right?  And then I would write a global.Excel.Cells() method, right?



Ok, I'll start with the Description file.  This is what I have:

<Script Name="_global_methods.js" InitRoutine="Initialize">

    <RuntimeObject Name="global">

        <Method Name = "excel_state" Routine = "excel">

            <Description>

                Generic method to open and close Excel.

            </Description>

        </Method>

        <Property Name="excel" Routine="excel" GetRoutine="get" SetRoutine"set">

            excel property description.

        </Property>

        <Description>

            Generic global class for methods used by all projects.

        </Description>

    </RuntimeObject>           

</Script>



I guess what's confusing me is that you would normally instantiate an object of the class, which makes it easy to identify what to manipulate.  What is the TestComplete/JScript equivalent to this?

In this case, there's no need to instantiate the object... it exists in memory.  Much like the aqFile or aqString runtime objects.  Actually, a better equivalent would be to take a look at what's in the AQAScriptExtensions.tcx file as that should have some similar items.



You can also take a look at the ExcelCompare extension available at http://smartbear.com/support/viewarticle/9046/ as that should show you how to properties and such.  



Basically, what you're building in the Description.xml is a description of the object with methods and properties declared in the XML.  Your code unit would then contain the methods described in the XML file (a function called "set", one called "get", your "excel" method, etc).

Ok, I've decided to make a new "Excel" object.  The first problem is this:

            <RuntimeObject Name="global">

                <Method Name="run_app" Routine="run_app">

                    <Description>

                        Runs the desired app.  If not found, the script kicks out and writes to the log.

                    </Description>

                </Method>

                <Method Name="close_app" Routine="close_app">

                    <Description>

                        Generic method to close the specified application.

                    </Description>

                </Method>

                <Method Name = "excel_state" Routine = "excel_state">

                    <Description>

                        Generic method to open and close Excel.

                    </Description>

                </Method>

                <Description>Generic global class for methods used by all projects.</Description>

            </RuntimeObject>

        </Script>

<!--

        <Script Name="_excel.js" InitRoutine="Initialize">

            <RuntimeObject Name="Excel">

                <Property Name="Excel" Routine="Excel" GetRoutine="get" SetRoutine"set">

                    Generic method to manipulate Excel.

                </Property>

                <Description>Generic global class for methods used by all projects.</Description>

            </RuntimeObject>

        </Script>

-->



When I un-comment the second script, NONE of the scripts work in TestComplete.  Do you see anything I'm doing wrong?

I don't think I'm configuring this correctly.  Here's my Description.xml:

<?xml version="1.0" encoding="UTF-8" ?>

<ScriptExtensionGroup Name = "Custom Functions">

    <ScriptExtension Name = "Global" Author = "Lance Behmer" Version = "1.0" Homepage = "www.flowcal.com">

        <Script Name="_global_methods.js">

            <RuntimeObject Name="global">

                <Method Name="run_app" Routine="run_app">

                    <Description>

                        Runs the desired app.  If not found, the script kicks out and writes to the log.

                    </Description>

                </Method>

                <Method Name="close_app" Routine="close_app">

                    <Description>

                        Generic method to close the specified application.

                    </Description>

                </Method>

                <Method Name = "excel_state" Routine = "excel_state">

                    <Description>

                        Generic method to open and close Excel.

                    </Description>

                </Method>

                <Description>Generic global class for methods used by all projects.</Description>

            </RuntimeObject>

        </Script>

    </ScriptExtension>

    <ScriptExtension Name = "Excel" Author = "Lance Behmer" Version = "1.0" Homepage = "www.flowcal.com">

        <Script Name="_excel.js" InitRoutine="Initialize" FinalRoutine="Kill">

            <RuntimeObject Name="Excel">

                <method Name="Workbooks" Routine="Workbooks">

                    Selects the spreadsheet file and workbook to use.

                </method>

                <Property Name="excel" GetRoutine="getExcel" SetRoutine"setExcel">

                    Holds the value of "Excel" for manipulation.

                </Property>

                <Description>Generic class for manipulating Excel.</Description>

            </RuntimeObject>

        </Script>

    </ScriptExtension>

    <Description>

        Global methods for use in all applications.

    </Description>

</ScriptExtensionGroup>



And here's the _excel.js contents:

//   Global variable to the class, not available to the main script.

var excel ;



function Initialize()

{

    //Log.Message("Initialization WORKS!") ;

    setExcel() ;

}



function Kill()

{

    excel.displayalerts = false ;

    excel.SaveWorkspace() ;

    excel.displayalerts = true ;

    excel.quit() ;

}



function getExcel()

{

    return excel ;

}



function setExcel()

{

    excel = Sys.OleObject("Excel.Application") ;

}



function Workbooks(path, worksheet)

{

    excel.Workbooks.Open(path).Worksheets(worksheet).Activate ;

}



/*

function Cells(row, column)

{

    excel.Cells(row,column) ;

}

*/



The contents of _global_methods.js file works fine until I add the new Runtime Object and then my "Global" Extension doesn't show up in the project.  Is there something I'm missing?

Does the Excel extension show up even if the Global one does not?



I THINK multiple extensions can be written into a single package but I'm not certain.  Have you tried putting both runtime objects in the same extension?

Yeah, there's at least one example of using multiple extensions I saw.  BUT, I have switched gears a bit.



I took the original global class and added an "Excel" member.  I created the set and get routines and it works fine.  It works ALMOST exactly as you described.  I simply have to add "global." in front of my current Excel manipulations, whatever they may be.



Everything is working fine, but now my problem is that I can't shut off the Excel process.  So, I have this:

var Excel ;

function excel_state(state, path, worksheet)

{

    if(state=='open')

    {

        Excel = Sys.OleObject("Excel.Application") ;

        Excel.Workbooks.Open(path).Worksheets(worksheet).Activate;

    }

    if(state=='close')

    {

        Excel.displayalerts = false;

        Excel.SaveWorkspace();

        Excel.displayalerts = true ;

        Excel.quit();

        //Sys.OleObject("Excel.Application").quit() ;

    }

}   //   excel_state()



Excel simply refuses to shut off whenever I perform the following in the main script OR in the excel_state() method:

global.excel_state('close') ;

Excel.quit() ;

Sys.OleObject("Excel.Application").quit() ;



Inside the excel_state() method, the first three statements DO execute.  Is there anything else I should do?  Should I write a loop to wait for it to die?  Is there a project setting I have to change?

This probably has to do with your initialization routine...  as soon as the RunTime Object is loaded in memory, it creates and instance of the Excel.Application object.  You probably can't terminate it as it will always be "initialized" by the RunTime Object.  



A suggestion:  Rather than initilizing the excel object in an initialization routine, how about you add code to your other routines to check to see if the object has been initialized.  If it has, don't do anything... if it hasn't, initialize it.