I'm trying to create an "open/close Excel" script extension. What I'm finding is that script extensions don't act like regular classes and I can't use a script extension to open Excel, perform operations on a spreadsheet in the script, and then close Excel with an extension.Can I make global objects or is there some other way to handle this?

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 WHITEglobal.excel('close') ;Test Complete generates an exception when setting the background color of the cell in the spreadsheet: 'Excel' is undefinedSo, 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 WHITEglobal.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).

Script Extensions and Scope | SmartBear Community

13 Replies

lrbehmer
Contributor
15 years ago
Well, shouldn't it kill the process when the script is done?
tristaanogre
Esteemed Contributor
15 years ago
I'm not entirely sure. I have a script extension I created for handling SQL queries and I instantiate an ADO.CreateConnection object in there as a global... that object is ALWAYS there, no matter what I do. So long as there is a variable that contains an instance of Excel.Application, it will always be present in memory.

So, the simple fact that you have a global variable instantiated as OleObject('Excel.Application') I think means it will always be memory resident until you set that variable to nil or null... Because it's a run time object, it never goes out of scope and so it never gets reassigned.
lrbehmer
Contributor
15 years ago
Oh, so if I run the excel executable manually, I should be able to kill it.

Forum Discussion

Script Extensions and Scope

13 Replies

Recent Discussions

NameError: name 'Aliases' is not defined

....Preparing an object recognition hint....

Cannot Debug !!! The windows is freeze

Related Content

JavaScriopt format script extension install

Initialization script extension

TestComplete + Jira Script extension