none
error in copy method for excel sheet in c# RRS feed

  • Question

  • Hi, I have a windows form application and I have a excel sheet that after some processing I should show the output on other excel sheet, I do not want to show main excel sheet but I should show the output result. so I used to excel object and I put one of them visible and the other not visible but when I want to copy the result on output excel it make an exception "Copy method of Range class failed". how can I solve the problem?

    Excel.Application xlApp = new Excel.Application(); Excel.Workbook SourceWorkbook; Excel.Application tarApp = new Excel.Application(); SourceWorkbook = xlApp.Workbooks.Open(@filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); tarApp.Visible = true; Excel.Workbook TargetWorkbook1; Excel.Worksheet SourceWorksheet; Excel.Worksheet TargetWorksheet1; SourceWorksheet = SourceWorkbook.Worksheets[1]; TargetWorkbook1 = tarApp.Workbooks.Add(); TargetWorksheet1 = TargetWorkbook1.Worksheets[1]; xlApp.ScreenUpdating = false; SourceWorksheet.Columns.AutoFilter(Y, "1"); SourceWorksheet.Columns.AutoFilter(P, "9100"); SourceWorksheet.Columns.AutoFilter(DP, "9100");

    SourceWorksheet.UsedRange.Copy(TargetWorksheet1.Range["A1"]);


    • Moved by Cindy Meister MVP Saturday, January 13, 2018 6:54 PM not using VSTO technology
    Tuesday, January 9, 2018 8:44 AM

