cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
anumolu9999
Contributor

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
SebastianP
New Contributor

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

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 

View solution in original post

5 REPLIES 5
tristaanogre
Community Hero

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

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?


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
anumolu9999
Contributor

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

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.

 

 

SebastianP
New Contributor

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

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 

View solution in original post

sonya_m
Community Manager

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

Thank you everyone for help! 

Hi @anumolu9999  , did you find a solution? Did you have a chance to try what SebastianP is suggesting? 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
anumolu9999
Contributor

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

Thank you @SebastianP 

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors