Solved

Copy Excel sheet between two workbooks

Posted on 2010-09-20
7
526 Views
Last Modified: 2013-11-10
Hello

Please help me to
copy a sheet from workbook1 ----> Create a new sheet in workbook2 -----> paste the sheet from workbook1 to workbook2

Thanks Senz
//OPEN TADDAM 
        Excel.Workbook _Sbook;
        Excel.Worksheet _Ssheet;
        
        string strExpEveSheet = @"D:\ExportEvent\EXPScript\exportevents-20100920.csv";

        //OPEN NETEISS 
        Excel.Workbook _Dbook;
        Excel.Worksheet _Dsheet;
        string strNETEISSsrc = @"D:\ExportEvent\EXPScript\Network.xls";


_Sbook = ClsComm.OpenExcelWorkbook(strExpEveSheet);
            ClsComm.excel.Visible = true;            
            _Sbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //_Ssheet = (Excel.Worksheet)_Sbook.ActiveSheet;
            //_Ssheet.Select(Type.Missing);


            _Dbook = ClsComm.OpenExcelWorkbook(strNetworksrc);
            ClsComm.excel.Visible = true;
            _Dsheet = (Excel.Worksheet)_Sbook.ActiveSheet;
            _Dsheet.Select(Type.Missing);



            _Dsheet.UsedRange.Select();
            _Dsheet.UsedRange.Copy(Type.Missing);

            _Ssheet = (Excel.Worksheet)_Sbook.ActiveSheet;
            _Ssheet.Select(Type.Missing);

            _Ssheet.Paste(Type.Missing, Type.Missing);

Open in new window

0
Comment
Question by:Senz79
  • 3
  • 2
  • 2
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 33714397
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33714441
How to: Copy Worksheets
http://msdn.microsoft.com/en-us/library/ms178800.aspx
To add a copied worksheet to a workbook in a document-level customization
   Globals.Sheet1.Copy(missing, Globals.ThisWorkbook.Sheets[3]);

To add a copied worksheet to a workbook in an application-level add-in
   Excel.Worksheet worksheet1 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[1]);
   Excel.Worksheet worksheet3 = ((Excel.Worksheet)Application.ActiveWorkbook.Worksheets[3]);
   worksheet1.Copy(missing, worksheet3);

Open in new window

0
 
LVL 4

Author Comment

by:Senz79
ID: 33714458

_Dbook = ClsComm.OpenExcelWorkbook(strNETEISSsrc);
ClsComm.excel.Visible = true;
_Dsheet = (Excel.Worksheet)_Dbook.ActiveSheet;
_Dsheet.Select(Type.Missing);

_Sbook = ClsComm.OpenExcelWorkbook(strExpEveSheet);
ClsComm.excel.Visible = true;

_Sbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_Ssheet = (Excel.Worksheet)_Sbook.ActiveSheet;
_Ssheet.Select(Type.Missing);

_Dsheet.Copy(Type.Missing, _Ssheet);
 
 
I get the below error
System.Runtime.InteropServices.COMException was unhandled
  Message="Exception from HRESULT: 0x800A03EC"
  Source="Interop.Excel"
  ErrorCode=-2146827284
  StackTrace:
       at Excel._Worksheet.Copy(Object Before, Object After)
       at ExportEvent.Program.OpenExportEvent() in D:\ExportEvent\Export Event Project\ExportEvent\ExportEvent\Program.cs:line 94
       at ExportEvent.Program.Main(String[] args) in D:\ExportEvent\Export Event Project\ExportEvent\ExportEvent\Program.cs:line 31
       at System.AppDomain._nExecuteAssembly(Assembly 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.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:
 
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 53

Expert Comment

by:Dhaest
ID: 33714507
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
ID: 33714517
the following code is working (tested):
private static void CopyExcelWroksheet(string source, string dest, string worksheetName)
        {
            ApplicationClass excelApplicationClass = new ApplicationClass();
            _Workbook finalWorkbook = null;
            Workbook workBook = null;
            Worksheet workSheet = null;
            Worksheet newWorksheet = null;
            try
            {

                //Open the destination WorkBook  
                finalWorkbook = excelApplicationClass.Workbooks.Open(dest, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //Open the source WorkBook  
                workBook = excelApplicationClass.Workbooks.Open(source, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //Open the WorkSheet  
                workSheet = (Worksheet)workBook.Sheets[1];

                newWorksheet = (Worksheet)finalWorkbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                newWorksheet.Name = worksheetName;
                workSheet.Copy(Missing.Value, newWorksheet);   //Copy from source to destination  

                finalWorkbook.Save();
                workBook.Save();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (finalWorkbook != null)
                {
                    finalWorkbook.Close(true, Missing.Value, Missing.Value);
                }

                if (workBook != null)
                    workBook.Close(true, Missing.Value, Missing.Value);

                if (workSheet != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

                workSheet = null;

                if (workBook != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);

                if (finalWorkbook != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(finalWorkbook);

                workBook = null;

                if (excelApplicationClass != null)
                {
                    excelApplicationClass.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplicationClass);
                    excelApplicationClass = null;
                }
            }

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 33714548
example for usage:

CopyExcelWroksheet(@"c:\temp\src.xlsx", @"c:\temp\dest.xlsx", "Worksheet1");
0
 
LVL 4

Author Comment

by:Senz79
ID: 33714790
It worked but there is a flaw
A new sheet to be created and then the data need to be pasted, in this code the existing sheet was deleted and new sheet with the copied data was pasted.
I need to keep the original sheet as it is.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question