Forum Discussion

frank_vanderstr's avatar
frank_vanderstr
Contributor
8 years ago

Excel.ActiveWorkBook.WorkSheets is not a function

I am working with scripts that read in an excel file for input data and verification.

 

I have had no issue in the past but in one of my read functions I am getting a jscript runtime error of

 

TypeError

excel.ActiveWorkBook.WorkSheets is not a function Error location:

 

I am unsure how this error is occurring as I have used the ActiveWorkBook.WorkSheets call in other places.

 

This is the code the error is being thrown on.

 

function ReadRow(rownum, columnstart, filename, sheetname, columnend) {

               var filepath = "C:/Repositories/Git/Repos/Automation/Automation/ExcelSheets/" + filename;

               var excel = Sys.OleObject("Excel.Application");
               excel.Workbooks.Open(filepath);
              var xlsheet = excel.ActiveWorkBook.WorkSheets(sheetname);


             //Read some data

             return rowdata;

}

 

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      8 years ago
      HKosova wrote:

      Hi Frank,

       

      In JavaScript (unlike JScript), you need to use .Item to access collection items in COM objects.

      var xlsheet = excel.ActiveWorkBook.WorkSheets.Item(sheetname);

      JavaScript - Specifics of Usage: Accessing Collection Items

      Ah, yes... there is that... however, he's using the same code in a different location so I'm thinking this is JScript and not JavaScript... but certainly worth the try...

      • frank_vanderstr's avatar
        frank_vanderstr
        Contributor
        8 years ago

        Using .Item seemed to do the trick.

         

        Next problem I am facing now is that using that pathway, I do not have access to the Cells function for the worksheet as shown in my code here where I populate my array with the data found in the given row of the worksheet.

         

        for(var colcounter = column; colcounter <= colend; colcounter ++) {

        var testcell = VarToStr(xlsheet.Cells(row, colcounter).Value);

        rowdata[arraycounter] = testcell;
        arraycounter +=1;



        }

         

        Using the Item object does it have a similar function to get cell data?

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    Try altering your path name... my guess is that the Workbook is not actually getting opened so excel.ActiveWorkbook might be a null object.  Right now, your path is using the wrong slash...  Try

     

    var filepath = "C:\\Repositories\\Git\\Repos\\Automation\\Automation\\ExcelSheets\\" + filename;

    This should format your string properly for the call to open the workbook.

    Also... make sure that you don't already have the workbook open... since you are using the Excel application directly, you might have a problem with exclusive access...

     

    Finally... what is the reason for using this function rather than implementing DDT.ExcelDriver? It seems you want to iterate through rows in a sheet and use the data in each row... the DDT.ExcelDriver and similar DDT objects were designed to do exactly that.

    • frank_vanderstr's avatar
      frank_vanderstr
      Contributor
      8 years ago

      Still got the same error after changing the file path to what you suggested. I also checked that Excel.exe is not running when I go to run the test.

       

      This is mainly confusing to me as pretty much the same code in  another file works fine.

       

      var Excel = Sys.OleObject("Excel.Application");
      Excel.Workbooks.Open("C:/Users/UserName/Documents/Test Cases.xlsx");
      var xlsheet = Excel.ActiveWorkBook.WorkSheets("CategoryGroup");

       

       

      I am also using this function because it was the easiest to understand when I started doing it.

       

       

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        8 years ago

        You're example of where it is working someplace else has another significant difference... the name of the spreadsheet file is actually hardcoded in the code... the function you posted initially passes that filename in as a parameter.  It's ENTIRELY possible that, when you're getting this error, that the filename may be incorrect... double check that by dropping a breakpoint in your function at excel.Workbooks.Open(filepath) and check the value of filepath.

        You can also, step to the next line and then do a check to see if excel.ActiveWorkbook is actually a non-null value... again, just to double check... but I think the reason for your error is that ActiveWorkbook probably is null and so, therefore, Worksheets cannot be called.