Copy range values in C#

B

BrassicaNigra

Greetings,

There are several examples of what I am trying to do in this section but
they all appear to use VB. I have an Excel add-in written in C#. One thing
I need to do is copy the values from a range of cells into a different range
of cells in another workbook. Here is the code;


private void Go_Click(object sender, EventArgs e)
{
String strTopSourceCell = "A2", strBottomSourceCell = "A46";
String strTopDestinationCell = "F16", strBottomDestinationCell =
"F60";
Excel.Worksheet activeWorksheet =
(Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range exrSource =
activeWorksheet.get_Range(strTopSourceCell, strBottomSourceCell);
Globals.ThisAddIn.ThisAddIn_OpenDigiKeyRFQTemplate();
activeWorksheet =
(Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range exrDestination =
activeWorksheet.get_Range(strTopDestinationCell, strBottomDestinationCell);
//exrSource.Copy(exrDestination);
//exrSource.Copy();

exrSource.Copy(exrDestination.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues,
Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, true, false));
}

When I use the (commented) line exrSrouce.Copy(exrDestination) I get the
values but the format of the destination is being altered.


When I use the (commented) line exrSrouce.Copy() I get a compiler error - no
overload for range.copy has zero arguments.

The documentation for Range.Copy() said that if the destination argument was
omitted it would copy to the clipboard. If I put null in there I get a run
time error.

I have tried using the Range.PasteSpecial as an argument for copy (the
uncommented line in my code) and it throws a runtime error. I suspect it is
to be used after the clipboard has been loaded.

How do I copy this range to the clipboard and then paste just the values to
the destination range (in C#).

Thanks for your help.

Dale Hoffman
 
J

Jie Wang [MSFT]

Hello Dale,

The PasteSpecial approach is right. Actually you are very close to the
solution now!

First, the Copy method can take zero arguments in VB because it supports
optional parameters, however this is not the story in C#. So we'll have to
pass in something. In this case, it would be Type.Missing.

And we need to specify correct parameters to the PasteSpecial method to
make it work.

I made a little bit changes to your code and please let me know if it works.

/* Begin code snippet */
String strTopSourceCell = "A2", strBottomSourceCell = "A46";
String strTopDestinationCell = "F16", strBottomDestinationCell = "F60";

Excel.Worksheet activeWorksheet =
(Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

Excel.Range exrSource = activeWorksheet.get_Range(strTopSourceCell,
strBottomSourceCell);

Excel.Range exrDestination =
activeWorksheet.get_Range(strTopDestinationCell, strBottomDestinationCell);

exrSource.Copy(Type.Missing);
exrDestination.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPas
teFormulas,

Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperati
onNone,
false,
false);
/* End code snippet */

Regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
B

BrassicaNigra

Thank you so much. This resolved my issue. I did use

Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues

instead of

Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormulas

because all I want to copy is the current value in the cell.

"Jie Wang [MSFT]" said:
Hello Dale,

The PasteSpecial approach is right. Actually you are very close to the
solution now!

First, the Copy method can take zero arguments in VB because it supports
optional parameters, however this is not the story in C#. So we'll have to
pass in something. In this case, it would be Type.Missing.

And we need to specify correct parameters to the PasteSpecial method to
make it work.

I made a little bit changes to your code and please let me know if it works.

/* Begin code snippet */
String strTopSourceCell = "A2", strBottomSourceCell = "A46";
String strTopDestinationCell = "F16", strBottomDestinationCell = "F60";

Excel.Worksheet activeWorksheet =
(Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet;

Excel.Range exrSource = activeWorksheet.get_Range(strTopSourceCell,
strBottomSourceCell);

Excel.Range exrDestination =
activeWorksheet.get_Range(strTopDestinationCell, strBottomDestinationCell);

exrSource.Copy(Type.Missing);
exrDestination.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPas
teFormulas,

Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperati
onNone,
false,
false);
/* End code snippet */

Regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Click to expand...
 
J

Jie Wang [MSFT]

Hi Dale,

Great to see now it works!

If you have any further questions, please feel free to post here and we'll
be happy to help.

Have a nice day!

Best regards,

Jie Wang ([email protected], remove 'online.')

Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business days is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"PasteSpecial method of Range class failed" when range Number of Rows > 2000 0
Copy Range Between Workbooks 1
Displaying a dataset or datatable in Excel 2
.NET Interop Copy all cells from Worksheet to Clipboard 3
Save Method Of Workbook Class Failed 0
Copy filtered data (Values only) 3
Collecting Range Name values to VBA 1
Copy values only 5

Top