Solved

Copy Excel sheet between two workbooks

Posted on 2010-09-20
7
525 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
Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

810 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