Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# COM Excel
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...
 
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
77Jeff533
Edited 24-Sep-12 6:26am
v2
Comments
Kuthuparakkal at 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 at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
 
 
     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):
 
 
     Excel.Range original2 = worksheetSource.get_Range("A1" "A35" , Type.Missing);
 
     Excel.Range Destination1 = worksheetDestination.get_Range("A1", "A35" , Type.Missing);
 
 
Full Code:
 
 
     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();
            }
        }
 
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 881
1 OriginalGriff 270
2 Volynsky Alex 205
3 CPallini 180
4 Richard Deeming 175
0 OriginalGriff 5,640
1 Sergey Alexandrovich Kryukov 4,693
2 CPallini 4,540
3 George Jonsson 3,132
4 Gihan Liyanage 2,445


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 21 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100