C# Excel How to copy/paste non contiguous ranges programatically

Question

0.00/5 (No votes)

See more:

How can I use Excel COM C# to copy non contiguous ranges and paste those ranges to another WorkBook?
The Sheets cannot have other cells outside of the defined ranges affected. Some of my Destination Ranges will be different than the Source Ranges.
Here is some of my code. It gives compiler error "That command cannot be used on multiple selections.... " I have even tried to perform the copy and paste using application.union to no effect.
Please, can any one help me with this? Most of what I have found involved full sheets or contiguous ranges...

C#

Excel.Range original1 = worksheetSource.get_Range("A1:A9, A15:A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1:A9, A15:A35", Type.Missing);

original1.Copy(Type.Missing);
                Destination1.PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteAll, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);

Posted 23-Sep-12 20:21pm

77Jeff

Updated 24-Sep-12 6:26am

v2

Add a Solution

Comments

Kuthuparakkal 24-Sep-12 16:08pm

Use a hidden sheet with a continuous rectangular range, set names to its parts and use these names on all other sheets.

77Jeff 24-Sep-12 17:58pm

I cannot modify the destination sheet with anything other than the pasted values. By naming the parts on the destination sheets wouldn't it affect scripting currently in place in the Destination Workbook? I am thinking this will have to be done with a loop.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

John d. Bartels · Answer 1 · 2012-10-21T06:32:00

I would suggest making the first call and grabbing the data from the first sheet in the form of a DataTable. Then set the values of the destination sheet and pass in the data table from the previous method. Can you split this up into two calls? and use the get_Range method to get a range of cells by defining the startCell, and endCell.

C#

Excel.Range original1 = worksheetSource.get_Range("A1", "A9" , Type.Missing);

Excel.Range original2 = worksheetSource.get_Range("A15" "A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1", "A9" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A15" "A35" , Type.Missing);

Of course, instead of splitting this into two pieces you, could also do the following (Assuming there is no data from A10 to A14):

C#

Excel.Range original2 = worksheetSource.get_Range("A1" "A35" , Type.Missing);

Excel.Range Destination1 = worksheetDestination.get_Range("A1", "A35" , Type.Missing);

Full Code:

C#

string fileName1 = @"C:\testDir\Book1.xls";
string fileName2 = @"C:\testDir\Book2.xls";

System.Data.DataTable test1 = new System.Data.DataTable();

test1 = getCellRangeValueAsDataTable(fileName1, "A1", "A9");

setCellRangeValueWithDataTable(fileName2, "A1", "A9", test1);

public System.Data.DataTable getCellRangeValueAsDataTable(string filename, string startCell, string endCell)
   {
       System.Data.DataTable newTable = new System.Data.DataTable();

       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
       string result = "";
       if (xlApp == null)
       {
           MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
           return newTable;
       }
       //xlApp.Visible = true;

       Workbook wb = xlApp.Workbooks.Open(fileName,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing);
       Worksheet ws = (Worksheet)wb.Worksheets[1];

       if (ws == null)
       {
           MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
           return newTable;
       }

       // Select the Excel cells, in the range startcell to endcell in the worksheet.
       Range aRange = ws.get_Range(startCell, endCell);

       if (aRange == null)
       {
           MessageBox.Show("Could not get a range. Check to be sure you have the correct versions of the office DLLs.");
           return newTable;
       }

       for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
       {
           newTable.Columns.Add("String"+cCnt, typeof(string));
       }

       for (int rCnt = 1; rCnt <= aRange.Rows.Count; rCnt++)
       {
           object[] objArr = new object[aRange.Columns.Count];

           for (int cCnt = 1; cCnt <= aRange.Columns.Count; cCnt++)
           {
               objArr[cCnt - 1] = (string)(aRange.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2;
           }
           newTable.Rows.Add(objArr);
       }


       var val = aRange.Value2;

       wb.Close(true, Type.Missing, Type.Missing);

       xlApp.Quit();

       releaseObject(ws);
       releaseObject(wb);
       releaseObject(xlApp);

       return newTable;
   }

   /// <summary>
   /// sets the values for a given cell range by utilizing values from a specified datatable
   /// </summary>
   /// <param name="filename"></param>
   /// <param name="startCell"></param>
   /// <param name="endCell"></param>
   /// <param name="values"></param>
   public void setCellRangeValueWithDataTable(string filename, string startCell, string endCell, System.Data.DataTable values)
   {
       Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

       if (xlApp == null)
       {
           MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are correct.");
           return;
       }
       //xlApp.Visible = true;

       Workbook wb = xlApp.Workbooks.Open(fileName,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,
               Type.Missing, Type.Missing);
       Worksheet ws = (Worksheet)wb.Worksheets[1];

       if (ws == null)
       {
           MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
       }
       Range aRange = ws.get_Range(startCell, endCell);

       for (int i = 0; i <= values.Rows.Count - 1; i++)
       {
           for (int j = 0; j <= values.Rows[i].ItemArray.Count() - 1; j++)
           {
               aRange.Cells[i + 1, j + 1] = values.Rows[i].ItemArray[j];
           }
       }

       wb.Close(true, Type.Missing, Type.Missing);

       xlApp.Quit();

       releaseObject(ws);
       releaseObject(wb);
       releaseObject(xlApp);
   }

private void releaseObject(object obj)
   {
       try
       {
           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
           obj = null;
       }
       catch (Exception ex)
       {
           obj = null;
           MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
       }
       finally
       {
           GC.Collect();
       }
   }