Help needed with manipulating Excel files!

[kevin.cochrane]

Hi there

If anybody could help me with this problem it would be great. I'm a noob when it comes to c#, and I know people loathe the idea of using c# to manipulate excel files but if you had my job you would understand. I have to sort through hundreds of excel files and generate hundreds of graphs- so I am writing an app to do that for me.

So far I have got the app to open the files, delete the unnecessary data, and generate a graph for me. Great stuff! Now the problem is I'm trying to figure out how to save the files automatically so that the program will essentially open excel, change everything, save and close everything without the user ever even seeing excel.

Two issues:

1. I need to save the files as an xls and not a csv- which is their original format. When the program reaches this section of my code:

   oWB.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
   Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
   oWB.Close(true, filename, Missing.Value);

I have an error thrown at me: Exception from HRESULT: 0x800A03EC

1. I need the excel processes that are opened into my task manager to close when excel exits after saving. I know that everyone has this problem but I have not found a solution that works yet.

CODE: (You can see I've been fiddling with different methods)

private void Close1()
        {
           int length;

            //oXL.Visible=true;


            length = filename.Length;
            length = length - 3;
            //filename = filename.Remove(length, 3);
            filename = filename.Remove(length, 3) + "xls";


            oWB.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
           Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
           oWB.Close(true, filename, Missing.Value);

            releaseObject(oSheet);
            releaseObject(oWB);
            releaseObject(oXL);

            //MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");

           // foreach (Process p in Process.GetProcessesByName("EXCEL"))
            //{
             //   if (oXL.Caption == p.MainWindowTitle)
             //   {
             //       p.Kill();
              //      break;
                }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

ANY help would be magnificent as I've been battling this for 2 days.

Thanks
Kevin

[sknake]

Hello Kevin and welcome to daniweb!

Please use code tags when you post code on the forum:

To answer your question here is a code snippet from an old post:

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());
      }
    }

Borrowed from here

[kevin.cochrane]

Thanks sknake that works brilliantly!

Your help is much appreciated. I'm still looking for help with the excel processes not closing.

Thanks again.

[sknake]

Please post your updated code in code tags so I can run it locally. It should be closing the Excel process when you call .Close() so something seems to be a little odd here.

[kevin.cochrane]

Yeah sorry I had edited that since my previous post and forgot! My bad. It's working fine now. Thanks for your help again!

Please post your updated code in code tags so I can run it locally. It should be closing the Excel process when you call .Close() so something seems to be a little odd here.