none
The process cannot access the file it is being used by another process. to close excel file before sending attachment RRS feed

  • Question

  • While running the following code the error message is coming The process cannot access the file 'E:\NotNeed\636153238546697234.xlsx' because it is being used by another process.

    string filename =  DataExport();
    SendEmail(filename);

    DataExport

    {

     ExcelFilePath = @"E:\NotNeed\" + string.Format(@"{0}.xlsx", DateTime.Now.Ticks);
                    dt.ExportToExcel(ExcelFilePath);

    }

    SendEmail()

    {

      System.Net.Mail.Attachment _attachment;

       _attachment = new System.Net.Mail.Attachment(filename);
                msg.Attachments.Add(_attachment);   

    }

      public static class DataTableToExcel
        {
             /// <summary>
            /// Export DataTable to Excel file
            /// </summary>
            /// <param name="DataTable">Source DataTable</param>
            /// <param name="ExcelFilePath">Path to result file name</param>
            public static void ExportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
            {
                try
                {
                    int ColumnsCount;

                    if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
                        throw new Exception("ExportToExcel: Null or empty input table!\n");

                    // load excel, and create a new workbook
                    Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
                    Excel.Workbooks.Add();

                    // single worksheet
                    Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;

                    object[] Header = new object[ColumnsCount];

                    // column headings               
                    for (int i = 0; i < ColumnsCount; i++)
                        Header[i] = DataTable.Columns[i].ColumnName;

                    Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
                    HeaderRange.Value = Header;
                    HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                    HeaderRange.Font.Bold = true;

                    // DataCells
                    int RowsCount = DataTable.Rows.Count;
                    object[,] Cells = new object[RowsCount, ColumnsCount];

                    for (int j = 0; j < RowsCount; j++)
                        for (int i = 0; i < ColumnsCount; i++)
                            Cells[j, i] = DataTable.Rows[j][i];

                    Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;

                    // check fielpath
                    if (ExcelFilePath != null && ExcelFilePath != "")
                    {
                        try
                        {
                            Worksheet.SaveAs(ExcelFilePath);

                            Excel.Quit();
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                + ex.Message);
                        }
                    }
                    else    // no filepath is given
                    {
                        Excel.Visible = true;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: \n" + ex.Message);
                }
            }
        }
        }


    polachan

    Monday, November 21, 2016 11:20 AM

All replies

  • Hello,

    You could have Task Manager open to see how many instances of Excel are under the process tab. Then determine actions from here. If one instance this would indicate the garbage collection has not occurred and there is still one or more objects still in memory even thou you quit Excel automation. 

    In regards to disposal of all objects, the following (a random example) is a pattern to follow done in a form but could easily be in a class. Your code is not cleaning up all objects which is why I'm showing this.

    using System;
    using System.Runtime.InteropServices;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace Example_C2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                dataGridView1.Rows.Add(new object[] { "Karen", "Payne" });
                dataGridView1.Rows.Add(new object[] { "Mary", "White" });
                dataGridView1.Rows.Add(new object[] { "Joe", "Adams" });
            }
    
            private void Demo()
            {
                string SheetName = "Sheet1";
                string FileName = System.IO.Path.Combine
                    (AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx");
    
                bool Proceed = false;
    
                Excel.Application xlApp = null;
                Excel.Workbooks xlWorkBooks = null;
                Excel.Workbook xlWorkBook = null;
                Excel.Worksheet xlWorkSheet = null;
                Excel.Sheets xlWorkSheets = null;
                Excel.Range xlCells = null;
    
                xlApp = new Excel.Application();
                xlApp.DisplayAlerts = false;
                xlWorkBooks = xlApp.Workbooks;
                xlWorkBook = xlWorkBooks.Open(FileName);
    
                xlApp.Visible = false;
    
                xlWorkSheets = xlWorkBook.Sheets;
    
                for (int x = 1; x <= xlWorkSheets.Count; x++)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
    
                    if (xlWorkSheet.Name == SheetName)
                    {
                        Proceed = true;
                        break;
                    }
    
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet);
                    xlWorkSheet = null;
                }
    
                for (int row = 0; row < dataGridView1.Rows.Count; row++)
                {
                    xlCells = xlWorkSheet.Range["A" + (row + 1)];
                    xlCells.Value = dataGridView1.Rows[row].Cells["Column1"].Value;
                    Marshal.ReleaseComObject(xlCells);
                    xlCells = null;
    
                    xlCells = xlWorkSheet.Range["B" + (row + 1)];
                    xlCells.Value = dataGridView1.Rows[row].Cells["Column2"].Value;
                    Marshal.ReleaseComObject(xlCells);
                    xlCells = null;
                }
    
                xlWorkSheet.SaveAs(FileName);
                xlWorkBook.Close();
                xlApp.UserControl = true;
                xlApp.Quit();
    
                ReleaseComObject(xlCells);
                ReleaseComObject(xlWorkSheets);
                ReleaseComObject(xlWorkSheet);
                ReleaseComObject(xlWorkBook);
                ReleaseComObject(xlWorkBooks);
                ReleaseComObject(xlApp);
    
                MessageBox.Show("Done");
            }
            private void ReleaseComObject(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
            }
            private void button1_Click(object sender, EventArgs e)
            {
                Demo();
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 21, 2016 11:35 AM
    Moderator
  • Yes and, if a file is used by another program you cannot use it if it is not opened read only. 

    That is not the case with Excel where is expected it update changes. 


    Success
    Cor

    Monday, November 21, 2016 11:36 AM
  • I think you should read these articles on proper exception handling, encapsulation and similar stuff:

    Vexing exceptions - Fabulous Adventures In Coding - Site Home - MSDN Blogs

    Exception Handling Best Practices in .NET - CodeProject

    You should be getting much more usefull error information upon following those guidelines.


    Remember to mark helpfull answers as helpfull and close threads by marking answers.

    Monday, November 21, 2016 4:06 PM