C# Excel forces ReadOnly with no Edit Priveledges

Question

I have an Excel Workbook ("test.xlsx") that has a cell named "gv_epxsize". My goal is to open the excel workbook and write down the string "101" in the cell with the name "gv_epxsize".

The problem is that my code keeps making the file Read Only, so it will not write the string "101" to the named cell. I keep receiving a windows prompt that I already have the file open with Windows privileges and asks if I want to open a Read Only copy. The problem is that I don't have the file open, and there is no EXCEL.EXE process running before I initiated the code.

What I've already done:

• I've saved the file as a different name in a different folder - still no luck.
• The file is not saved in a shared folder.
• I have made sure my TaskManager has every EXCEL.EXE process killed before I run my code.

Can someone please show me what I'm doing wrong here?

Here's the snippet of C#

  string filePath = "C:\\Users\\ussatdafa\\Desktop\\Work\\Projects\\test.xlsx";

  Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlApp == null)
        {
            MessageBox.Show("Excel has not been properly installed");
        }
        else
        {
            //string fileName = Path.Combine(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath), filePath);
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            Workbooks wbs = excelApp.Workbooks;
            Workbook wb = wbs.Open(filePath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);


            Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            wb.Names.Item("gv_epxsize").RefersToRange.Value = "101";
        }

Answer 1

I am pretty sure your issue is with WHERE in your code you are saving the file. From your posted code, there is one thing that does not really work and could explain your problem. Take a look at the following snippet:

else {
  //string fileName = Path.Combine(Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath), filePath);
  Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
  Workbooks wbs = excelApp.Workbooks;
  Workbook wb = wbs.Open(filePath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

  Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
  wb.Names.Item("gv_epxsize").RefersToRange.Value = "101";
}

This is the else portion of the if (xlApp == null) statement. First in this else portion you create a NEW Excel application called excelApp with:

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

Why you are doing this is unknown since you have already created one earlier called xlApp. But this is still ok; you are allowed to open more than one Excel application.

Then open the file and make changes:

Workbooks wbs = excelApp.Workbooks;
Workbook wb = wbs.Open(filePath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
wb.Names.Item("gv_epxsize").RefersToRange.Value = "101";

You open a workbook using the filePath and call the woorkbook wb. Then grab the first sheet in wb and assign it to a worksheet variable ws. Keep in mind you OPEN the workbook in this else clause. Make the changes to the workbook and then exit the else clause. All seems OK EXCEPT… since we have exited the else clause... excelApp, wbs, wb and ws variables NO LONGER EXIST. Even if you tried to save or close the file outside this else clause you will have no way to reference it because you CREATED those variables inside the else clause.

Since you can not create a new variable named wb if it already exist... Then it is clear the workbook wb is never getting saved or closed.

I put the following lines INSIDE your else clause and was able to open the file correctly even with your program running. It is clear you may be saving and closing the file somewhere later in your code but it’s not the file you opened in this else clause.

else {
  Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
  Workbooks wbs = excelApp.Workbooks;
  Workbook wb = wbs.Open(filePath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

  Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
  wb.Names.Item("gv_epxsize").RefersToRange.Value = "101";
  wb.Save();
  wb.Close();
  excelApp.Quit();
  System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
  System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}

Hope this makes sense.

Answer 2

I'm honestly not sure what's going on, but I have two observations:

1. You are opening Excel twice -- that appears to be certain. One instance is xlApp and the other is excelApp. If you open your task manager on the instantiation of excelApp I'm pretty sure you will find two instances of Excel running. I have no idea if that's contributing to your issue or not
2. I always found it helpful, when debugging, to make Excel visible. You can always comment that line out before you deploy, but during debugging it's nice since exposing COM objects during debugging isn't as helpful as native .NET objects -- this way you can see it in its native form

I ran a version of your code with these changes and had no issues changing the cell value of the named range "gv_epxsize."

string filePath = "C:\\Users\\ussatdafa\\Desktop\\Work\\Projects\\test.xlsx";
Microsoft.Office.Interop.Excel.Application excelApp =
    new Microsoft.Office.Interop.Excel.Application();

if (excelApp == null)
{
    MessageBox.Show("Excel has not been properly installed");
}
else
{
    excelApp.Visible = true;
    Workbook wb = excelApp.Workbooks.Open(filePath, 0, false, 5, "", "", false,
        XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    Worksheet ws = wb.Sheets[1];

    wb.Names.Item("gv_epxsize").RefersToRange.Value = "101";
}

This worked flawlessly for me. Here is proof:

So that leads me to several possibilities:

(Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);

Isn't what you think it is. Or perhaps, it's not the same as what I used, which was:

Worksheet ws = wb.Sheets[1];

Which will return the first worksheet ("Sheet1" on a new workbook).

And of course, there is the possibility that having two Excels open is causing issues.