Forum Discussion

meenakshiyadav1's avatar
meenakshiyadav1
Contributor
6 years ago

how to iterate through worksheets while using get ActiveXObject("Excel.Aplication")

I am using Javascript for my project. I am picking up test data from excel sheet via getActiveXObject. Earler I was just picking data from one sheet but now I am required to pick data correspoding to each testcase from different worksheets.I have written code to get row count , when my Workbook had just one worksheet ,it worked fine. I am not able to get worksheet object and workshet count . Below is my code:

 

function get_valid_row_count (ExcelPath)
{
var objExcel = getActiveXObject("Excel.Application");
//objExcel.Visible = true;
var aobjWorkbook = objExcel.Workbooks.Open(ExcelPath);
(what method sould I use here to get refernce to  worksheets so that I can iterate through them and go to each worksheet by using their name)
var iUsedRowCount = aobjWorkbook.ActiveSheet.UsedRange.Rows.Count;
var ValidRowCnt = (iUsedRowCount-1);
return ValidRowCnt;
}

How can I get worksheet objcet so that I can pass it in place of 'Activesheet'. Also I am not able to check what methods are avilable for the workbook object..whatever I try I get error  that this is not a function.

 

 

5 Replies

Sort By
    • meenakshiyadav1's avatar
      meenakshiyadav1
      Contributor
      6 years ago

      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.

      aobjWorkbook.Sheets is not a function"
      below is my code:
       
      function get_valid_row_count (ExcelPath,sheetName)
      {
      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;
       
      Regards,
      Meenakshi
       
       
      • AlexKaras's avatar
        AlexKaras
        Champion Level 3
        6 years ago

        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();