How to copy an excel sheet from workbook A to workbook B

Question

I have the below code to copy Sheet1 from a workbook (test1.xlsx) to another workbook (test2.xlsx). The code doesn't have any errors and it takes forever to execute and I had to stop the code with no change in the files. Please let me know what is wrong.

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

namespace Read_from_Excel_file
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApp = new Excel.Application();

            Excel.Workbook test1 = xlApp.Workbooks.Open(@"C:\Users\namokhtar\Desktop\test1.xlsx");

            Excel.Workbook test2 = xlApp.Workbooks.Open(@"C:\Users\namokhtar\Desktop\test2.xlsx");

            test2.Worksheets.Copy(test1.Worksheets["Sheet1"]);

            test2.Save();

            test1.Close();

            test2.Close();

            xlApp.Quit();

        }
    }
}

Did you try to set debugging flags on the lines of code to see where it is stuck? — Marthyn Olthof, Feb 20 '19 at 10:04
i did and it stops at the copy line but it doesn't produce any errors. — Tito, Feb 20 '19 at 10:11
How big is the worksheet you want to copy and how long did you wait? Did you consider it might just be taking a long time because it is a lot of data? — Marthyn Olthof, Feb 20 '19 at 10:13
it was a big one at the beginning, however i have taken many data out of the worksheets and now it is 9 rows by 9 columns. so it should be small and i have waited for almost 5 minutes without any change...can you see anything wrong with the code or the syntax? — Tito, Feb 20 '19 at 10:16

score 1 · Accepted Answer · answered Feb 20 '19 at 10:22

1

I think you need to specify where to copy the worksheet to. So at line

test2.Worksheets.Copy(test1.Worksheets["Sheet1"]);

you have to specify which worksheet in test2 you want to copy the worksheet in. test2.Worksheets["whateverworksheetyouwanttooverwrite"].Copy(test1.Worksheets["Sheet1"]);

Source: (https://learn.microsoft.com/nl-nl/visualstudio/vsto/how-to-programmatically-copy-worksheets?view=vs-2017)

answered Feb 20 '19 at 10:22

Marthyn Olthof

1,029
11
22

1

still the same case...no change...strangely also it is not releasing/quitting the files. when i end up the program and i try to open the excel files, it says for read only. – Tito Feb 20 '19 at 10:38
Were they read only before you started with this proces? Because that might be the issue here then. – Marthyn Olthof Feb 20 '19 at 10:56
You could try to remove the read only flag on the file before copying: https://stackoverflow.com/questions/7399611/how-to-remove-a-single-attribute-e-g-readonly-from-a-file – Marthyn Olthof Feb 20 '19 at 10:58
when i put the worksheet index, it did work. however, you have to have a sheet with an index already in the workbook. if you don't have an index and you try to copy into a new sheet, it won't work. – Tito Feb 20 '19 at 15:23

How to copy an excel sheet from workbook A to workbook B

1 Answers1