How to copy data from one excel workbook to another RRS feed

  • Question

  • I am trying to copy selection of columns from test.xls workbook to another blank workbook called book1.xls. 

    when run the form below, it displays book.xls as blank.  I am little new into programming using excel-add in C# applications. 

    could someone please provide some guide or some assistance as to how I can debug the error on this form. 

    Many thanks

    private void button1_Click(object sender, EventArgs e) {

    //Test.xls xlWorkBook = xlApp.Workbooks.Open("C:/##/##/##/##/2015/Projects/###/Test.xls"); //Book.xls xlWorkBook2 = xlApp.Workbooks.Open("C:/##/##/##/##/2015/Projects/##/Book1.xls"); //~~> Display Excel xlApp.Visible = true; //~~> Set the source worksheet xlWorkSheet = xlWorkBook.Sheets["Sheet1"]; //~~> Set the destination worksheet xlWsheet2 = xlWorkBook2.Sheets["Sheet1"]; //~~> Set the source range xlSourceRange = xlWorkSheet.Range["E15"].EntireColumn; xlSourceRange1 = xlWorkSheet.Range["D15"].EntireColumn; //~~> Set the destination range xlDestRange = xlWsheet2.Range["A2"]; xlDestRange1 = xlWsheet2.Range["B2"]; //~~> Copy and paste the range //xlSourceRange.Copy(xlDestRange); //xlSourceRange1.Copy(xlDestRange1); xlSourceRange.Copy(Type.Missing); xlSourceRange1.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false); }

    Friday, June 5, 2015 1:49 PM


  • Hi,
     >> when run the form below, it displays book.xls as blank
    The code you provided would copy the xlSourceRange(which was in xlWorkSheet) ,and paste it on xlSourceRange1(which was still in xlWorksheet), if you want to copy data to another workbook, you should choose a range which was in another workbook, not the same workbook.You may could alter the code like this:

    //Source Range 
    xlSourceRange = xlWorkSheet.Range["E15"].EntireColumn;
    //destination range(xlWsheet2 was in another workbook)
     xlDestRange = xlWsheet2.Range["A2"].EntireColumn ;
        Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);
    The above code will copy data E column in sheet1 in test.xls  to A column in sheet1 in Book1.xlsx.

    Hope this could help you
    Best Regards.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, June 8, 2015 6:22 AM