Repost: After Automating Excel it stays in memory 
Author Message
 Repost: After Automating Excel it stays in memory

I wrote this routine to save a datatable to an Excel spreadsheet.
It works pretty well. (It is a bit slow though.) I have complete control
over the data and overall I am happy with it.

The one problem I haven't solved yet is that Excel stays in memory until I
close the form that called the code. So if I run 3 exports in a row I have 3
instances of Excel started. They all close when the form is closed. (The app
also ends at this point so I am not 100% sure if it is the form close or the
app end that releases the Excel instances.)

The code is stored in a separate project and I call it as a method of one of
my objects.

Can someone please explain if this is "normal"? If not, what do I need to do
to release the memory correctly?

Code:
Public Sub Export2Excel(ByVal dt As DataTable, ByVal Path As String, ByRef
pb As ProgressBar)

'add reference to Microsoft Excel

Dim objXL As Excel.Application

Dim objWB As Excel.Workbook

Dim objWS As Excel.Worksheet

Dim mRow As DataRow

Dim colIndex As Integer

Dim rowIndex As Integer

Dim col As DataColumn

pb.Value = 0

pb.Minimum = 0

pb.Step = 1

Try

    pb.Maximum = dt.Rows.Count

    pb.Visible = True

    If File.Exists(Path) Then

        File.Delete(Path)

    End If

    objXL = New Excel.Application

    objWB = objXL.Workbooks.Add

    objWS = CType(objWB.Worksheets(1), Excel.Worksheet)

    For Each col In dt.Columns

        colIndex += 1

        objWS.Cells(1, colIndex) = col.ColumnName

    Next col

    objWS.Range("A1:Y1").Font.Bold = True

    objWS.Columns.ColumnWidth = 10.5

    rowIndex = 1

    For Each mRow In dt.Rows

        pb.PerformStep()

        rowIndex += 1

        colIndex = 0

        For Each col In dt.Columns

            colIndex += 1

            objWS.Cells(rowIndex, colIndex) =
mRow(col.ColumnName).ToString()

     Next col

    Next mRow

   objWS.Range("A1:A" & CStr(dt.Rows.Count)).RowHeight = 12.75

    objWB.SaveAs(Path)

    objWB.Close()

    objXL.Quit()

Catch exc As Exception

    Throw

Finally

    objWS = Nothing

    objWB = Nothing

    objXL = Nothing

    pb.Visible = False

End Try

End Sub

--
Joe Fallon



Sat, 26 Nov 2005 22:24:51 GMT  
 Repost: After Automating Excel it stays in memory
Hello Joe,


Quote:
> instances of Excel started. They all close when the form is closed. (The
app
> also ends at this point so I am not 100% sure if it is the form close or
the
> app end that releases the Excel instances.)

Try something like this:

\\\
objExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
objExcel = Nothing

' Perhaps you need these lines too:
GC.Collect()
GC.WaitForPendingFinalizers()
///

Regards,
Herfried K. Wagner
--
http://www.mvps.org/dotnet



Sat, 26 Nov 2005 22:26:11 GMT  
 Repost: After Automating Excel it stays in memory
Herfried,
Thanks again.

I will give it a try right now.

In the meantime I was able to minimize the problem using this code:

Try

    'get a running instance of Excel - this minimizes the number of
instances of Excel in memory to just 1.

    objXL = CType(GetObject(, "Excel.Application"), Excel.Application)

Catch ex As Exception

    'create a new instance of Excel if there isn't one running.

    objXL = New Excel.Application

End Try

--
Joe Fallon



Quote:
> Hello Joe,


> > instances of Excel started. They all close when the form is closed. (The
> app
> > also ends at this point so I am not 100% sure if it is the form close or
> the
> > app end that releases the Excel instances.)

> Try something like this:

> \\\
> objExcel.Quit()
> System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
> objExcel = Nothing

> ' Perhaps you need these lines too:
> GC.Collect()
> GC.WaitForPendingFinalizers()
> ///

> Regards,
> Herfried K. Wagner
> --
> http://www.mvps.org/dotnet



Sat, 26 Nov 2005 22:56:16 GMT  
 Repost: After Automating Excel it stays in memory
I had high hopes that your code samples would work.
They looked like they should do the trick.

Alas, the instance of Excel refuses to die programmatically.

I think I can live with my workaround which limits the number of instances
in memory to 1.
When it was unbounded, I was uncomfortable leaving it that way since the
user's PC could run out of memory if they ran multiple exports in a row.

Thanks anyway!
--
Joe Fallon



Quote:
> Hello Joe,


> > instances of Excel started. They all close when the form is closed. (The
> app
> > also ends at this point so I am not 100% sure if it is the form close or
> the
> > app end that releases the Excel instances.)

> Try something like this:

> \\\
> objExcel.Quit()
> System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)
> objExcel = Nothing

> ' Perhaps you need these lines too:
> GC.Collect()
> GC.WaitForPendingFinalizers()
> ///

> Regards,
> Herfried K. Wagner
> --
> http://www.mvps.org/dotnet



Sat, 26 Nov 2005 23:06:38 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Help Excel Object stays in memory

2. visio stays hidden in memory after improperly closing

3. visio stays hidden in memory

4. Word 2000 stays in memory no matter what

5. Why does my VB App stay in memory

6. Does object stay in memory?

7. App..closes but stays in memory

8. Program stays in memory

9. ActiveX DLL Stays in Memory

10. Process stays in memory

11. DLL stays in memory after error occurs in program

12. VB 4.0 app stays in memory when terminated

 

 
Powered by phpBB® Forum Software