Forum Discussion

rushikesh's avatar
rushikesh
Contributor
6 years ago
Solved

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
  • TanyaYatskovska's avatar
    TanyaYatskovska
    6 years ago

    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();

     

4 Replies

Sort By
  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    6 years ago

    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();

     

      • TanyaYatskovska's avatar
        TanyaYatskovska
        SmartBear Alumni (Retired)
        6 years ago

         

        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.