I have an Windows Form application that gives the user an open dialog box to open a spreadsheet.
The application performs some tasks on that spreadsheet like sorting and deleting blank rows. Then when it is finished it performs a SaveAs using the current date as part of the new file name.
After that is finished what I want to do is delete the original spreadsheet.
I am using Microsoft.Office.Interop.Excel.
I found code here on StackOverflow (How do I properly clean up Excel interop objects?) that shows how to shutdown Excel and the 3rd answer in that post by nightcoder even showed a way (which I am using) that will removed Excel from the Processes tab of the Task Manager.
I thought once that Excel was dumped from Task Manager it would remove its icy tenticle-like death grip with the file I am trying to close but how wrong I am.
When the code hits the File.Delete(MyFile) command it still says I cannot delete this file because there is an icy tenticle-like death grip on it or something like that.
Does anyone know where I can find a baseball bat big enough to make the app let go of the file. I would really like to delete it.
==============================================================
7/22/2013 Update
Here is part of my code that I have so far.
What this code does is allow the user to select an excel spreadsheet, open it, rename it and then close it. I stripped out some of the code that manipulated the spreadsheet that I didn't think was necessary.
What I would like for it to do is after it has saved the renamed file is to close the original, open the renamed and exit the application while leaving the renamed spreadsheet opened for any further editing.
Currently it does not do that. It only creates the spreadsheet with the new name and then closes both the app and the spreadsheet. Then I have to open the new spreadsheet manually.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;
namespace Weekly_Stats_Organizer
{
public partial class Form1 : Form
{
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
string _ExcelFileName;
private string ExcelFileName
{
get { return _ExcelFileName; }
set { _ExcelFileName = value; }
}
private string DefaultPath = "C:\\My Documents\\Weekly Stats";
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
Stream FileName = null;
OpenFileDialog OpenExcelSpreadsheet = new OpenFileDialog();
OpenExcelSpreadsheet.InitialDirectory = DefaultPath;
OpenExcelSpreadsheet.Filter = "Excel 2003 (*.xls)|*.xls|Excel 2007 (*.xlsx)|*.xlsx";
OpenExcelSpreadsheet.FilterIndex = 1;
OpenExcelSpreadsheet.RestoreDirectory = true;
if(OpenExcelSpreadsheet.ShowDialog() == DialogResult.OK)
{
if((FileName = OpenExcelSpreadsheet.OpenFile()) != null)
{
ExcelFileName = ((System.Windows.Forms.FileDialog)(OpenExcelSpreadsheet)).FileName;
GenerateWorkbook();
}
}
}
private void GetExcel()
{
Excel.Application ExcelApp = null;
Excel.Workbook ExcelWorkBook = null;
Excel.Sheets ExcelSheets = null;
Excel.Worksheet MySheet = null;
try
{
DateTime CurrentDate = DateTime.Today;
string FileName = DefaultPath + "\\" + CurrentDate.Year.ToString() + "-" + CurrentDate.Month.ToString("D2") + "-" + CurrentDate.Day.ToString("D2") + " Weekly Stats.xls";
ExcelApp = new Excel.Application();
ExcelApp.Visible = false;
ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelFileName, 0, true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
ExcelSheets = ExcelWorkBook.Worksheets;
MySheet = (Excel.Worksheet)ExcelSheets.get_Item("Sheet 1");
ExcelWorkBook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false,
Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlUserResolution, true, "", "", "");
//GC.Collect();
//GC.WaitForPendingFinalizers();
Marshal.ReleaseComObject(MySheet);
Marshal.ReleaseComObject(ExcelSheets);
MySheet = null;
ExcelSheets = null;
ExcelWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
}
finally
{
Marshal.ReleaseComObject(ExcelWorkBook);
int hWnd = ExcelApp.Application.Hwnd;
TryKillProcessByMainWindowHwnd(hWnd);
Marshal.ReleaseComObject(ExcelApp);
ExcelWorkBook = null;
ExcelApp = null;
//if (File.Exists(ExcelFileName))
// File.Delete(ExcelFileName);
System.Windows.Forms.Application.Exit();
}
}
//=========================================================================================================================================
//=========================================================================================================================================
//=========================================================================================================================================
// This code was found at https://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects
// is the answer provided by nightcoder. This solution seems to be the only one that actually clears the Excel instance out of the
// Processes in Windows Task Manager. The idea is to completely shut down Excel so that I can delete the original spreadsheet. So far not
// working out so well.
private void GenerateWorkbook()
{
try
{
GetExcel();
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
//=============================================================================================================================================
/// <summary> Tries to find and kill process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <returns>True if process was found and killed. False if process was not found by hWnd or if it could not be killed.</returns>
public static bool TryKillProcessByMainWindowHwnd(int hWnd)
{
uint processID;
GetWindowThreadProcessId((IntPtr)hWnd, out processID);
if (processID == 0) return false;
try
{
Process.GetProcessById((int)processID).Kill();
}
catch (ArgumentException)
{
return false;
}
catch (Win32Exception)
{
return false;
}
catch (NotSupportedException)
{
return false;
}
catch (InvalidOperationException)
{
return false;
}
return true;
}
/// <summary> Finds and kills process by hWnd to the main window of the process.</summary>
/// <param name="hWnd">Handle to the main window of the process.</param>
/// <exception cref="ArgumentException">
/// Thrown when process is not found by the hWnd parameter (the process is not running).
/// The identifier of the process might be expired.
/// </exception>
/// <exception cref="Win32Exception">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="NotSupportedException">See Process.Kill() exceptions documentation.</exception>
/// <exception cref="InvalidOperationException">See Process.Kill() exceptions documentation.</exception>
public static void KillProcessByMainWindowHwnd(int hWnd)
{
uint processID;
GetWindowThreadProcessId((IntPtr)hWnd, out processID);
if (processID == 0)
throw new ArgumentException("Process has not been found by the given main window handle.", "hWnd");
Process.GetProcessById((int)processID).Kill();
}
//=========================================================================================================================================
//=========================================================================================================================================
//=========================================================================================================================================
}
}