Answers

  • Hello nadianaji,

    Since source workbook and target workbook are in different application instances, we could not copy and paste the data directly. We need to copy the data into clipboard and then paste from clipboard. Please note that this way need more time to decoding the format from the clipboard.

    Here is the example.

    Excel.Application xlApp = new Excel.Application();       
    Excel.Workbook SourceWorkbook;
    Excel.Application tarApp = new Excel.Application();
    SourceWorkbook = xlApp.Workbooks.Open(@filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
    tarApp.Visible = true;
    Excel.Workbook TargetWorkbook1;
    Excel.Worksheet SourceWorksheet;
    Excel.Worksheet TargetWorksheet1;
    SourceWorksheet = SourceWorkbook.Worksheets[1];
    TargetWorkbook1 = tarApp.Workbooks.Add();
    TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
    xlApp.ScreenUpdating = false;
    SourceWorksheet.Columns.AutoFilter(Y, "1");
    SourceWorksheet.Columns.AutoFilter(P, "9100");
    SourceWorksheet.Columns.AutoFilter(DP, "9100"); 
    SourceWorksheet.UsedRange.Copy();
    TargetWorksheet1.PasteSpecial("XML Spreadsheet");
    Clipboard.Clear();

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Terry Xu - MSFT Thursday, January 11, 2018 8:04 AM
    • Marked as answer by nadianaji Monday, January 15, 2018 3:32 PM
    Thursday, January 11, 2018 7:07 AM
  • Hello nadianaji,

    Please try to paste the range via PasteSpecial("XML Spreadsheet") in my previous reply.

    This is the only way I could find to copy and paste sheet range from an Excel application to another Excel application and keep its original format.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Monday, January 15, 2018 3:32 PM
    Monday, January 15, 2018 2:23 AM

All replies

  • Hello nadianaji,

    Since source workbook and target workbook are in different application instances, we could not copy and paste the data directly. We need to copy the data into clipboard and then paste from clipboard. Please note that this way need more time to decoding the format from the clipboard.

    Here is the example.

    Excel.Application xlApp = new Excel.Application();       
    Excel.Workbook SourceWorkbook;
    Excel.Application tarApp = new Excel.Application();
    SourceWorkbook = xlApp.Workbooks.Open(@filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    
    tarApp.Visible = true;
    Excel.Workbook TargetWorkbook1;
    Excel.Worksheet SourceWorksheet;
    Excel.Worksheet TargetWorksheet1;
    SourceWorksheet = SourceWorkbook.Worksheets[1];
    TargetWorkbook1 = tarApp.Workbooks.Add();
    TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
    xlApp.ScreenUpdating = false;
    SourceWorksheet.Columns.AutoFilter(Y, "1");
    SourceWorksheet.Columns.AutoFilter(P, "9100");
    SourceWorksheet.Columns.AutoFilter(DP, "9100"); 
    SourceWorksheet.UsedRange.Copy();
    TargetWorksheet1.PasteSpecial("XML Spreadsheet");
    Clipboard.Clear();

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Terry Xu - MSFT Thursday, January 11, 2018 8:04 AM
    • Marked as answer by nadianaji Monday, January 15, 2018 3:32 PM
    Thursday, January 11, 2018 7:07 AM
  • Hi,

    I used the below code but the target excel sheet only have one cell that reference to tempworksheets but and after deleting these tempworksheet it produce an error! why these information dose not copy on targetwroksheet?

    Excel.Application xlApp = new Excel.Application();
    Excel.Application tarApp = new Excel.Application();
    
                Excel.Workbook SourceWorkbook;
                Excel.Workbook TargetWorkbook1;
    
                Excel.Worksheet SourceWorksheet;
                Excel.Worksheet TargetWorksheet1;
    
    
                tarApp.Visible = true;
                Excel.Worksheet TargetWorksheet2;
                Excel.Worksheet TargetWorksheet3;
                TargetWorkbook1 = tarApp.Workbooks.Add();
     TargetWorksheet1 = TargetWorkbook1.Worksheets[1];
     TargetWorksheet2 = TargetWorkbook1.Worksheets.Add();
     TargetWorksheet3 = TargetWorkbook1.Worksheets.Add();
    
     SourceWorkbook = xlApp.Workbooks.Open(@"C:\Users\maedeh\Desktop\Base---961005.xls");
     SourceWorksheet = SourceWorkbook.Worksheets[1];
                
    
               
    Excel.Worksheet TempWorkSheet1 = SourceWorkbook.Worksheets.Add();
     Excel.Worksheet TempWorkSheet2 = SourceWorkbook.Worksheets.Add();
                                Excel.Worksheet TempWorkSheet3 = SourceWorkbook.Worksheets.Add();
                int lastColumnIndex = SourceWorksheet.Cells[1, SourceWorksheet.Columns.Count].End(Excel.XlDirection.xlToLeft).Column;
                int lastRowIndex = SourceWorksheet.Cells[SourceWorksheet.Rows.Count, 1].End(Excel.XlDirection.xlUp).Row;
    
                string AddressStr = SourceWorksheet.Cells[2, 121].Address[false, false];
                SourceWorksheet.Cells[2, lastColumnIndex + 1].Formula = string.Format("=OR({0}=9100,AND({0}>=1000,{0}<=1999))", AddressStr);
                SourceWorksheet.Cells[2, lastColumnIndex + 1].AutoFill(SourceWorksheet.Range[SourceWorksheet.Cells[2, lastColumnIndex + 1], SourceWorksheet.Cells[lastRowIndex, lastColumnIndex + 1]]);
                SourceWorksheet.Columns.AutoFilter(lastColumnIndex + 1, "TRUE");
                SourceWorksheet.UsedRange.Copy();
                TempWorkSheet1.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
                Clipboard.Clear();
                xlApp.ScreenUpdating = false;
                TempWorkSheet1.Columns.AutoFilter(16, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                TempWorkSheet1.Columns.AutoFilter(25, "1");
                TempWorkSheet1.UsedRange.Copy();
                TargetWorksheet1.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
                TargetWorksheet1.Name = "certain";
                int number1 = 0;
                if (TargetWorksheet1 != null)
                {
                    number1 = TargetWorksheet1.UsedRange.Rows.Count - 1;
                }
                SourceWorksheet.AutoFilterMode = false;
    
                xlApp.ScreenUpdating = false;
                AddressStr = SourceWorksheet.Cells[2, 16].Address[false, false];
                Clipboard.Clear();
                //replace 4 with Y, 5 with 16, 6 with 120 in your case
                SourceWorksheet.Columns.AutoFilter(121, "<1000",Excel.XlAutoFilterOperator.xlOr, ">1999");
                SourceWorksheet.UsedRange.Copy();
                TempWorkSheet2.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
    
                //filter temporary worksheet and export result to target worksheet
                TempWorkSheet2.Columns.AutoFilter(121, "<>9100");
                xlApp.ScreenUpdating = false;
                TempWorkSheet2.Columns.AutoFilter(16, ">=1000", Excel.XlAutoFilterOperator.xlAnd, "<=1999");
                TempWorkSheet2.Columns.AutoFilter(25, "1");
                TempWorkSheet2.UsedRange.Copy();
                TargetWorksheet2.Range["A1"].PasteSpecial(Excel.XlPasteType.xlPasteAllUsingSourceTheme);
                TargetWorksheet2.Name = "output";
                int number2 = 0;
                if (TargetWorksheet2 != null)
                {
                    number2 = TargetWorksheet2.UsedRange.Rows.Count - 1;
                }
                SourceWorksheet.AutoFilterMode = false;
                xlApp.ScreenUpdating =true ;
                tarApp.ScreenUpdating = true;
                Clipboard.Clear();
                SourceWorkbook.Worksheets[3].Delete();
                SourceWorkbook.Worksheets[2].Delete();
                SourceWorkbook.Worksheets[1].Delete();
    
    
                SourceWorkbook.Close(0);


    • Edited by nadianaji Friday, January 12, 2018 7:02 PM
    Friday, January 12, 2018 7:01 PM
  • Hello nadianaji,

    Please try to paste the range via PasteSpecial("XML Spreadsheet") in my previous reply.

    This is the only way I could find to copy and paste sheet range from an Excel application to another Excel application and keep its original format.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by nadianaji Monday, January 15, 2018 3:32 PM
    Monday, January 15, 2018 2:23 AM
  • Thank you very much. it works for me:)
    Monday, January 15, 2018 3:32 PM