?
Solved

Copy Excel sheet between two workbooks

Posted on 2010-09-20
7
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 53

Expert Comment

by:Dhaest
ID: 33714507
0
 
LVL 42

Accepted Solution

by:
sedgwick earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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…

770 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