[RESOLVED] how to stop Excel creating extra books

[DebbieInFlorida]

it seems that every Excel workbook I create in Vb, Excel automatically creates a ghost book with the name Bookn.xls where the n is incremental. After a lot of processing, I could have 20+ blank books open and running in taskmanager!!!
Any ideas on how to prevent this??
thanks

[Siddharth Rout]

Debbie, how are you interacting with Excel... Can you show us some code?

[DebbieInFlorida]

sure.. first, I call this sub to create the file, then I open and add sheets as I need

Public Sub Create_Excel_File(ByVal sPath As String, ByVal sName As String)

Dim pExcel_App As Excel.Application
Dim pExcel_Wkbook As Excel.Workbook
Dim wkbSheet As Excel.Worksheet

Try

pExcel_App = New Excel.Application
pExcel_Wkbook = pExcel_App.Workbooks.Add
'== add worksheet to file
wkbSheet = pExcel_Wkbook.Worksheets.Add

Dim sOutExcelPath As String
sOutExcelPath = sPath & "\" & sName
pExcel_Wkbook.SaveAs(sOutExcelPath)

pExcel_App.DisplayAlerts = False
pExcel_App.ActiveWorkbook.Close(SaveChanges:=True, Filename:=sOutExcelPath)
pExcel_App.Quit()


Catch ex As Exception
MsgBox("ERROR: " & m_Current_Address & " - Create Excel log file for - " & mDistrictName & " - " & ex.Message)
Finally
pExcel_App = Nothing
pExcel_Wkbook = Nothing
wkbSheet = Nothing
End Try

End Sub


'==== this is adding sheets and data

pExcel_App = New Excel.Application
pExcel_Wkbook = pExcel_App.Workbooks.Open(mCurrent_Folder & "\" & mDistrict & ".xls")

'== add Summary worksheet to file
wkbSheet = pExcel_Wkbook.Worksheets.Add
wkbSheet.Name = sWKsheetName & " Summary"

wkbSheet2 = pExcel_Wkbook.Worksheets.Add
wkbSheet2.Name = mDistrict_DisplayName

'== do headings for summary
With wkbSheet

.Cells(9, 1) = "blah blah"...

'== close excel
Dim sOutExcelPath As String
sOutExcelPath = mCurrent_Folder & "\" & mDistrict
pExcel_App.DisplayAlerts = False
pExcel_Wkbook.SaveAs(sOutExcelPath)

pExcel_App.ActiveWorkbook.Close(SaveChanges:=True, Filename:=sOutExcelPath)
pExcel_App.Quit()


Cheers....

[Siddharth Rout]

Try it this way... I just typed this in Notepad so it is untested...

Imports Excel = Microsoft.Office.Interop.Excel
Public Sub Create_Excel_File(ByVal sPath As String, ByVal sName As String)

    Dim pExcel_App As Excel.Application, pExcel_Wkbook As Excel.Workbook
    Dim wkbSheet As Excel.Worksheet, sOutExcelPath As String
    
    sOutExcelPath = sPath & "\" & sName
    pExcel_App = New Excel.ApplicationClass
    pExcel_Wkbook = pExcel_App.Workbooks.Add
    wkbSheet = pExcel_Wkbook.Worksheets.Add
    
    pExcel_App.DisplayAlerts = False
    pExcel_App.ActiveWorkbook.Close(SaveChanges:=True, Filename:=sOutExcelPath)
    pExcel_Wkbook.SaveAs (sOutExcelPath)
    
    pExcel_Wkbook.Close()
    pExcel_App.Quit()

    releaseObject (pExcel_App)
    releaseObject (pExcel_Wkbook)
    releaseObject (wkbSheet)
End Sub

Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

Use the same way to release object in the second part of the code...

Hope this helps...

[DebbieInFlorida]

Koolsid,

THat did it, I shouldv'e thot of that myself. thanks so much

[Siddharth Rout]

Glad to be of help

[DebbieInFlorida]

Bad news, it cleared the instance great for 1, but when I do the subsequent excel books it mysteriously stops, no errors at all.
It took a while to find out exactly where it goes off the grid, but this is the line..


System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
obj = Nothing

I even reversed the order and eliminated them one by one, but it seems that doing the Release again does not work!!!! seems kinda stupid to me. THe object is totally new each time it's created.
So, for now I am living with multiple instances in task manager until the pc is restarted...

[BULK]

Hi
If you still got problem, add this when you are done with your Excel stuff:

 Dim pProcess() As Process = System.Diagnostics.Process.GetProcessesByName("Excel")
        For Each p As Process In pProcess
            p.Kill()
        Next

This will close all Excel books.