Can not Kill or Quit Excel process problem!

[wending168]

Hi, Excel Automation Guru:

I am using VC++ Excel Automation to Open a text file using OpenText() and do some modification. My application requires to insert a new WorkSheet. But seems like inserting a new WorkSheet causes a big problem that the Excel.exe never got exited or killed when I finished the Excel Automation. Here is my code (I modify it so that it is very simple):

CreateExcel(...)
{
_Application app;
COleException* pError = new COleException;
if(!app.CreateDispatch("Excel.Application", pError)) return;
Workbooks books = app.GetWorkbooks();
books.OpenText( sFileName, //input filename
Origin, //Origin: xlWindows
StartRow, //StartRow: 1
DataType, //DataType: xlDelimited
(long)1, //TextQualifier: xlDoubleQuote
covFalse, //ConsecutiveDelimiter
covTrue, //Tab
covFalse, //Semicolon
covFalse, //Comma
covFalse, //Space
covFalse, //Other
covFalse, //OtherChar
covOptional, //FieldInfo
covOptional, //TextVisualLayout
covOptional, //DecimalSeparator
covOptional); //ThousandsSeparator

_Workbook book = books.GetItem(COleVariant((short)1));
Worksheets sheets =book.GetSheets();

//The below line cause the problem
sheets.Add( covOptional,
covOptional,
COleVariant((short)(1)),
COleVariant((long)-4167)); //xlWorkSheet = -4267

_Worksheet sheet;

short nSize = sheets.GetCount();
for(short ii = 1; ii <= nSize; ii++)
{
sheet = sheets.GetItem(COleVariant(ii));
sheet.ReleaseDispatch();
}
sheets.ReleaseDispatch();

book.Close(covFalse, covOptional, covOptional);
book.ReleaseDispatch();

books.Close();
books.ReleaseDispatch();

app.Quit();
app.ReleaseDispatch();
app = NULL;
}


If I don't add a new Worksheet, The program works fine. But if I add or insert a new Worksheet, the Excel.exe is not able to get killed when I exit this method. What is wrong in my code? Thanks for your help.

[zaKKum]

Hi.

Maybe this method can be useful.

#include <tlhelp32.h>

void ExcelKillProc()
{
   HANDLE hProcessSnap = NULL;
   PROCESSENTRY32 pe32 = {NULL};
   char tbuff[] = {"EXCEL.EXE"};

   hProcessSnap = CreateToolhelp32Snapshot 
      (TH32CS_SNAPPROCESS, 0);

   pe32.dwSize = sizeof(PROCESSENTRY32);
   if (Process32First(hProcessSnap, &pe32))
   {
      do
      {
         if (strncmp(tbuff, strupr(pe32.szExeFile), 9) == 0)
         {
            HANDLE excelHandle = OpenProcess
               (PROCESS_ALL_ACCESS, FALSE, pe32.th32ProcessID);

            DWORD exCode;
            GetExitCodeProcess(excelHandle, &exCode);
            TerminateProcess(excelHandle, exCode);
            break; // To kill only one instance
         }
      } while (Process32Next(hProcessSnap, &pe32));
   }
}

[wending168]

Thanks for your reply. It seems like you are using a mandatory method to close the Excel.exe process. But I feel it may not be the perfect way. If I can't kill the process naturally by using the Excel automation method, it should be something error in my code. I am frustrated. There are no enough document about it inside MSDN. Thank you anyway.

[Tom Frohman]

If you put

app.SetUserControl(TRUE);
app.SetVisible(TRUE);

after the createdispatch does it still hang around in memory?


Tom

[wending168]

Hi, Tom,

it still hang in the memory if I put:
app.SetUserControl(TRUE);
app.SetVisible(TRUE);

I made my test more simple: just start a new excel file, and add a new sheet, and prompt(or display) the Excel.exe. Here is my code:

