none
[Dummy] How to Copy-Paste a Excel selection in C#?

    Question

  • Hello,
    I have, probably, a dummy question, but I've searched a little and not found explicitly, how to copy paste a Excel selection through C# (keeping the same formatting)?

    the code should be like bellow:

    Code Snippet

                    excelWorksheetSource.UsedRange.Select();

                    selection.Copy(); // ???

                    Range r = excelWorksheetDestination.get_Range("A1", Missing.Value);

                    r.PasteSpecial(XlPasteType.xlPasteAll, XlPasteSpecialOperation.xlPasteSpecialOperationNone,

                        Missing.Value, Missing.Value);


    Monday, April 21, 2008 1:39 PM

Answers

  • Hi,

     

    These codes work fine on my side:

    Code Snippet

                excel = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

                Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet;

                Excel.Worksheet sheet2 = excel.Worksheets[2] as Excel.Worksheet;

                sheet2.UsedRange.Copy(Type.Missing);

                Excel.Range range = sheet1.Cells[1, 1] as Excel.Range;

                range.Select();

                sheet1.Paste(Type.Missing, Type.Missing);

     

     

    It copies Sheet2’s whole scope filled with data into Sheet1, begin from “A1” cell.

     

     

    Thanks,

    Ji

     

    Friday, April 25, 2008 7:54 AM
    Moderator
  •  Cindy Meister wrote:

    Everything that's available is in the Help topics for the methods Paste and PasteSpecial. Have you looked at them?

     

    If you can't find them, one way to access them is to

     

    1. Start excel

    2. Press Alt+F11 to start the macro editor

    3. Press Ctrl+G to bring up the immediate window

    4. Type the term in the immediate window, then press F1 to call the Help on that term



    I prefer Visual Studio MSDN F1 on PasteSpecial.

    Code Snippet
    private void CopyAndPasteSpecialRange()
    {
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
    this.Controls.AddNamedRange(this.Range["A1", "A3"],
    "namedRange1");
    namedRange1.Value2 = 22;

    Microsoft.Office.Tools.Excel.NamedRange namedRange2 =
    this.Controls.AddNamedRange(this.Range["C1", "C3"],
    "namedRange2");
    namedRange2.Value2 = 5;

    // Copy the contents of namedRange1 to the clipboard, and then
    // paste the contents into namedRange2, adding each to
    // the value in namedRange2.
    namedRange1.Copy(missing);
    namedRange2.PasteSpecial(Excel.XlPasteType.xlPasteAll,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd,
    false,
    false);
    }

     
    Thanks to everybody for help!
    Friday, April 25, 2008 8:49 AM
  • Yes,

     

    If you want to specify Paste Option, you need to use PasteSpecial() method of Sheet or Range object. The following link is official document about this method:

    http://msdn2.microsoft.com/en-us/library/aa195818(office.11).aspx

    With specifying the first parameter to the following different constants, the paste effective will differs from your choice:

    xlPasteAll default

    xlPasteAllExceptBorders

    xlPasteColumnWidths

    xlPasteComments

    xlPasteFormats

    xlPasteFormulas

    xlPasteFormulasAndNumberFormats

    xlPasteValidation

    xlPasteValues

    xlPasteValuesAndNumberFormats

    You can have a try for each constant and each parameter, and see what the different behavior is.

     

     

    Thanks,

    Ji

     

    Friday, April 25, 2008 8:55 AM
    Moderator

