copying of specific range of excel cells from one worksheet to another worksheet

Question

I am writing a C# program which copies a range of cells from a worksheet of one workbook to a worksheet of an other workbook. But the problem I am facing is I am only able to copy and paste the whole worksheet of first workbook. I want to know how to select only a specific range(from row 5 [column 1 to column 10] to row 100 [column 1 to column 10]) and paste it in second workbook worksheet starting from row 2 column 8.

Also i want to know how a fill a column say from C1 to C100 with some value in a direct way instead of using the loop like below

for(i=1;i<2;i++) { for(j=1;j<101;i++) { worksheet.cells[i,j]="Fixed"; } }

Here is the code that i have written so far

using System; using System.Collections.Generic; using System.Linq; using System.Text; using Excel = Microsoft.Office.Interop.Excel; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { Excel.Application srcxlApp; Excel.Workbook srcworkBook; Excel.Worksheet srcworkSheet; Excel.Range srcrange; Excel.Application destxlApp; Excel.Workbook destworkBook; Excel.Worksheet destworkSheet; Excel.Range destrange; string srcPath; string destPath; //Opening of first worksheet and copying srcPath="C:\\Documents and Settings\\HARRY\\Desktop\\incident.csv"; srcxlApp = new Excel.Application(); srcworkBook = srcxlApp.Workbooks.Open(srcPath); srcworkSheet = srcworkBook.Worksheets.get_Item(1); srcrange = srcworkSheet.UsedRange; srcrange.Copy(Type.Missing); //opening of the second worksheet and pasting destPath = "C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report.xls"; destxlApp = new Excel.Application(); destworkBook = destxlApp.Workbooks.Open(destPath,0,false); destworkSheet = destworkBook.Worksheets.get_Item(1); destrange = destworkSheet.Cells[1, 1]; destrange.Select(); destworkSheet.Paste(Type.Missing, Type.Missing); destworkBook.SaveAs("C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report " + DateTime.Now.ToString("MM_dd_yyyy") + ".xls"); srcxlApp.Application.DisplayAlerts = false; destxlApp.Application.DisplayAlerts = false; destworkBook.Close(true, null, null); destxlApp.Quit(); srcworkBook.Close(false, null, null); srcxlApp.Quit(); } } }

mrtig · Accepted Answer · 2014-08-23 16:48:21Z

17

You should be able to do this:

 Excel.Range from = srcworkSheet.Range("C1:C100"); Excel.Range to = destworkSheet.Range("C1:C100"); from.Copy(to);

answered Aug 23, 2014 at 16:48

mrtig

2,26717 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8620003 Over a year ago

I keep getting "Non-invocable member '_Worksheet.Range[object, object]' cannot be used like a method" error using this.

Eman Pitts Over a year ago

use [ ] inplace of ( ) to get rid of non-invocable member <code> var from = sSheet.Range["A1:C168"]; var to = oSheet.Range["A1:C168"];</code>

James John McGuire 'Jahmic' · Accepted Answer · 2015-10-07 08:58:21Z

mrtig has a very elegant solution. But it won't work if you have the workbooks in separate instances of excel. So, the key is to open them in just one instance. I've modified your example to show using this approach:

public void CopyRanges() { // only one instance of excel Excel.Application excelApplication = new Excel.Application(); srcPath="C:\\Documents and Settings\\HARRY\\Desktop\\incident.csv"; Excel.Workbook srcworkBook = excelApplication.Workbooks.Open(srcPath); Excel.Worksheet srcworkSheet = srcworkBook.Worksheets.get_Item(1); destPath = "C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report.xls"; Excel.Workbook destworkBook = excelApplication.Workbooks.Open(destPath,0,false); Excel.Worksheet destworkSheet = destworkBook.Worksheets.get_Item(1); Excel.Range from = srcworkSheet.Range("C1:C100"); Excel.Range to = destworkSheet.Range("C1:C100"); // if you use 2 instances of excel, this will not work from.Copy(to); destworkBook.SaveAs("C:\\Documents and Settings\\HARRY\\Desktop\\FIXED Aging incident Report " + DateTime.Now.ToString("MM_dd_yyyy") + ".xls"); srcxlApp.Application.DisplayAlerts = false; destxlApp.Application.DisplayAlerts = false; destworkBook.Close(true, null, null); srcworkBook.Close(false, null, null); excelApplication.Quit(); }

What if I copy the entire row? Example: destworkSheet.get_Range("A1").EntireRow.Copy(); You said copy won't work if there is more than 1 instance. What if I have two instance of excel?
@Si8 Replacing the Range method with EntireRow.Copy in my example should work fine. My example only uses instance of excel. The problem with separate instances of excel is that it seems each instance has it's own clipboard. So, when you try to paste to second instance, there is nothing there to paste. So, just use a single instance.
Thank you for the response. Does that mean my line would work?
@Si8 I think so, but, as always, you will need to test and possibly debug to make sure.

kishore V M · Accepted Answer · 2014-08-23 17:16:40Z

For the First part of setting the same value for the entire range, instead of looping following will work out

range1 = workSheet.get_Range("A1:B100"); range1.Value = "Fixed";

And for copying you can try what @mrtig has suggested.

What if I copy the entire row? Example: destworkSheet.get_Range("A1").EntireRow.Copy();

Collectives™ on Stack Overflow

copying of specific range of excel cells from one worksheet to another worksheet

3 Answers 3

2 Comments

5 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

1 Comment

Linked

Related