Forum Discussion
Hi,
I hope that https://support.smartbear.com/viewarticle/20878/ and https://docs.microsoft.com/en-us/visualstudio/vsto/excel-object-model-overview?view=vs-2017 should help.
Hi Alex,
Thanks for taking time out to reply on my query. I have tried sheets function as per the information given in the link. But it is not working either I am getting the same error "JavaScript runtime error. TypeError: aobjWorkbook.Sheets is not a function Error location: Unit: "A360_AutoQA\A360_AutoQA\Script\CommonFuncLib" Line: 65 Column: 16.
{
var objExcel = getActiveXObject("Excel.Application");
//objExcel.Visible = true;
var aobjWorkbook = objExcel.Workbooks.Open(ExcelPath);
//Test code
aobjWorkbook.Sheets(sheetName)
//TestCode ends
var iUsedRowCount = aobjWorkbook.ActiveSheet.UsedRange.Rows.Count;
var ValidRowCnt = (iUsedRowCount-1);
return ValidRowCnt;
- AlexKaras6 years agoChampion Level 3
Hi Meenakshi,
I am not sure what getActiveXObject() function in your code is and what are the actual values for the ExcelPath and sheetName parameters, but the following code executed as expected on my box:
var ExcelPath = "C:\\Projects\\Path\\File.xlsx"; var objExcel = new ActiveXObject("Excel.Application"); var aobjWorkbook = objExcel.Workbooks.Open(ExcelPath); var sheet = aobjWorkbook.Sheets("Capacity"); var iUsedRowCount = sheet.UsedRange.Rows.Count; WScript.Echo(iUsedRowCount); aobjWorkbook.Close(); objExcel.Quit();
- meenakshiyadav16 years agoContributor
Hi Alex,
My Objective is to open an excel workbook and retrive data from different worksheets. Also I need to access worksheets by their name.
If I use "new ActiveXObject" in my Function, I get error "JavaScript runtime error. ReferenceError: ActiveXObject is not defined "on the first line itself.
and If I get "getActiveXObject" the I get "JavaScript runtime error. TypeError: aobjWorkbook.Sheets is not a function" on 4th line.
In below mentioned function, ExcelPath is the path of Excel file That I am trying to open and sheetName is name of the the worksheet.
function get_valid_row_count (ExcelPath,sheetName)
{
var objExcel = new ActiveXObject("Excel.Application"); // ---------first line
//objExcel.Visible = true;
var aobjWorkbook = objExcel.Workbooks.Open(ExcelPath);
var sheet = aobjWorkbook.Sheets(sheetName) // -------4th line
var iUsedRowCount =sheet.ActiveSheet.UsedRange.Rows.Count;
var ValidRowCnt = (iUsedRowCount-1);
return ValidRowCnt;
}Regards,
Meenakshi
- AlexKaras6 years agoChampion Level 3
Hi Meenakshi,
If I use "new ActiveXObject" in my Function, I get error "JavaScript runtime error. ReferenceError: ActiveXObject is not defined "on the first line itself.
Ah, indeed, my bad - this is clearly described in the https://support.smartbear.com/testcomplete/docs/scripting/specifics/javascript-for-jscript-users.html help topic: "new ActiveXObject() is not supported in JavaScript. Use Sys.OleObject or getActiveXObject instead."
Try to use Sys.OleObject and check if it helps.Also read the referenced article and check if it contains something that matters to you. (I am not JavaScript guy and well may miss some important detail.)
P.S. Also, https://smartbear-cc.force.com/portal/KbArticleViewer?name=Get-COM-reference-for-a-running-Excel-instance&sp=testcomplete is something that you must be aware of as you are working with Excel via COM.
Related Content
- 5 years ago
- 5 years ago
- 4 years ago