how to pass excel worksheet name path as string

2 posts, 0 answers
  1. Priyanka
    Priyanka avatar
    21 posts
    Member since:
    Jan 2013

    Posted 29 Aug 2013 Link to this post

    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.
  2. Velin Koychev
    Admin
    Velin Koychev avatar
    88 posts

    Posted 02 Sep 2013 Link to this post

    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
    The New Release of Telerik Test Studio Is Here! Download, install,
    and send us your feedback!
Back to Top