Excel not closing!

Hi. When I create a workbook then add some sheets I am finding that the excel process is not closing in task manager. In the code below, I create a series of workbooks, then add sheets. Finally later I will need to populate data. There will be approx 200 workbooks and up to 10 sheets per workbook so it is important that I close each one after each step.

The problem I am getting is that the workbooks are staying open. What have I done wrong?


    Private Sub AddSheet(ByVal folder As String, ByVal workbook As String, ByVal sheet As String)
        Dim xlObj As Object

        xlObj = CreateObject("Excel.Application")
        xlObj.visible = False

        Dim wbk As Object
        wbk = xlObj.Workbooks.Open(Filename:=folder & workbook, UpdateLinks:=False, ReadOnly:=False)
        mysheet = wbk.sheets.add()
        mysheet.name = sheet

        xlObj.Workbooks.Close()
        wbk.saveas(folder & workbook)
        wbk.Close()
        xlObj.Quit()
        xlObj = Nothing
        wbk = Nothing
    End Sub



    Private Function CreateWorkbook(ByVal folder As String, ByVal workbook As String) As Object
        Dim xlObj As Object

        xlObj = CreateObject("Excel.Application")
        xlObj.visible = False
        xlObj.workbooks.add()
        sheetCount = xlObj.workbooks(1).sheets.count
        xlObj.workbooks(1).saveas(folder & workbook)
        xlObj.Workbooks.Close()
        xlObj.Quit()
        xlObj = Nothing
    End Function



LVL 1
craigdevAsked:
Who is Participating?
 
bruintjeCommented:
Hello craigdev,

you could have a look at this comrelesae function
source: http://www.codeproject.com/vb/net/ReleaseExcelObject.asp
---------
Private Sub ReleaseComObject(ByRef Reference As Object)
    Try        
        Do Until _
         System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)<=0
        Loop
    Catch
    Finally
        Reference = Nothing
    End Try
End Sub
---------

and apply it to your code like
---------
    Private Sub AddSheet(ByVal folder As String, ByVal workbook As String, ByVal sheet As String)
        Dim xlObj As Object

        xlObj = CreateObject("Excel.Application")
        xlObj.visible = False

        Dim wbk As Object
        wbk = xlObj.Workbooks.Open(Filename:=folder & workbook, UpdateLinks:=False, ReadOnly:=False)

        Dim mysheet As Object
        mysheet = wbk.sheets.add()
        mysheet.name = sheet

        wbk.saveas(folder & workbook)
        wbk.Close()

        'cleanup
        mysheet = Nothing
        ReleaseComObject(mysheet)
        wbk = Nothing
        ReleaseComObject(wbk)
        xlObj.Quit()
        ReleaseComObject(xlObj)
    End Sub
---------

hope this helps a bit
bruintje
0
 
craigdevAuthor Commented:
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.