CreateExcel(...)
{
_Application app;
COleException* pError = new COleException;

if(!app.CreateDispatch("Excel.Application", pError)) return;

Workbooks books = app.GetWorkbooks();
_Workbook book = books.Add(covOptional); //start a new file

Worksheets sheets =book.GetSheets();

//The below line cause the problem: add a new worksheet
sheets.Add( covOptional, covOptional,
COleVariant((short)(1)),
COleVariant((long)-4167)); //xlWorkSheet = -4267

_Worksheet sheet;
short nSize = sheets.GetCount();
for(short ii = 1; ii <= nSize; ii++)
{
sheet = sheets.GetItem(COleVariant(ii));
sheet.ReleaseDispatch();
}
sheets.ReleaseDispatch();

pError->Delete();

//open the Excel progam
app.SetUserControl(TRUE);
app.SetVisible(TRUE);
}

After running this method, the Excel 2000 program pop up, and one worksheet is corrrectly added (total have 4 sheets). But when I close the Excel 2000 program, it pop up a "EXCEL.EXE - Application Error" dialog. It said: "The instruction at "Ox650161fe" referenced memory ..... The memory could not be "read". Click OK ... "

When I click OK, the Excel.exe program close, but it still hang inside memory. I can see that from "Windows Task Manager -> Processes". It seems like some objects are still not released in my code. But I don't know which one, even if the program is now so simple.

If I don't add a new worksheet, the program works perfect. So I think the problem is caused by the line:

sheets.Add( covOptional, covOptional,
COleVariant((short)(1)),
COleVariant((long)-4167)); //xlWorkSheet = -4267

But what's wrong with it?

By the way, when I debug the program line by line, there is no exception thrown.

Thank you again for your reply and help.

[Tom Frohman]

I took your code and added a few lines.(shown in blue)
With this code if I comment out app.SetDisplayAlerts(FALSE);
,I get errors and a disconnected process.

When I restore set display alerts the error and disconnected process don't appear.

Part of the problem is it is trying to warn you that the worksheet hasn't been saved when you quit. If it isn't visible you can't respond to this message. You could also set book.SetSaved(TRUE) to suppress this message.

_Application app;
COleException* pError = new COleException;
COleVariant  
			 covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR),covWidth((short)40),
			 covFalse((short)FALSE),
             covTrue((short)TRUE);
if(!app.CreateDispatch("Excel.Application", pError)) return;

app.SetDisplayAlerts(FALSE);
app.SetVisible(TRUE);
app.SetUserControl(TRUE);

Workbooks books = app.GetWorkbooks();
_Workbook book = books.Add(covOptional); //start a new file

Worksheets sheets =book.GetSheets();

//The below line cause the problem: add a new worksheet
sheets.Add( covOptional, covOptional, 
COleVariant((short)(1)), 
COleVariant((long)-4167)); //xlWorkSheet = -4267

_Worksheet sheet;
long nSize = sheets.GetCount();
for(long ii = 1; ii <= nSize; ii++)
{
sheet = sheets.GetItem(COleVariant(ii));
sheet.ReleaseDispatch();
sheet=NULL;
}
sheets.ReleaseDispatch();

app.Quit();
app.ReleaseDispatch();
app=NULL;

pError->Delete();

[wending168]

Hi, Tom,

app.SetDisplayAlerts(FALSE) did make the Error Dialog box gone. But after exit this function/Method, the Excel.exe is still hang in the memory. When I comment out:

sheets.Add( covOptional, covOptional,
COleVariant((short)(1)),
COleVariant((long)-4167)); //xlWorkSheet = -4267

It works fine without hanging Excel.exe in the memory. If I don't comment out this line, the Excel.exe hang. Don't know why but it looks like getting there.

Thanks again.

[Tom Frohman]

One other thing to try. If you have app.ReleaseDispatch();, somewhere before this and before app.quit() try app.m_bAutoRelease=FALSE;

Most of my problems occur when I kill Excel before the program.
Setting this to false gets rid of that problem.

[wending168]

Thank you Tom.

But I also tried using "app.m_bAutoRelease=FALSE;"

It still doesn't work. Does anybody have any clue on this thing?