Copy/paste cells in Excel with C#

Question

How to select a specific user range in Excel file and copy those cells and Insert copied cells Shift:=xlDown using C#.

This is the VBA code I need to convert into C#:

Range("A9:L9").Select
Selection.Copy
Rows("10:10").Select
Selection.Insert Shift:=xlDown
Range("F10").Select

I don't know how to convert this code into C# code to run.

Solution 1

If you have'nt tried this yet, then you can try this

Add reference to your project in VS: Microsoft.Office.Interop.Excel

using Excel = Microsoft.Office.Interop.Excel;
class Program
{
    static void Main(string[] args)
    {
       var excelapp = new Excel.Application();
       excelapp.Workbooks.Add();
       string path = "Your Excel Path";            
       Excel.Workbook workbook = excelapp.Workbooks.Open(path);
       Excel.Worksheet workSheet = workbook.Worksheets.get_Item(1);
       Excel.Range source = workSheet.Range["A9:L9"].Insert(Excel.XlInsertShiftDirection.xlShiftDown);
       Excel.Range dest = workSheet.Range["F10"];
       source.Copy(dest);
    }
}

Solution 2

The below code execute copy-paste between Excel workbooks and optionally I've gave three ways depending of your needs. 1st - copy data from all rows and columns on the worksheet, 2nd - copy only data up to column B (2 cols) regardless if there's more data in other cols, 3rd - similar to opt2 but with paste special. Additionally it saves it all, closes and kills PID so there is no open Excel Com-Object.

using System;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Range = Microsoft.Office.Interop.Excel.Range;
using System.Diagnostics;

namespace Excel
{
class CopyPaste2
{
    public CopyPaste2()
    {
        //copy-paste dynamic range

        source2WB = @"C:\WIP\source2WB.xlsm";
        destinationWB = @"C:\WIP\destinationWB.xlsm";

        Application xlApp = new Application();
        xlApp.Visible = true;

        Workbook sourceWorkbook2 = xlApp.Workbooks.Open(source2WB, 0, false, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        Workbook destinationWorkbook = xlApp.Workbooks.Open(destinationWB, 0, false, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

        Worksheet SourceWorksheet = sourceWorkbook2.Worksheets.get_Item("Source2WSname");
        Worksheet DestinationWorksheet = destinationWorkbook.Worksheets.get_Item("destinationWSname");

        //Option 1 - copy data from all columns with value
        //Range last = SourceWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing);
        //Range sourceRng = SourceWorksheet.get_Range("A1", last);

        //Option 2 - copy only data up to column defined (i.e.A - 1 col, B - 2 cols etc.) regardless if there is more data in other columns
        Range last = SourceWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).End[XlDirection.xlToLeft];
        Range sourceRng = SourceWorksheet.get_Range("B1", last);

        //Option 3 - copy only data up to column defined (i.e.A - 1 col, B - 2 cols etc.) regardless if there is more data in other columns
        //Range last = SourceWorksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).End[XlDirection.xlToLeft];
        //Range sourceRng = SourceWorksheet.get_Range("B1", last);
        //      sourceRng.Copy(Missing.Value);

        //Option 3 - paste data into column 3 under last used row
        //Range destUsedRange = DestinationWorksheet.UsedRange;
        //int nRows = destUsedRange.Rows.Count +1;
        //Range destPaste = (Range)DestinationWorksheet.Cells[nRows, 3];
        //      destPaste.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

        //Option 1 and 2 - paste data into column 3 under last used row
        Range destUsedRange = DestinationWorksheet.UsedRange;
        int nRows = destUsedRange.Rows.Count +1;
        Range destPaste = (Range)DestinationWorksheet.Cells[nRows, 3];

        sourceRng.Copy(destPaste);

        destinationWorkbook.Save();
        sourceWorkbook2.Close();
        destinationWorkbook.Close();
        xlApp.Quit();

        int pid = -1;
        HandleRef hwnd = new HandleRef(xlApp, (IntPtr)xlApp.Hwnd);
        GetWindowThreadProcessId(hwnd, out pid);

        KillProcess(pid, "EXCEL");
    }


    [DllImport("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
    public static extern int GetWindowThreadProcessId(HandleRef handle, out int processId);
    static public void KillProcess(int pid, string processName)
    {
        // to kill current process of excel
        Process[] AllProcesses = Process.GetProcessesByName(processName);
        foreach (Process process in AllProcesses)
        {
            if (process.Id == pid)
            {
                process.Kill();
            }
        }
        AllProcesses = null;
    }
}
}

Comments

[Ben]

What have you tried? Nobody here will write your code for you. I think you should start by searching for similiar topics here and then try to adapt them to your problem. When you have some code, we are happy to help you :)

[Arnold_john]

I searched code but its not doing and implemented But its not working.Then i need to copy and insert a specfic range of cells to shift down i tried but only the whole rows are getting shifted down.The code above i written was the excel providing a option to record macro from that process i copied it

[Ben]

Edit your question and put the code in there. You will get more feedback with some code

[Jeremy Thompson]

Be careful using 2 dots in Excel Interop coding. Otherwise nice answer shows the OP the programming model (Interop vs VBA) is exactly the same except sqr brackets and line terminators.

[Arnold_john]

Thanks for giving this codes m facing still some problem Excel.Range source = workSheet.Range["A9:L9"].Insert(Excel.XlInsertShiftDirection‌​.xlShiftDown); in this line m geting error ie cannot convert bool to Microsoft.office.Interop.Excel.Range