Hi all,
Want to write some data from C# to Excel.
Code enough I thought, here at DANI's and on the web.
So I managed to come up with this :

private void DoExcel(string Fname)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excel = null;
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            Microsoft.Office.Interop.Excel.Range rng = null;

            object missing = Type.Missing;
            bool ReadOnly = false; //true or missing gives the same
            try
            {
                //Excel.Application is abstract class so I use this
                excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //If I use Open or _Open it gives the same
                wb = excel.Workbooks.Open(Fname, missing, ReadOnly, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing, missing);
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;      
                rng = ws.get_Range("A1", missing);
                rng.Value2 = "Some string";
                //All is well until here, Save thinks the excelfile is readonly
                excel.Save(Fname);
                excel.Quit();              
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            } 
        }

Whatever I do with ReadOnly , Save always thinks the excel file is read only. I even used Quit but then I get a dialog to save a copy.
What is wrong here and is there a solution?
Using Excel 2002 and Object Library 10.0.
Any suggestiions are welcome.

Recommended Answers

All 3 Replies

As far as I know you cannot save over a file that already exists. With Excel automation I always save the file to a temp file and overwrite the old file:

private void DoExcel()
    {
      const string Fname = @"C:\dotnetwin\daniweb\Book1.xlsx";
      Microsoft.Office.Interop.Excel.ApplicationClass excel = null;
      Microsoft.Office.Interop.Excel.Workbook wb = null;
      Microsoft.Office.Interop.Excel.Worksheet ws = null;
      Microsoft.Office.Interop.Excel.Range rng = null;

      object missing = Type.Missing;
      //bool ReadOnly = false; //true or missing gives the same
      try
      {
        //Excel.Application is abstract class so I use this
        excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
        //If I use Open or _Open it gives the same
        wb = excel.Workbooks.Open(Fname,
          missing, //updatelinks
          false, //readonly
          missing, //format
          missing, //Password
          missing, //writeResPass
          true, //ignoreReadOnly
          missing, //origin
          missing, //delimiter
          true, //editable
          missing, //Notify
          missing, //converter
          missing, //AddToMru
          missing, //Local
          missing); //corruptLoad
        ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;
        rng = ws.get_Range("A1", missing);
        rng.Value2 = "Some string";
        //All is well until here, Save thinks the excelfile is readonly
        string tmpName = Path.GetTempFileName();
        File.Delete(tmpName);        
        wb.SaveAs(tmpName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
        wb.Close(false, missing, missing);
        excel.Quit();
        File.Delete(Fname);
        File.Move(tmpName, Fname);
      }
      catch (Exception ex)
      {
        MessageBox.Show("Error: " + ex.ToString());
      }
    }
commented: Great guy! +6

Thanks Scott!
It works like I want it to, and it's so simple!
Besides, you have given me a great tip for the layout of all those "missing" things.

Can you point to me how to update it. I need to update data every day. It means the data last day is used, so it is not possibble to use this code because it lose the data.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.