asked on

Copy Excel sheet between two workbooks

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

Meir Rivkin

check this posts:
http://forums.asp.net/p/1457463/3340354.aspx
http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/b31bf687-2549-436a-8997-c11c9f3258ed

Dirk Haest

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

Senz79

ASKER

_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:

Dirk Haest

My previous comment was for copying inside the same workbook ...

http://www.eggheadcafe.com/community/aspnet/66/10142380/copy-a-specific-worksheet-to-another-workbook.aspx

ASKER CERTIFIED SOLUTION

Meir Rivkin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Meir Rivkin

example for usage:

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

Senz79

ASKER

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.