none
WorkSheet Copy from one workbook to another using interop with C3 2010

    Question

  • Trying to simply copy a worksheet from a source file to a destination file

    Code :

         private void button5_Click(object sender, EventArgs e)
            {
                object misValue = System.Reflection.Missing.Value;
                string MyOriginatingWorkBookName;
                MyOriginatingWorkBookName = textBox2.Text;
                string MyDestinationWorkBookName;
                MyDestinationWorkBookName = textBox1.Text;
                string MyOriginatingWorkSheetName;
                MyOriginatingWorkSheetName = listBox2.SelectedItem.ToString();
                string MyDestinationWorkSheetName;
                MyDestinationWorkSheetName = listBox1.SelectedItem.ToString();
               
                if ((MyOriginatingWorkSheetName != null) & (MyDestinationWorkSheetName != null))
                {
                    Excel.Application MyExcelApp = new Excel.Application();
                    MyExcelApp.Visible = true;
                    Excel.Workbook MyExcelWorkBookOriginating;
                    Excel.Workbook MyExcelWorkBookDestination;
                    Excel.Worksheet MyExcelWorkSheetOriginating;
                    Excel.Worksheet MyExcelWorkSheetDestination;
                    MyExcelWorkBookOriginating = MyExcelApp.Workbooks.Open(MyOriginatingWorkBookName, 0, false, misValue, misValue, misValue, false, misValue, misValue, false, true, misValue, true, true, misValue);
                    MyExcelWorkBookDestination = MyExcelApp.Workbooks.Open(MyDestinationWorkBookName, 0, false, misValue, misValue, misValue, false, misValue, misValue, false, true, misValue, true, true, misValue);
                    MyExcelWorkSheetOriginating = MyExcelWorkBookOriginating.Sheets[MyOriginatingWorkSheetName];
                    MyExcelWorkSheetDestination = MyExcelWorkBookDestination.Sheets[MyDestinationWorkSheetName];

                    MyExcelWorkSheetOriginating.Copy(misValue, MyExcelWorkSheetDestination);

                    MyExcelWorkBookDestination.Close(true, MyDestinationWorkBookName, false);
                    MyExcelWorkBookOriginating.Close(false, MyOriginatingWorkBookName, false);
                    MyExcelApp.Quit();


                    if (MyExcelWorkBookOriginating != null)
                    {
                        Marshal.ReleaseComObject(MyExcelWorkBookOriginating);
                    }
                    if (MyExcelWorkBookDestination != null)
                    {
                        Marshal.ReleaseComObject(MyExcelWorkBookDestination);
                    }
                    if (MyExcelApp != null)
                    {
                        Marshal.ReleaseComObject(MyExcelApp);
                    }

                }

    When it gets to the part of Saving the code faults with the following message:

    System.Runtime.InteropServices.COMException was unhandled
      HelpLink=xlmain11.chm
      HResult=-2146827284
      Message=Cannot access read-only document 'Tests 21.xlsx'.
      Source=Microsoft Excel
      ErrorCode=-2146827284
      StackTrace:
           at Microsoft.Office.Interop.Excel._Workbook.Close(Object SaveChanges, Object Filename, Object RouteWorkbook)
           at ExcelBuilder.Form1.button5_Click(Object sender, EventArgs e) in C:\Users\rzjp1z\Documents\Visual Studio 2010\Projects\ExcelBuilder\ExcelBuilder\Form1.cs:line 184
           at System.Windows.Forms.Control.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnClick(EventArgs e)
           at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
           at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
           at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
           at System.Windows.Forms.Application.Run(Form mainForm)
           at ExcelBuilder.Program.Main() in C:\Users\rzjp1z\documents\visual studio 2010\Projects\ExcelBuilder\ExcelBuilder\Program.cs:line 18
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 

    Any ideas as to why the destination file is being opened in Read Only Mode?

    Monday, April 01, 2013 4:20 PM

Answers

  • Hello Bernie, yes I did try it, but it also did not work.

    Luckily while troubleshooting I forgot to select the worksheet to copy to/after, and it faulted in debug mode.  However, it also left my excel in locked read only mode.  Upon investigation I arrived at the following code:

           private void getSheetsListBox2()
            {
                string MyFileName;
                MyFileName = textBox2.Text;
                Excel.Application MyExcelApp = new Excel.Application();
                
                Excel.Workbook MyExcelWorkBook;
                Excel.Worksheet MyExcelWorkSheet;
                MyExcelWorkBook = MyExcelApp.Workbooks.Open(MyFileName);

                int MySheetCount = MyExcelWorkBook.Sheets.Count;
                listBox2.Items.Clear();
                string MySheetName;

                for (int i = 1; i <= MySheetCount; i++)
                {
                    MyExcelWorkSheet = MyExcelWorkBook.Sheets[i];
                    MySheetName = MyExcelWorkSheet.Name;
                    listBox2.Items.Add(MySheetName);
                }

    Which is missing the closing of the excel book.  When I added the following:

              MyExcelWorkBook.Close(false, MyExcelWorkBook, false);
                MyExcelApp.Quit();
                if (MyExcelWorkBook != null)
                {
                    Marshal.ReleaseComObject(MyExcelWorkBook);
                }
                if (MyExcelApp != null)
                {
                    Marshal.ReleaseComObject(MyExcelApp);
                }

    It all worked like a charm.  Thanks for the help guys!

    Tuesday, April 02, 2013 1:19 PM

All replies

  • I would consider not opening the destination workbook, but instead add a blank workbook and then use saveAs to give it the new name after copying the sheet into it. That should bypass the read only mode.

    Note that copying worksheets can be problematic because of names, links, etc.  I usually like to copy the cells and paste into the new sheet, possibly values and formats to remove links, names, and formulas. The best solution of course depends on what it is that you actually want to do - which you do not describe, so it is hard to give advice based on limited information.

    Monday, April 01, 2013 5:20 PM
  • OK, here is the desired objective of the software.........

    user will create a blank excel file.

    Code is here for that.

          private void button1_Click(object sender, EventArgs e)
            {
                SaveFileDialog mySaveFileDialog = new SaveFileDialog();
                string MyFileName = "";
                mySaveFileDialog.InitialDirectory = "MyDocuments";
                mySaveFileDialog.Filter = "Excel File|*.xlsx";
                mySaveFileDialog.Title = "Save an Excel File";
                mySaveFileDialog.ShowDialog();
                if (mySaveFileDialog.FileName != "")
                {
                    MyFileName = mySaveFileDialog.FileName;
                    object misValue = System.Reflection.Missing.Value;
                    Excel.Application MyExcelApp = new Excel.Application();
                    MyExcelApp.DisplayFullScreen = true;
                    Excel.Workbook MyExcelWorkBook;
                    MyExcelWorkBook = MyExcelApp.Workbooks.Add(misValue);
                    MyExcelWorkBook.SaveAs(MyFileName, Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, misValue, misValue, true);

                    MyExcelWorkBook.Close();
                    MyExcelApp.Quit();
                    if (MyExcelWorkBook != null)
                    {
                        Marshal.ReleaseComObject(MyExcelWorkBook);
                    }
                    if (MyExcelApp != null)
                    {
                        Marshal.ReleaseComObject(MyExcelApp);
                    }
                }         
                mySaveFileDialog.Dispose();
            }

    This works properly and I can open the file using excel after it is created.

    The next objective is to allow the user to select existing excel files, one at a time, select a worksheet within the file, and then copy/insert it after the destination workbook's selected worksheet.  Prior to the Close method call, I can see that the worksheet is properly inserted, I just can't save the file because it behaves as a Read Only File.   I would do this with VB in excel except for the fact that there is ultimately so decent data analysis that will be done with the individual worksheets.

    Monday, April 01, 2013 5:40 PM
  • I don't know why the file is read-only, but I think the issue is that you are passing the same filename  to the close method, and it really doesn't want that - if you use the same name in a saveas, Excel asks if you want to overwrite - it might be that interaction that is bad.

    So instead of the Close with True to do the save, try this (VBA code, but you'll get the idea

    Application.DisplayAlerts = False

    MyExcelWorkBookDestination.Save

    MyExcelWorkBookDestination.close  False

    Application.DisplayAlerts = True

    Monday, April 01, 2013 5:57 PM
  • Hello Bernie, yes I did try it, but it also did not work.

    Luckily while troubleshooting I forgot to select the worksheet to copy to/after, and it faulted in debug mode.  However, it also left my excel in locked read only mode.  Upon investigation I arrived at the following code:

           private void getSheetsListBox2()
            {
                string MyFileName;
                MyFileName = textBox2.Text;
                Excel.Application MyExcelApp = new Excel.Application();
                
                Excel.Workbook MyExcelWorkBook;
                Excel.Worksheet MyExcelWorkSheet;
                MyExcelWorkBook = MyExcelApp.Workbooks.Open(MyFileName);

                int MySheetCount = MyExcelWorkBook.Sheets.Count;
                listBox2.Items.Clear();
                string MySheetName;

                for (int i = 1; i <= MySheetCount; i++)
                {
                    MyExcelWorkSheet = MyExcelWorkBook.Sheets[i];
                    MySheetName = MyExcelWorkSheet.Name;
                    listBox2.Items.Add(MySheetName);
                }

    Which is missing the closing of the excel book.  When I added the following:

              MyExcelWorkBook.Close(false, MyExcelWorkBook, false);
                MyExcelApp.Quit();
                if (MyExcelWorkBook != null)
                {
                    Marshal.ReleaseComObject(MyExcelWorkBook);
                }
                if (MyExcelApp != null)
                {
                    Marshal.ReleaseComObject(MyExcelApp);
                }

    It all worked like a charm.  Thanks for the help guys!

    Tuesday, April 02, 2013 1:19 PM