Forum Discussion

meenakshiyadav1's avatar
meenakshiyadav1
Contributor
7 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...
  • meenakshiyadav1's avatar
    meenakshiyadav1
    Contributor
    7 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
      7 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();

       

       

      • meenakshiyadav1's avatar
        meenakshiyadav1
        Contributor
        7 years ago

        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