How to overwrite an existing Excel file's sheet

Question

I am trying to overwrite/edit a column in a sheet in my excel file, even though it is not throwing any error, but still it is not even updating the excel sheet.

Here is my code:

Cursor.Current = Cursors.WaitCursor;
string path = Path.Combine(Application.StartupPath, ConfigurationManager.AppSettings["ExportTemplate"]);
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("MetaData");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
foreach (Excel.Worksheet oworksheet in mWorkBook.Worksheets)
{
    if (oworksheet.Name == "Disicpline")
    {
        long fullrow = oworksheet.Rows.Count;
        int rowcount = oworksheet.UsedRange.Rows.Count; //get row count
        //long lastrow = oworksheet.Cells[fullrow, 1].get_End(XlDirection.xlUp).Row;
        oworksheet.Cells[rowcount, 1].value = txtDiscCode.Text;
        oworksheet.Cells[rowcount, 2].value = txtDiscplinName.Text;
        oworksheet.Cells[rowcount, 3].value = txtDiscCode.Text + txtDiscplinName.Text;
        oworksheet.Cells[rowcount, 6].value = txtDiscCode.Text;
        oworksheet.Cells[rowcount, 7].value = txtDiscCode.Text;
    }
}
oXL.DisplayAlerts = false;
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
mWorkBook.Close(true, path, Type.Missing);
Marshal.ReleaseComObject(mWorkBook); 
GC.Collect();
GC.WaitForPendingFinalizers();
oXL.Quit();

Possible duplicate of [How to Save/Overwrite existing Excel file without message](https://stackoverflow.com/questions/25154717/how-to-save-overwrite-existing-excel-file-without-message) — Vijunav Vastivch, Apr 02 '19 at 05:36
@VijunavVastivch I have already gone through all possible answers related to my question, but none of them seem to be working for me. I have tried everything but still my excel sheet would not update. — Soniya Trivedi, Apr 02 '19 at 05:38
I suggest looking into one of the libraries that works with Excel. [ClosedXML](https://github.com/ClosedXML/ClosedXML), [EPPlus](https://github.com/JanKallman/EPPlus) and [SpreadsheetLight](http://spreadsheetlight.com/) are just a few of the options — mcalex, Apr 02 '19 at 05:43
Possibly related: https://stackoverflow.com/questions/5203150/openxml-spreadsheat-save-as — mcalex, Apr 02 '19 at 06:39
Have you set a breakpoint on the code that should modify the worksheet to make sure that it is being reached? `if (oworksheet.Name == "Disicpline")` looks suspiciously like a typo ("Discipline" ?), but only you can tell us that. — TnTinMn, Apr 02 '19 at 17:17

score 0 · Answer 1 · edited Apr 02 '19 at 05:42

0

File is just a file: You can use File.Exist()

if (File.Exists("ExcelFilenameHere"))
{
    File.Delete("ExcelFilenameHere");
}

use it before saving

edited Apr 02 '19 at 05:42

Uwe Keim

39,551
56
175
291

answered Apr 02 '19 at 05:40

Vijunav Vastivch

4,153
1
16
30

Sounds to me he does _not_ want to replace an entire file, but just _one_ sheet inside a multi-sheet Excel file instead. – Uwe Keim Apr 02 '19 at 05:43
It sounds like he need an update @uweKeim. Seems like the code is not. – Vijunav Vastivch Apr 02 '19 at 05:47
this code is deleting the existing excel file so when the debugger comes to 'SaveAs', it says file not found. – Soniya Trivedi Apr 02 '19 at 05:48
I just want to edit a certain column in the excel sheet simultaneously as I'm editing data in my datagridview column. – Soniya Trivedi Apr 02 '19 at 05:49
I've done creating an excel file for my past task.. but i started a blank excel file and saving it after to the designated directory. Now if the file exists before saving then you can do it from the answer above. – Vijunav Vastivch Apr 02 '19 at 05:52
the answer above is throwing a runtime error at mWorkBook.SaveAs, "file not found at ~path~" – Soniya Trivedi Apr 02 '19 at 06:22

How to overwrite an existing Excel file's sheet

1 Answers1