Search code examples
c#exceloffice-interop

not able to copy excel column values to specified range in another excel file

I have written a small code to copy few columns from source excel file to another excel file (destination excel file) using c#. Below is sample image of source excel file.

enter image description here

The expected result in destination excel file should be as shown in below image. enter image description here

Below is my code 

    string fileTarget = @"C:\Users\sia\Desktop\Excel Automation\destination.xlsx";
    string fileTemplate = @"C:\Users\sia\Desktop\Excel Automation\source.xlsx";  

    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook wbTemp, wbTarget;
    Microsoft.Office.Interop.Excel.Worksheet sh;         


Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook wbSource = excel.Workbooks.Open(fileTemplate, ReadOnly: false);
    Microsoft.Office.Interop.Excel.Worksheet WorksheetSource = wbSource.Sheets[1];
    //Copy all range in this worksheet
    WorksheetSource.UsedRange.Copy(Type.Missing);


    //Open destination workbook
    Microsoft.Office.Interop.Excel.Workbook wbDestination = excel.Workbooks.Open(fileTarget, ReadOnly: false);
    Microsoft.Office.Interop.Excel.Worksheet WorksheetDestination = wbDestination.Sheets[1];
    WorksheetDestination.UsedRange.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
    wbDestination.SaveAs(@"C:\Users\sia\Desktop\Excel Automation\destination.xlsx");
    wbSource.Close();
    excel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

But the i'm not getting the expected format, below is the result I'm getting. enter image description here

where and what modification i need to do in my existing code to get the expected result.

Thanks

Solution

  • You need to specify proper column and range, otherwise by default paste will go in the first column

    workSheet.Range

    For reference: Copy/paste cells in Excel with C#