Question about using Excel {code only works once}

rahavtom
10-06-2004, 02:26 PM
Hello !

I have a procedure which export data to MS Excel file and rub macro.
I call this procedure once and it works perfect, but when I try to call it again during the same program's run the application freezes.
I use the followin code:

Public Sub ExportToExcel()

Dim oExcel As Excel.ApplicationClass
Dim oBook As Excel.WorkbookClass
Dim oBooks As Excel.Workbooks
oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oBooks = oExcel.Workbooks
oBook = oBooks.Open("c:\temp\graphs.xls")

Dim excelWorksheet As Excel.Worksheet = CType(oBook.Worksheets(1), Excel.Worksheet)

excelWorksheet.Range("A1").Value = 80
oExcel.Run("PrintGraphs")
oBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
GC.Collect()

End Sub

Can someone explain me why this procedure runs only once, and what I should change?

Thanks!
Tom.

herilane
10-06-2004, 02:34 PM
You're setting all Excel objects to Nothing, except excelWorksheet. Try adding excelWorksheet = Nothing after you've finished using the sheet.

Mike Rosenblum
10-06-2004, 05:10 PM
Yes, in addition, since you are calling GC.Collect explicitly, you should not need the calls to Marshal.ReleaseComObject(). Try it without it, it will avoid unnecessary clutter in your code. In the end, your closeup routine might look like this:
excelWorksheet = Nothing
oBook.Close(False)
oBook = Nothing
oBooks = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
Let us know...! :)

rahavtom
10-07-2004, 06:28 AM
Yes, in addition, since you are calling GC.Collect explicitly, you should not need the calls to Marshal.ReleaseComObject(). Try it without it, it will avoid unnecessary clutter in your code. In the end, your closeup routine might look like this:
excelWorksheet = Nothing
oBook.Close(False)
oBook = Nothing
oBooks = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
Let us know...! :)

Dear Experts!
It works perfect now... thanks a lot!

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum