how to pass excel worksheet name path as string

[Priyanka]

Hi Telerik team,

I want to write the data in particular worksheet in excel.
I am using following code in test script.

string myPath = @"C:\Users\priyanka.kadam\Desktop\Automation_excel\Data_Driven_Test.xlsx";
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApp.Workbooks.Open(mypath);

Data_Driven_Test.xlsx is excel in which i have data in Patient_Select, SSN_Duplicate and MRN_Duplicate worksheets.
I want to write the data in SSN_Duplicate. For that i am using following path and then pass that string to open excel
string myPath = @"C:\Users\priyanka.kadam\Desktop\Automation_excel\Data_Driven_Test.xlsx[SSN_Duplicate]]"

Getting following error.

'8/29/2013 11:51:22 AM' - 'Fail' : 9. [SSN_Duplicate_CodedStep] : New Coded Step
------------------------------------------------------------
Failure Information:
~~~~~~~~~~~~~~~
Exception thrown executing coded step: '[SSN_Duplicate_CodedStep] : New Coded Step'.
InnerException:
System.Runtime.InteropServices.COMException (0x800A03EC): 'C:\Users\priyanka.kadam\Desktop\Automation_excel\Data_Driven_Test.xlsx[SSN_Duplicate].xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.

So tell me, how can i pass the particular worksheet name in file path.

[Velin Koychev]

Hi Priyanka,

Thank you for contacting Telerik Support.

I have slightly changed the code that you sent us in order to select the correct worksheet:

using Excel = Microsoft.Office.Interop.Excel;

/////////////

string myPath = @"C:\Users\priyanka.kadam\Desktop\Automation_excel\Data_Driven_Test.xlsx";

Excel.Application excelApp = new Excel.Application();

Excel.Workbook workbook = excelApp.Workbooks.Open(mypath);

//select the correct worksheet
Excel.Worksheet demoWorksheet = (Excel.Worksheet)excelApp.Worksheets["SSN_Duplicate"];
    
excelApp.Visible = false; 
//select the right cell in the correct worksheet
demoWorksheet.Cells[1, 3] = GetExtractedValue("DemoValue"); 
    
excelApp.ActiveWorkbook.Save();  
    
excelApp.Workbooks.Close(); 

 
As you can see, the code that you need to select the right worksheet is:
Excel.Worksheet demoWorksheet = (Excel.Worksheet)excelApp.Worksheets["SSN_Duplicate"];

Let me know if you need additional assistance.  

Regards,
Velin Koychev
Telerik