locked
read data from 2nd excel sheet in vb.net RRS feed

  • Question

  • User423751540 posted

    I Have two Excel Sheet. in one sheet Have Macro. and i want to read data in macro from 2nd sheet. plz tell me how i can do it

    Friday, December 24, 2010 6:26 AM

Answers

  • User1992938117 posted

    a) You can simply read the data from the two sheets into datasets using Jet , and create two datasets. and then do a Merge like, 
         DataSet dsOne = ReadFromWorkbook(sheet1);
         DataSet dsTwo = ReadFromWorkbook(sheet2);
         //Final dataset.
         dsOne.Merge(dsTwo);
        Now simply write the dsOne (the merged dataset into a new worksheet. And delete the first two worksheets. 
    b) Use Excel Interop (Add a Reference to Microsoft Excel xx Object Library.from the Com tab), and using the Worksheet.GetusedRange method for both the sheets. This would give you the data from the used ranges in the worksheets.  Do a copy on the usedranges from the worksheets - 1 & 2 and Paste these ranges in a new worksheet one after the other. 
    As a cleanup procedure, delete the initial two worksheets. 
    c) Use Visual Studio Tools for Office (known as 'VSTO') that provides some advanced functionality like Copying worksheets. and much more. 

    a) You can simply read the data from the two sheets into datasets using Jet , and create two datasets. and then do a Merge like, 

         DataSet dsOne = ReadFromWorkbook(sheet1);

         DataSet dsTwo = ReadFromWorkbook(sheet2);

         //Final dataset.

         dsOne.Merge(dsTwo);

        Now simply write the dsOne (the merged dataset into a new worksheet. And delete the first two worksheets. 


    b) Use Excel Interop (Add a Reference to Microsoft Excel xx Object Library.from the Com tab), and using the Worksheet.GetusedRange method for both the sheets. This would give you the data from the used ranges in the worksheets.  Do a copy on the usedranges from the worksheets - 1 & 2 and Paste these ranges in a new worksheet one after the other. 

    As a cleanup procedure, delete the initial two worksheets. 


    c) Use Visual Studio Tools for Office (known as 'VSTO') that provides some advanced functionality like Copying worksheets. and much more. 

    see:

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, December 24, 2010 7:21 AM