How to Overwrite existing Excel file by adding new sheet with Excel Interop - C#

Question

My program, every hour, does some math calculations and saves these result into excel. When it first run(lets say 08:00 AM) it creates a excel workbook and one sheet namely "Sheet1". It saves excel and releases the COM objects. so far everything is fine.

My problem begins with second run (09:00 AM). when it tries to save new results, it overwrites existing excel file (This is OK, the way i want it) but it overwrites Sheet1 which was created in 08:00 AM. I want it to save new result in Sheet2.

In third run, i want it to save result in Sheet3
In fourth run, i want it to save result in Sheet4. so on so forth..

How can i change my code to do like above ? thanks in advance..

My Code:

using excelApp = Microsoft.Office.Interop.Excel;

public static void Main(string[] arg)
    {
        while (true)
        {               
            writeToExcel();
            int wait = 3600 * 1000;
            System.Threading.Thread.Sleep(Convert.ToInt32(wait));
        }
    }
public static void writeToExcel()
    {
        excelApp.Application excl = new Microsoft.Office.Interop.Excel.Application();
        excl.Visible = true;

        //MATH CALCULATIONS...... 

        excelApp.Workbook wb = excl.Workbooks.Add(excelApp.XlWBATemplate.xlWBATWorksheet);
        excelApp.Worksheet ws1 = (excelApp.Worksheet)wb.Worksheets[1];
        excelApp.Worksheet ws2 = (excelApp.Worksheet)wb.Sheets.Add();
        excelApp.Worksheet ws3 = (excelApp.Worksheet)wb.Sheets.Add();
        excelApp.Worksheet ws4 = (excelApp.Worksheet)wb.Sheets.Add();
        excelApp.Worksheet ws5 = (excelApp.Worksheet)wb.Sheets.Add();
        excl.DisplayAlerts = false;
        string fileName = string.Format(@"{0}\Data_" + DateTime.Now.Month + "-" DateTime.Now.Day  + ".xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
        workSheet.SaveAs(fileName);
        Console.WriteLine("Excel Saved Successfully!!");

        excl.Quit();

        // Release COM objects
        if (excl != null)                                       
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excl);

        if (workSheet != null)                   
        System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);

        excl = null;
        workSheet = null;

        GC.Collect();
}

First read your old file then save the current worksheets(e xcelApp.Worksheet ws1 = (excelApp.Worksheet)wb.Worksheets[1];) and then in your new Excel file you add the old worksheets + the new one, and you're done ! — Jacques Martin
The problem is the file name you're assigning in workSheet.SaveAs. It has Month and Day, so every run on the same day is going to overwrite. You need the Hour, at the very least... — Cindy Meister

Owen Ivory Owen Ivory · Accepted Answer · 2016-01-21T16:16:28

You need to get the work book from the saved file. So at the beginning of your routine, you need a mechanism to determine if today's file already exists, if so, use the the following to get your Workbook.

        Workbook WB = ExcelApp.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Hopefully this helps you see the error in your ways.

How to Overwrite existing Excel file by adding new sheet with Excel Interop - C#

1 Answers