locked
How to copy one worksheet to another WorkBook

    Question

  • Hi everyone, i am doing a project using the library Microsoft Excel 11.0 in C# and I am trying to copy one worksheet to another worksheet that is in a diferent Workbook. But I don´t know why it doesn´t works. So the problems is this.

    System.Runtime.InteropServices.COMException  Exception from HRESULT: 0x800A03EC.

    My code is like this:

    System.Threading.Thread.CurrentThread.CurrentCulture =
                    System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

                string Path = @"C:\Ejemplos excel\ejemplo 1.xls";
                string savepath = "C:\\test3.xls";
               
                    Excel.Application app = new Excel.ApplicationClass();
                    app.UserControl = true;
                    Excel.Workbook workBook = app.Workbooks.Open(Path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows,
                        "\t", false, false, 0, true, 1, 0);
                    workBook = app.ActiveWorkbook;
                   
                    Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
                    workSheet.Unprotect("pass");

                    Excel.Workbook workBook2 = (Excel.Workbook)(app.Workbooks.Add(Missing.Value));
              
                    workSheet.Copy(Missing.Value, workBook2.Sheets.Count);
       
                    workBook2.SaveAs(savepath, Excel.XlFileFormat.xlXMLSpreadsheet,
                        Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
           
                workBook.Close(false, Missing.Value, Missing.Value);
                app.Quit();

    I apprecided one answer. Thanks.

    Jhes

    Thursday, September 21, 2006 12:56 AM

All replies

  • Hi,

    Please check out this blog, hope this helps.
    http://blogs.msdn.com/carlosag/archive/2005/08/27/WhyIWroteExcelXmlWriter.aspx

    Thank you,
    Bhanu.

    Thursday, September 21, 2006 9:20 PM
  • I'm having the same problem with the same exception.

    Trying to copy a worksheet from one workbook to another and it doesn't work. Does anyone know why?

    Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
    Excel.Workbook wb = app.ActiveWorkbook;
    Excel.Workbook wb2 = app.ActiveWorkbook;
    Excel.Worksheet ws;
          try
          {
    wb = app.Workbooks.Open(path, false, false, missing, missing, missing, false, missing, missing, true, missing, missing, false, missing, missing);
    wb2 = app.Workbooks.Open(path2, false, false, missing, missing, missing, false, missing, missing, true, missing, missing, false, missing, missing);
          }
          catch
          {
            
          }
    ws = (Excel.Worksheet)wb.Worksheets[1];
    ws.Copy(wb2.Worksheets[1]);  
    

    Thursday, January 13, 2011 8:20 PM
  • Just in case anyone has a similar problem, I found out what the problem was: I was trying to copy a worksheet from a new Excel.Application object to a workbook belonging to a different Excel.Application object (the one created by VSTO). The problem was gone as soon as I used Globals.ThisWorkbook.Application instead of new Application.
    Monday, January 17, 2011 1:30 PM
  • Pls provide me the final code

    Thx for your help

    Wednesday, February 01, 2012 4:09 PM
  • Hello,

    Follow the links below. It amyu help you

    http://stackoverflow.com/questions/3808368/c-sharp-how-to-copy-a-single-excel-worksheet-from-one-workbook-to-another

    http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.copy%28VS.80%29.aspx

    Tuesday, February 07, 2012 5:57 AM
  • The subtopic in this forum is "Visual C# Express Edition", so your suggestion for using  Globals.ThisWorkbook.Application doesn't seem to apply.

    As far as I can make out from my reading and attempts to find it is the Globals class is not available in the Express edition. I becomes available in the premium or higher editions when selecting a template for developing a Microsoft Office project.

    My problem is, while I am able to successful a copy a Microsoft.Office.Interop.Excel.Worksheet into another Microsoft.Office.Interop.Excel.Workbook, I can not position it where I want it. When I specify the worksheet I want it to to be placed Before with the ImportedWorksheet.Copy(BeforeWorksheet, Missing.Value) method, it is always positioned at the end, before the last required sheet. Even when I attempt to then use the MovingWorksheet.Move(BeforeWorksheet, Missing.Value) method it still is not re-positioned.

    It may just be that I need to use a Globals .Workbook to get this to work, but as I said, I haven't found how to make that available in the Visual Studio C# 2010 Express edition.

    If anyone knows how to make that possible, I sure would appreciate it.


    • Proposed as answer by jox1958 Monday, May 21, 2012 1:34 PM
    • Unproposed as answer by jox1958 Monday, May 21, 2012 1:34 PM
    Monday, May 21, 2012 12:55 PM
  • Never mind.

    I found that my program was written properly. My problem wasn't with my code but with how I set up the workbook I used as a template for the workbooks I copied the sheets into.

    I wanted to import sheets and position them in front of some existing sheets, but I set those preexisting sheets in the workbook to be hidden. After I "unhide"d those sheets in the template and reran the program, the copy(before) worked properly and imported the new sheet in front of them.

    So, the worksheet.Copy(beforeWorksheet, missing) and the worksheet.Move(beforeWorksheet, missing) methods won't insert a worksheet in front of a sheet that is set to "hide" in the workbook.

    • Proposed as answer by jox1958 Monday, May 21, 2012 2:04 PM
    Monday, May 21, 2012 2:04 PM