How to copy specific sheet name from excel and save it to a new location? Using C#

Question

I am using C# Windows Forms.

Goal:

If I have multiple excel sheets.

For example, "Sheet1, Sheet2, Sheet3, TestSheet1, TestSheet2"

How can I grab specific sheet name e.g. ["Sheet2"] and save it as a new excel workbook?

This is what I have so far:

Button Click:

private void button1_Click(object sender, EventArgs e)
{
    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Open("C:\Users\LV98\Desktop\Test C#\excel_file.xlsx");

    excelBook.Worksheets.Copy("Sheet2");
}

Update:

Here is where I got to.

private void button1_Click(object sender, EventArgs e)
{
    Excel.Application excelApp;

    string fileTarget = "C:\\Users\\LV98\\Desktop\\Test C#\\template.xlsx";
    string fileTemplate = "C:\\Users\\LV98\\Desktop\\Test C#\\excel_file.xlsx";
    excelApp = new Excel.Application();
    Excel.Workbook wbTarget;
    Excel.Worksheet sh;

    //Create target workbook
    wbTarget = excelApp.Workbooks.Open(fileTemplate);

    //Fill target workbook
    //Open the template sheet
    sh = wbTarget.Worksheets["Sheet2"];
    sh.Copy(wbTarget.Worksheets[1]);


    //Save file
    wbTarget.SaveAs(fileTarget);
    wbTarget.Close(true);
    excelApp.Quit();

}

When I open the new excel file, it opens "Sheet2", just what I was after! But only problem is, there is other sheets saved too.. I will be looking into renaming the new sheet - and delete the rest.

do you want to save all worksheet or only particular worksheet? — Udhay Titus, Mar 12 '20 at 12:09

Udhay Titus · Accepted Answer · 2020-03-13T09:03:56.207

1

just try this in your code it will be works

foreach (Excel.Worksheet sheet in wbTarget.Worksheets)
            {
                //Save Particular sheet as file
                if (sheet.Name == "Sheet2")
                {
                    var newbook = excelApp.Workbooks.Add(1);
                    sheet.Copy(newbook.Sheets[1]);

                    newbook.SaveAs(@"C:\Users\LV98\Desktop\Test C#\" + sheet.Name);
                    newbook.Close();
                }
            }

complete code of your button click event. I tested the below code and it's working fine

protected void button1_Click(object sender, EventArgs e)
    {
        Excel.Application excelApp;

        string fileTemplate = "C:\\Users\\LV98\\Desktop\\Test C#\\template.xlsx";            
        excelApp = new Excel.Application();
        Excel.Workbook wbTarget;

        //Create target workbook
        wbTarget = excelApp.Workbooks.Open(fileTemplate);

        foreach (Excel.Worksheet sheet in wbTarget.Worksheets)
        {
            //Save Particular sheet as file
            if (sheet.Name == "Sheet2")
            {
                var newbook = excelApp.Workbooks.Add(1);
                sheet.Copy(newbook.Sheets[1]);

                newbook.SaveAs(@"C:\Users\LV98\Desktop\Test C#\" + sheet.Name);
                newbook.Close();
            }
        }
        wbTarget.Close(true);
        excelApp.AskToUpdateLinks = false;
        excelApp.Quit();
        GetExcelProcess(excelApp);
    }
    [DllImport("user32.dll")]
    static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);
    Process GetExcelProcess(Excel.Application excelApp)
    {
        int id;
        GetWindowThreadProcessId(excelApp.Hwnd, out id);
        return Process.GetProcessById(id);
    }

edited Mar 13 '20 at 09:03

answered Mar 13 '20 at 04:54

Udhay Titus

5,761
4
23
41

@LV98 sorry I'm not getting, can you explain me the problem exactly? – Udhay Titus Mar 13 '20 at 08:43
this line of code `string fileTemplate = "C:\\Users\\LV98\\Desktop\\Test C#\\template.xlsx"; ` - is the source file where we are getting the sheet name and save it to a new location `newbook.SaveAs(@"C:\Users\LV98\Desktop\Test C#\" + sheet.Name);`- after the application is closed, the original file `template.xlsx` is locked for editing. – Eduards Mar 13 '20 at 08:51
go to task manager and select Process tab, in process tab check whether any process is running by name of `EXCEL.exe` – Udhay Titus Mar 13 '20 at 08:55
That's what I currently do - but need to prevent that from happening in the code. Will do some research into it – Eduards Mar 13 '20 at 08:56
did you tried this https://stackoverflow.com/a/17777840/6108882 or https://stackoverflow.com/a/15556843/6108882 – Udhay Titus Mar 13 '20 at 08:59
@LV89 can you check the updated answer, it might be works. – Udhay Titus Mar 13 '20 at 09:05
for `template.xlsx` folder give the permissions. Right click on the folder then click properties, then select Security tab and give edit it and give full control for all users. – Udhay Titus Mar 13 '20 at 09:14
can you try this https://social.msdn.microsoft.com/Forums/en-US/b81a3c4e-62db-488b-af06-44421818ef91/excel-2007-automation-on-top-of-a-windows-server-2008-x64 or run your application by Administrator login and use our above button click event. – Udhay Titus Mar 13 '20 at 09:36
Check new question - I have answered it there too. – Eduards Mar 13 '20 at 11:52

score 0 · Answer 2 · answered Mar 12 '20 at 12:15

After a bit of research I came across some useful links. I am pasting below a code snippet which I think will help you out:

IWorkbook sourceWorkbook = application.Workbooks.Open("Source.xlsx");
IWorkbook destinationWorkbook = application.Workbooks.Open("Destination.xlsx");

//Copy Excel worksheet from source workbook to the destination workbook
destinationWorkbook.Worksheets.AddCopy(sourceWorkbook.Worksheets[0]);

//Save the file
destinationWorkbook.Save();

I have found the above code in the following link: Copy Excel worksheet to another workbook

Also it I believe that you will find it helpful to read the following answer: How to copy a single Excel worksheet from one workbook to another?

How to copy specific sheet name from excel and save it to a new location? Using C#

2 Answers2