How to copy a sheet with a different name - C# and Excel Interop

Question

I would like to simply copy one sheet within my workbook and give it a different name.

var pointName1 = workbook.Worksheets["PointName1"] as Worksheet;
pointName1.Copy(); // How do I access this newly created sheet?

Ideally I would like be able to write a method like this

pointName1.CopyTo("New Sheet");

where 'New Sheet' is a renamed copy of 'PointName1'.

Sometimes PointName1 will be the only sheet in the workbook, other times there will be others.

Solution 1

You should be able to use the Copy function, but you won't be able to rename the sheet in the same step. The MSDN documentation shows the additional parameters:

pointName1.Copy(pointName1, Type.Missing); //Place a copy before the existing sheet

From the documentation: If you do not specify either Before or After, Microsoft Office Excel creates a new workbook that contains the copied sheet.

To rename the sheet you'll need to get a reference to the new sheet (by index or name) and use the Name property of worksheet to change the name.

EDIT:

If you use the code above you can use the index of the original sheet (since you're placing the copy before the original):

int index = pointName1.Index;
pointName1.Copy(pointName1, Type.Missing);
Worksheet newWS = (Worksheet)xlApp.Worksheets[index];

Solution 2

well, other solution mentioned here did not work for me. I got this solution.

Step 1: Create a copy of the source file (i.e. TempFile )

Step 2: Copy desired sheet of source file to TempFile

Step 3: Delete the source file

Step 4: Rename TempFile to Source File.

Note: You will need the "Microsoft.Office.Interop.Excel" package from Nuget for this solution. Also, add using Excel = Microsoft.Office.Interop.Excel;

static void Main(string[] args)
{
    Excel.Application excelApp;

    string sourceFileName = "Original.xlsx"; //Source excel file
    string tempFileName = "temp.xlsx";

    string folderPath = @"C:\FodlerPath\";

    string sourceFilePath = System.IO.Path.Combine(folderPath, sourceFileName);
    string destinationFilePath = System.IO.Path.Combine(folderPath, tempFileName);

    System.IO.File.Copy(sourceFilePath,destinationFilePath,true);

    /************************************************************************************/

    excelApp = new Excel.Application();
    Excel.Workbook wbSource, wbTarget;
    Excel.Worksheet currentSheet;

    wbSource = excelApp.Workbooks.Open(sourceFilePath);
    wbTarget = excelApp.Workbooks.Open(destinationFilePath); 

    currentSheet = wbSource.Worksheets["Sheet1"]; //Sheet which you want to copy
    currentSheet.Name = "TempSheet"; //Give a name to destination sheet

    currentSheet.Copy(wbTarget.Worksheets[1]); //Actual copy
    wbSource.Close(false);
    wbTarget.Close(true);
    excelApp.Quit();

    System.IO.File.Delete(sourceFilePath);
    System.IO.File.Move(destinationFilePath, sourceFilePath);
}