how to check if sheet in excel driver exist or not

[shiva_ranabhat]

Hi, 

I want to check if Sheet1 exists or not. Sometimes 'Sheet1' is named as Error in our application, and hence is an actual error in export. Even though file is exported correctly. 

Attempt:

var excelDDT= DDT.ExcelDriver(ProjectSuite.Variables.PlanExportDirectory, "Sheet1", true);

      if(excelDDT.){
          Log.Message("excel DDT exists");
      }
      else{
          Log.Error("Exported file contains error");
      }

     while(!DDT.CurrentDriver.EOF()){
       //Test
       Log.message(DDT.CurrentDriver.Value(0)); 
      }

      // Close driver
      DDT.CloseDriver(DDT.CurrentDriver.Name); 

It throws Jscript error on first line because Sheet1 doesn't exist. 

Thanks in advance,

Shiva

[AlexKaras]

Hi Shiva,

I am not sure if this can be done using the DDT object, but you can get the list of the sheets that are within the given Excel file by using either ADO or Excel COM model. E.g.:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/create-list-of-tabs-in-excel/08db2...

http://stackoverflow.com/questions/13618224/how-to-get-sheets-name-from-excel-2007-xlsx-using-vb-net...

http://www.mathworks.com/matlabcentral/answers/102131-how-can-i-rename-a-sheet-in-excel-using-the-co...

[HKosova · Accepted solution]

As Alex said, you can use Excel's COM object, Excel.Application for that. Something along the lines of:

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

[Ravik]

Hey,

Sometime "

Sys.OleObject( "Excel.Application" )

" may give you error, so instead to "Sys.OleObject ()" use "CreateObject( "Excel.Application" )"

In order to be able to get an OLE object for an already running application, this application must be registered in the Running Object Table (ROT). However, Microsoft Office applications that are launched from the shell (for example, from the Start menu or the TestedApps project item) do not register their running objects at startup, but rather once the application loses focus. So, if you obtain an OLE object for an already running Office application, you may get errors when accessing its properties and methods (for more information on this problem, see http://support.microsoft.com/kb/238610/).