Click here to Skip to main content
15,880,796 members
Please Sign up or sign in to vote.
2.14/5 (3 votes)
See more:
I have a code which basically takes the data from an excel file and copies it to another. I have been using the below code
C#
string pathFileSource = "C:\\Temp\\Output.xls";
                string pathFileDestination = "C:\\Temp\\Dest.xls";
                Excel.Application excel = new Excel.Application();
                Excel.Workbook wbSource = excel.Workbooks.Open(pathFileSource, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                Excel.Workbook wbDestination = excel.Workbooks.Open(pathFileDestination, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                Excel.Worksheet WorksheetSource = wbSource.Sheets[1];
                //Copy all range in this worksheet
                WorksheetSource.UsedRange.Copy(Missing.Value);
                Excel.Worksheet WorksheetDestination = wbDestination.Sheets[1];
                // Select used Range, paste value only
                //WorksheetDestination.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);
                WorksheetDestination.UsedRange.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationAdd, false, false);
                wbDestination.Save(); 
                wbSource.Close();
                wbDestination.Close();
                //Quit application
                excel.Quit();


Although the data is getting copied from the source sheet to Clipboard, it is not copied to the destination file. Can anyone point out to some other solution or help point out any gaps in the above code??
Posted
Comments
creepz03 12-Dec-13 22:55pm    
Do you want to copy the sheet to another sheet of the same workbook? Or do you want to copy a sheet in to a sheet of another workbook?

I found this on a thread and the user claims it works.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace CodeCall
{
    public class ExcelTest
    {
        public static void Main()
        {
            ApplicationClass app = new ApplicationClass();
            Workbook curWorkBook = null;
            Workbook destWorkbook = null;
            Worksheet workSheet = null;
            Worksheet newWorksheet = null;
Object defaultArg = Type.Missing;
try
{
// Copy the source sheet
curWorkBook = app.Workbooks.Open("c:\\Book1.xlsx", defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
workSheet = (Worksheet)curWorkBook.Sheets[1]; 
workSheet.UsedRange.Copy(defaultArg);

// Paste on destination sheet
destWorkbook = app.Workbooks.Open("c:\\Book2.xlsx", defaultArg, false, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet = (Worksheet)destWorkbook.Worksheets.Add(defaultArg, defaultArg, defaultArg, defaultArg);
newWorksheet.UsedRange._PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
}
catch (Exception exc)
{
System.Windows.Forms.MessageBox.Show(exc.Message);
}
finally
{
if (curWorkBook != null) {
curWorkBook.Save();
curWorkBook.Close(defaultArg, defaultArg, defaultArg);
}

if (destWorkbook != null)
{
destWorkbook.Save();
destWorkbook.Close(defaultArg, defaultArg, defaultArg);
}
}
            app.Quit();
        }
    }
}
 
Share this answer
 
Comments
Member 10426182 16-Dec-13 1:57am    
Thanks @bowlturner. That piece guided me towards a probable solution. My final code looks something like what I have mentioned below.
bowlturner 16-Dec-13 9:23am    
Glad I could help.
I was trying to copy all the data from 'Output.xls' file to the 'sheet1' of 'Dest.xls'. This is what my final code looks like-

C#
string pathFileDestination = "C:\\Dest.xls";
Excel.Application excel = new Excel.Application();
//excel.Visible = true;
Excel.Workbook wbDest = excel.Workbooks.Open(pathFileDestination, 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetDest = wbDest.Sheets[1];
//Clear all contents in Destination workbook
WorksheetDest.UsedRange.ClearContents();
WorksheetDest.get_Range("A1").Value = "No Data";
wbDest.Save();
wbDest.Close();
//Open the Source file
Excel.Workbook wbSource = excel.Workbooks.Open("C:\\Output.xls", 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetSource = wbSource.Sheets[1];
//Copy all range in this worksheet
WorksheetSource.UsedRange.Copy(Missing.Value);
//Open destination workbook
Excel.Workbook wbDestination = excel.Workbooks.Open(pathFileDestination, 0, false, 1, "", "", false, Excel.XlPlatform.xlWindows, 9, true, false, 0, true, false, false);
Excel.Worksheet WorksheetDestination = wbDestination.Sheets[1];
WorksheetDestination.UsedRange.PasteSpecial(Excel.XlPasteType.xlPasteAll, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, misValue, misValue);
wbDestination.Save();
wbSource.Close();
 
Share this answer
 
Comments
Member 11362443 15-Apr-15 4:12am    
That's a great solution. It works on local but doesn't work on my webserver.
Do I need to install Excel on the server?

Also how do I Insert new sheet after the first sheet?
Member 11638380 6-Oct-16 4:17am    
I am trying with above code but getting paste special method of range class failed exception
Member 11174000 15-Jun-15 3:29am    
work fine on trial...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900