[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel not closing!

Posted on 2006-04-27
2
Medium Priority
?
193 Views
Last Modified: 2009-04-22
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



0
Comment
Question by:craigdev
2 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 2000 total points
ID: 16551569
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
 
LVL 1

Author Comment

by:craigdev
ID: 16558649
Thanks again
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

873 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