?
Solved

Excel not closing

Posted on 2005-03-04
13
Medium Priority
?
430 Views
Last Modified: 2008-02-01
Hello,

I have an application that needs to access data in an Excel spreadsheet.  I've added a reference to Excel and am able to open Excel with the correct spreadsheet, get the data I need and seemingly, close Excel.  The problem is that if I run the program five times when I go to Task Manager I see five instances of Excel running.  If I set the Visible property to true, I see Excel open and close.  I'm using the following code to close Excel:

excelApp.Quit();
excelApp = null;

Does anyone know why I'm getting all these instances of Excel staying open?

Thanks in advance,
Rob
0
Comment
Question by:rkellow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 6

Expert Comment

by:melodiesoflife
ID: 13464178
Try this code:

private void NAR(object o)
{
     try
     {
          System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
     }
     catch {}
     finally
     {
          o = null;
     }
}

/// When you quit Excel app, please call:
excelApp.Quit();
NAR(excelApp);

/// Remember that, if you already use: Workbooks , Sheets ... You also must use NAR to release all of them.
0
 

Author Comment

by:rkellow
ID: 13464608
I tried the code you submitted and the instances of Excel are still there after the program runs just as before.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13464658
without opening the excel directly you can read the excel data by using  
System.Data.OleDbConnection object.  

Read entire data into dataset.... then close the connection object..... this is the best approach......


http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_20972338.html


itsvtk
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13464730
Here is the sample code for readin excel file without opening excel file.......


using System;
using System.Data;
using System.Data.OleDb;

class Demo
{
      public static void Main()
      {
            OleDbConnection con = new OleDbConnection( "Provider=Microsoft.Jet.OleDb.4.0;Data Source=F:\\Book1.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"" );
            con.Open();
            OleDbDataAdapter da = new OleDbDataAdapter( "select * from [Sheet1$]", con );
            DataSet ds = new DataSet();
            da.Fill( ds, "Sheet1" );
            con.Close();
            
            foreach( DataColumn dc in ds.Tables[0].Columns )
                        Console.Write( dc.ColumnName + "\t" );
            
            Console.Write( "\n" );
            
            foreach( DataRow dr in ds.Tables[0].Rows )
            {
                  foreach( DataColumn dc in ds.Tables[0].Columns )
                        Console.Write( dr[dc.ColumnName].ToString() + "\t" );
                  Console.Write( "\n" );
            }
                        
            ds = null;
      }
}


after reading i checked my excel file is not exists in Task Manager...

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13464744
Don't forget to change the excel file path and sheet name...

1)  OleDbConnection con = new OleDbConnection( "Provider=Microsoft.Jet.OleDb.4.0;Data Source=<Excel File Path>;Extended Properties=\"Excel 8.0;HDR=YES;\"" );


2)  OleDbDataAdapter da = new OleDbDataAdapter( "select * from [<Excel Sheet Name>$]", con );


itsvtk
0
 

Author Comment

by:rkellow
ID: 13466213
Hello itsvtk,

Thanks for the code but I've used Excel with ADO.NET and you're right, it works fine.  In this case though I MUST run Excel.  It's part of an automation process and along with needing some of the values that are in the spreadsheet, the process must save the spreadsheet out as a tab-delimited file, make changes to the tab-delimited file (that will be used by another process) and then re-open the tab-delimited file in Excel and save it as an Excel (.xls) file again.  I know it sounds a little strange but it's part of a much larger process that needs all this.  So, using Excel with ADO isn't going to do the job.

Thanks and Regards,
Rob
0
 
LVL 6

Expert Comment

by:melodiesoflife
ID: 13473132
Hi rkellow

You should close all you workbook before close your Excel Application.
here is a sample code.

     foreach (Excel.Workbook book in excelApp.Workbooks)
     {
          //You should set the Saved property of a Workbook is set to True, Excel will not prompt you to save it.
          book.Saved = true;
          //Close the book
          book.Close(false, Type.Missing, Type.Missing);
     }
     //quit application
     excelApp.Quit();
     NAR(excelApp);
0
 

Author Comment

by:rkellow
ID: 13473947
Hi melodiesoflife,

Thanks for the sample but I'm already doing that and still the instance(s) (one for each time I run Excel) stays in memory.  I've worked on it more over the weekend and found a few things.  First, I checked on some other forums and it appears that others have the same problem but it seems isolated to Excel 2003 and I haven't found any threads where there is a resolution.  Second, I found that the instance of Excel goes away (everything works) if I don't open a workbook.  This is obviously kind of useless but it does prove that everything else is working correctly and the problem seems to lie in the workbook more than Excel itself.  Third, I then thought that it might be something unique to the workbook(s) I was opening for testing my application but again, no luck as I tested it with both a new empty workbook as well as a new workbook with some data in it.  Fourth, I wrote some code that calls the GetObject function with "Excel.Application" as a parameter that would just loop trying to get the Excel instance that was still left in memory and repeatedly try to close (destroy) it.  I based the loop on a counter and to continue as long as the counter was less than 1000 and yet the GetObject function still returned a valid Excel object (! IsNothing(excelApp)) this ran until the counter hit 1000.  So the app is finding the instance it is just unable to close it.  Lastly, (and possibly my temporary workaround) is I found that if I make Excel Visible and leave it open after my app is finished (don't call CloseExcel()) and then close it manually by clicking the Close button in Excel, the instance of Excel goes away.  For now I may just leave it open and that way at least I know there won't be a hundred instances of Excel sitting in memory.

Thanks for the feedback and let me know if you have any other thoughts.

--Rob
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13474098
before closing the excel   make  "DisplayAlerts" to false

======================
Book.DisplayAlerts = False
======================

itsvtk
0
 

Author Comment

by:rkellow
ID: 13474158
Hi itsvtk,

Thanks but that didn't do it either.  I just added "excelApp.DisplayAlerts = false;" as you suggested but the instance of Excel remained in memory.  It's making me crazy!

Thanks again,
Rob
0
 

Author Comment

by:rkellow
ID: 13501841
Hello Everyone,

I was getting help on another forum with this problem as well and a person (Thank you Fredrik!) sent me a link to an article about this problem.  Here is the link http://www.mswordtalk.com/ftopic23462.html and the steps in this thread fixes the problem.  It turns out to be a "side-effect" of the Google Desktop search engine (I never would have guessed).  I just verified that everything works as soon as the Google COM Add-in is turned off in Excel.

Thanks for everyone's help,
Rob
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 13710581
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question