How to clear excel interop com components

Question

Hi I have two parts in my code one is to create an excel and then email it. I get the error as : IOException: The process cannot access the file 'filename' because it is being used by another process

How can i get away from this: here is the code:

    public void createexcel()
    {

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        oXL = new Excel.Application();


        oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
       // oSheet = (Excel._Worksheet)oWB.ActiveSheet;
       //oSheet = (Excel._Worksheet)oWB.Worksheets.get_Item(2);
      //oSheet = (Excel._Worksheet)oWB.Worksheets.get_Item(2);
        oSheet = (Excel._Worksheet)oWB.Worksheets.get_Item(1);

        //  oWB.Colors(1);

        oSheet.Cells[1, 1] = "Expected_Result";
        oSheet.Cells[1, 2] = "Actual_Result";
        oSheet.Cells[1, 3] = "Test_Status";


        //Format A1:D1 as bold, vertical alignment = center.
        oSheet.get_Range("A1", "D1").Font.Bold = true;
        oSheet.get_Range("A1", "D1").Font.Color = 0;
        oSheet.get_Range("A1", "D1").VerticalAlignment =
        Excel.XlVAlign.xlVAlignCenter;
        oSheet.Name = "Hotel_Total_Oppy_Verification";
       loc = "c:\\" + "TestResults_" + DateTime.Now.ToString("ddMMMMyyHHMMss");
        oWB.SaveAs(loc + ".xlsx");

        oWB.Close();

    }





    public void emailtestresults (string testresult)
    {

        string to = "abc@abc.com";
        string from = "abc@bc.com";
        MailMessage message = new MailMessage(from, to);
        message.Subject = " Test Result for Run of ." + DateTime.Today.ToString("DD/MM/YY/HHMMss") + "is " + emailresult;
        message.Body = @"Verify the results from the attached excel sheet or look at the Screenshot.";
        string PathToAttachment = loc + ".xlsx";
        message.Attachments.Add(new Attachment(PathToAttachment));
        SmtpClient client = new SmtpClient("xxxxxx");
        // Credentials are necessary if the server requires the client  
        // to authenticate before it will send e-mail on the client's behalf.
        client.UseDefaultCredentials = true;

        try
        {
            client.Send(message);
            DateTime now = DateTime.Now;

        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception caught in CreateTestMessage2(): {0}", ex.ToString());
            Console.ReadLine();
        }
    }

Try looking at `Marshal.ReleaseComObject(oWB);` the same would work for `oXL` `oSheet=null` — MethodMan, Jun 04 '13 at 15:12

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

I always end my Excel-Interop-Component this way:

owB.Close();
owX.Quit();
Marshal.FinalReleaseComObject(oSheet);
Marshal.FinalReleaseComObject(owB);
Marshal.FinalReleaseComObject(owX);

But the problem of your IOException is that you don't have access to the directory C:\ without admin permission.

Try saving it in your users path instead (or start the program with admin permission):

string loc = @"C:\Users\MYUSER\Desktop";

or by using:

string loc = Environment.GetFolderPath(Environment.SpecialFolder.Personal);

enter image description here

edited May 23 '17 at 12:30

Community

1
1

answered Jun 04 '13 at 15:18

jAC

5,195
6
40
55

Ok, it turned out to be a small thing. I was actually using an oledb method call and never closed that connection after adding the connection close step. it worked. Thanks everyone for the input I am able to avoid some garbage in the memory which anyways would have got created. – Bhaskar Mishra Jun 05 '13 at 15:26

How to clear excel interop com components

1 Answers1