Excel Ghost Instance Issue while making SQL Query on a worksheet

**vikas.bhandari** · 11-13-2009, 09:40 AM

Hi all,

I am trying to run an SQL query to the worksheet of the workbook opened. This is worth noting, that this code is being called multiple times. Suppose if I have around 100 items in a listview, any item I will check or uncheck, this query will be fired. I tried selecting the five items, and the following code ran five times, and the excel ghost instance appeared.

Please Login or Register  to view this content.

There is no problem in the logic. Every thing is working fine and I am not getting any error. The only problem is, that whenever I try to close my Excel, it takes around 2 minutes to be removed from the Processes list in the task manager. It is really annoying me now. Can someone pleaseeee help?

I DON'T want to write any code in Workbook.close method.

Any idea?

Thanks,
Vikas

**vikas.bhandari** · 11-13-2009, 09:52 AM

One more thing, the more number of times I run this, the longer it takes to close. I am confused

**Richard Schollar** · 11-13-2009, 10:27 AM

I'm pretty sure that creates a memory leak that probably explains the increasingly poor performance. Why don't you save down a copy of your workbook and target this (unopened) version for the SQL queries?

**vikas.bhandari** · 11-13-2009, 11:12 AM

Hey Richard...thanks...I think it is the bug with the Microsoft Excel. See this link :

http://support.microsoft.com/default...;en-us;Q319998

I will need to change the logic...

Thanks,
Vikas

**vikas.bhandari** · 11-16-2009, 04:53 AM

Confirmed, it was a memory leak. When you use Jet.OLEDB on an open workbook, it creates a memory leak which at last doesn't allow to Excel to be closed because it tries to release the resources first. And the more number of queries you have made, the longer it takes to kill the variables and free the memory.

The solution is, to copy the data in a new workbook and then fire a query on that workbook. It will work like a charm

Thanks for the support

Vikas Bhandari