Forum Discussion

anumolu9999's avatar
anumolu9999
Contributor
5 years ago

Working With Excel Files via COM: set required sheet as active using javascript

I am using excel, which has two sheets. I have to use COM to read excel because of microsoft access database issue.

I have to access the second sheet in the excel based on its name.

 

Code is as below

var Excel = getActiveXObject("Excel.Application");
Excel.Workbooks.Open(excelPath);
var excel_sheet = Excel.workbook.worksheets.getItem("sheetname");
excel_sheet.activate();
Log.Message(Excel.Cells.Item(1, 1));

 

I am getting error at '

var excel_sheet = Excel.workbook.worksheets.getItem("sheetname");

 

Error is showing as 

JavaScript runtime error.
TypeError: Cannot read property 'worksheets' of undefined
 
Thank you,
Anumolu.

 

  • SebastianP's avatar
    SebastianP
    5 years ago

    Try this out,

    filePath="C:\Users\sebastian\Desktop\Sample.xlsx";

    SheetName="Sheet2";

    Excelbook= Excel.Workbooks.Open(filePath);
    var xlsheet = Excel.ActiveWorkBook.WorkSheets.Item(SheetName);

     

     

    I am using the aove and its working 

  • SebastianP's avatar
    SebastianP
    New Contributor
    5 years ago

    Try this out,

    filePath="C:\Users\sebastian\Desktop\Sample.xlsx";

    SheetName="Sheet2";

    Excelbook= Excel.Workbooks.Open(filePath);
    var xlsheet = Excel.ActiveWorkBook.WorkSheets.Item(SheetName);

     

     

    I am using the aove and its working 

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    5 years ago

    My rough guess is that the problem is not on that line but actually on the line where you're opening the file.  "Excel.workbook" is undefined which means there was a problem opening the workbook.

     

    What is the value of the excel path?

    • anumolu9999's avatar
      anumolu9999
      Contributor
      5 years ago

      Hi tristaanogre ,

       

      excelpath is as below

      var dataFileName="PartDetails.xlsx";
      var sCurPath = Sys.OleObject("Scripting.FileSystemObject").GetAbsolutePathName(".");
      var excelPath = sCurPath + "\\Data\\" + dataFileName;

       

      I am not getting any error when removed mentioned two lines of code, the value of active sheet is getting retrieved.

       

      I am using those lines to point to specific sheet. is the two lines of code is correct or is there any others methods to make specific sheet using COM object and in javascript?

       

      Thank you,

      Anumolu.