Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy Excel sheet between two workbooks

Posted on 2010-09-20
7
Medium Priority
?
536 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 53

Expert Comment

by:Dhaest
ID: 33714507
0
 
LVL 42

Accepted Solution

by:
Meir Rivkin 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:Meir Rivkin
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Make the most of your online learning experience.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Six Sigma Control Plans

609 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