All replies

  • Unfortunately, you don't state exactly what your problem is. The description implies the formatting is the problem, but your code sample questions the Copy method...

     

    In any case, according to the object model Help the Copy method expects a parameter:

     

    expression.Copy(Destination)

     

    If you don't want to specify the Destination as part of the Copy method, then you have to pass System.Type.Missing as the parameter value. C# does not allow you to ignore parameters as Visual Basic does.
    Tuesday, April 22, 2008 8:18 AM
    Moderator
  • Ye, I would like to precise that I would like to simulate a copy paste.
    First of all, copy to the clipboard, like Ctrl+C, and paste then, like Ctrl+V.

    Keeping the same formatting is the second step. it's like if I select in Excel the paste option.


    well I would like to do something like:

    Code Snippet

    private SimulateUserCopyPaste()
    {
    ***
    selection.Copy(); // to the clipboard

    newWorksheet.get_Range("A1").Paste(PasteOptions.UseTheSourceFormatting); // copy from the clipboard.

    ***
    }



    is it possible?
    Tuesday, April 22, 2008 9:38 AM
  • Hi Sergiu,

     

    Yes, that is possible! If you using Type.Missing to omit all parameters in Copy() and Paste(), the default option PasteAll will be adopted. That will make your text with original format copied to the target cell.

    Another approach will be using .Net SendKeys.Send() function to simulate Ctrl+C and Ctrl+V process.

    Have a try and if you have any future problems about this, welcome to post here.

     

     

    Thanks,

    Ji

     

    Friday, April 25, 2008 2:53 AM
    Moderator
  • Hi, Ji Zhou


    Could you bring me as example a little snatch of code?

    I don't understand what (whose) exacly Copy() to apply


    Thanks.

    Friday, April 25, 2008 6:42 AM
  • Hi,

     

    These codes work fine on my side:

    Code Snippet

                excel = Marshal.GetActiveObject("Excel.Application") as Excel.Application;

                Excel.Worksheet sheet1 = excel.Worksheets[1] as Excel.Worksheet;

                Excel.Worksheet sheet2 = excel.Worksheets[2] as Excel.Worksheet;

                sheet2.UsedRange.Copy(Type.Missing);

                Excel.Range range = sheet1.Cells[1, 1] as Excel.Range;

                range.Select();

                sheet1.Paste(Type.Missing, Type.Missing);

     

     

    It copies Sheet2’s whole scope filled with data into Sheet1, begin from “A1” cell.

     

     

    Thanks,

    Ji

     

    Friday, April 25, 2008 7:54 AM
    Moderator
  • Thanks, Ji,

    I've tried simply this
    Code Snippet

                Worksheet worksheet1 = excelWorkbook.Worksheets[1] as Worksheet;

                Worksheet worksheet2 = excelWorkbook.Worksheets[2] as Worksheet;

                worksheet1.UsedRange.Copy(Missing.Value);

                worksheet2.Paste(Missing.Value, Missing.Value);



    and it works!

    Can you say also if some paste options are available?
    Such as use the same formatting as source or destination, mantain the source column width, etc..
    Friday, April 25, 2008 8:09 AM
  •  Sergiu Dudnic wrote:
    Thanks, Ji,

    Can you say also if some paste options are available?
    Such as use the same formatting as source or destination, mantain the source column width, etc..

    Everything that's available is in the Help topics for the methods Paste and PasteSpecial. Have you looked at them?

     

    If you can't find them, one way to access them is to

     

    1. Start excel

    2. Press Alt+F11 to start the macro editor

    3. Press Ctrl+G to bring up the immediate window

    4. Type the term in the immediate window, then press F1 to call the Help on that term

    Friday, April 25, 2008 8:38 AM
    Moderator
  •  Cindy Meister wrote:

    Everything that's available is in the Help topics for the methods Paste and PasteSpecial. Have you looked at them?

     

    If you can't find them, one way to access them is to

     

    1. Start excel

    2. Press Alt+F11 to start the macro editor

    3. Press Ctrl+G to bring up the immediate window

    4. Type the term in the immediate window, then press F1 to call the Help on that term



    I prefer Visual Studio MSDN F1 on PasteSpecial.

    Code Snippet
    private void CopyAndPasteSpecialRange()
    {
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
    this.Controls.AddNamedRange(this.Range["A1", "A3"],
    "namedRange1");
    namedRange1.Value2 = 22;

    Microsoft.Office.Tools.Excel.NamedRange namedRange2 =
    this.Controls.AddNamedRange(this.Range["C1", "C3"],
    "namedRange2");
    namedRange2.Value2 = 5;

    // Copy the contents of namedRange1 to the clipboard, and then
    // paste the contents into namedRange2, adding each to
    // the value in namedRange2.
    namedRange1.Copy(missing);
    namedRange2.PasteSpecial(Excel.XlPasteType.xlPasteAll,
    Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd,
    false,
    false);
    }

     
    Thanks to everybody for help!
    Friday, April 25, 2008 8:49 AM
  • Yes,

     

    If you want to specify Paste Option, you need to use PasteSpecial() method of Sheet or Range object. The following link is official document about this method:

    http://msdn2.microsoft.com/en-us/library/aa195818(office.11).aspx

    With specifying the first parameter to the following different constants, the paste effective will differs from your choice:

    xlPasteAll default

    xlPasteAllExceptBorders

    xlPasteColumnWidths

    xlPasteComments

    xlPasteFormats

    xlPasteFormulas

    xlPasteFormulasAndNumberFormats

    xlPasteValidation

    xlPasteValues

    xlPasteValuesAndNumberFormats

    You can have a try for each constant and each parameter, and see what the different behavior is.

     

     

    Thanks,

    Ji

     

    Friday, April 25, 2008 8:55 AM
    Moderator