cancel
Showing results for 
Search instead for 
Did you mean: 

How to check if a sheet exits in Excel using Test Complete

SOLVED
Highlighted
Contributor

How to check if a sheet exits in Excel using Test Complete

My requirement is to check if sheet with name Test is present in a particular excel file.
If its not present insert a new sheet with name Test into excel.

So how check sheet name and insert new sheet in Excel
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Manager

Re: How to check if a sheet exits in Excel using Test Complete

Hi @rushikesh,

 

Helen has posted a sample script demonstrating how to check if a sheet exists in an Excel file. Here it is:

var oExcel = Sys.OleObject("Excel.Application");
var oWb = oExcel.Workbooks.Open("C:\\MyFile.xls");

var oSheet = null;
try {
  oSheet = oWb.Sheets("Sheet1");
}
catch (e) {}

if (oSheet != null) {
  Log.Message("The specified sheet exists.");
}
else {
  Log.Error("The specified sheet does not exists.");
}

oExcel.Quit();

 

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today

View solution in original post

4 REPLIES 4
Highlighted
Community Hero

Re: How to check if a sheet exits in Excel using Test Complete

Hi,

 

https://www.google.com/search?q=excel+check+if+sheet+is+present

 

Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
Highlighted
Community Manager

Re: How to check if a sheet exits in Excel using Test Complete

Hi @rushikesh,

 

Helen has posted a sample script demonstrating how to check if a sheet exists in an Excel file. Here it is:

var oExcel = Sys.OleObject("Excel.Application");
var oWb = oExcel.Workbooks.Open("C:\\MyFile.xls");

var oSheet = null;
try {
  oSheet = oWb.Sheets("Sheet1");
}
catch (e) {}

if (oSheet != null) {
  Log.Message("The specified sheet exists.");
}
else {
  Log.Error("The specified sheet does not exists.");
}

oExcel.Quit();

 

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today

View solution in original post

Highlighted
Contributor

Re: How to check if a sheet exits in Excel using Test Complete

@TanyaGorbunova can you provide a simlilar sample code demonstrating how to insert a new sheet into excel file?

Highlighted
Community Manager

Re: How to check if a sheet exits in Excel using Test Complete

 

Excel's COM object provides the Sheets.Add method. So, you can play with the code to add this method call. Please take a look at this article for more information.

---------
Tanya Gorbunova
SmartBear Community Manager

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
New Here?
Join us and watch the welcome video:
Announcements
Join TechCorner Challenge!
Want a fun and easy way to learn TestComplete? Try solving weekly TechCorner challenges and get into the Leaderboard!

Challenge Status

Changing options from outside of TestComplete

Participate!

How to execute remote test and obtain results via Test Runner REST API

Participate!

Comparing content of HTML table with Excel file data

Participate!

Compare HTML table with Excel file and correct data in the file

Participate!