dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] closing excel problem

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    32

    Resolved [RESOLVED] closing excel problem

    I have a vb6 program that opens a csv file, extracts data from it and then adds that data to a spreadsheet and updates a chart with the added data.
    The essence of the program is:

    Code:
    Sub main()
    Dim XL As Excel.Application
    'start excel
        Set XL = CreateObject("Excel.Application")
    ' open csv file
        Workbooks.Open FileName:=xlFilename
        sheetName = ActiveSheet.Name
        Worksheets(sheetName).Activate
    ' extract data
    ' close csv file
        ActiveWorkbook.Close savechanges:=True
        XL.Quit
    'start excel again
        Set XL = CreateObject("Excel.Application")
    ' open xlsm
        Workbooks.Open FileName:=xlFilename
        sheetName = ActiveSheet.Name
    ' write data to xlms
        '.......
        'update a chart
                ActiveSheet.ChartObjects("Chart 1").Activate
                ActiveChart.SeriesCollection(1).Values = "=Summary!$D$2:$D$" & lastrow + 1
                ActiveChart.SeriesCollection(2).Values = "=Summary!$B$2:$B$" & lastrow + 1
                ActiveChart.SeriesCollection(2).XValues = "=Summary!$A$2:$A$" & lastrow + 1
    ' close xlsm and save changes
        ActiveWorkbook.Close savechanges:=True
    End Sub
    The problem I have is that, although the program does exactly what I want, when the program closes, sometimes an instance of excel is left running. I can only see this instance in Task Manager and I have to end this process before I can run the program again.

    I admit that the way I open and close the excel application could probably be done much more effectively but I have tried all sorts of variations and cannot get the right way. This version works most of the time but as I said only sometimes it does not close excel on exit. I have noticed that if I delete the 4 lines re the chart, the program ends properly (ie no instance of excel left running) more often than when I update the chart.

    Can anyone offer a reason why the program acts differently on exit sometimes?

    Also, if there are better ways to open and close excel than I am doing, I would appreciate knowing them.
    Thanks

  2. #2
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,265

    Re: closing excel problem

    That is a common issue and has been asked and resolved many times at this forum. In the upper right hand corner is a search field. Enter "close excel" and you will get a number of hits with the solution.

    This is also very helpful:

    http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)
    Please remember next time...elections matter!

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,303

    Re: closing excel problem

    'start excel again
    Set XL = CreateObject("Excel.Application")
    there is no need to start a second instance of excel, just use the original instance again, instead of quitting it, also you are not quitting the second instance

    avoid working with active sheets, charts or books, use fully qualified ranges
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,560

    Re: closing excel problem

    Hi hunter55 ,

    I do pretty much exactly what you do all the time. However, there's one extra step that you're skipping that I always do. It's actually close the Excel application. Here's the code to do that:

    Code:
    XL.Quit
    Or, said differently, here's a somewhat complete set of open/close code for doing Excel automation:

    Code:
    
    Sub Main()
        Dim xls As Object
        Dim wbk As Object
        Dim wsh As Object
    
    
        Dim sFileSpec As String
    
    
        sFileSpec = "c:\MyWorkbook.xls"
    
    
        Set xls = CreateObject("Excel.Application")
        Set wbk = xls.workbooks.Open(sFileSpec)
        Set wsh = wbk.Worksheets("Sheet1")
    
    
        ' Do my work ....
    
    
    
        ExcelSaveAs wbk, sFileSpec
        wbk.Close False
        xls.Quit
        Set wsh = Nothing
        Set wbk = Nothing
        Set xls = Nothing
    End Sub
    
    
    Sub ExcelSaveAs(wbk As Object, sFileSpec As String, Optional bForceOldFormat As Boolean = True)
        Const xlExcel8 = 56&
        Const xlOpenXmlWorkbookMacroEnabled = 52&
        '
        If bForceOldFormat Then
            If Int(wbk.Application.Version) > 11 Then ' It's Office 2007 or greater.
                wbk.CheckCompatibility = False
                wbk.SaveAs sFileSpec, xlExcel8
            Else
                wbk.SaveAs sFileSpec
            End If
        Else
            If UCase$(Right$(sFileSpec, 4)) = "XLSM" Then
                wbk.SaveAs sFileSpec, xlOpenXmlWorkbookMacroEnabled
            Else
                wbk.SaveAs sFileSpec
            End If
        End If
    End Sub
    
    
    You don't absolutely need the Set ? = Nothing statements, but if the code isn't immediately going out of scope for those object variables, it's a good idea. Also, I just declared them all as "Object" because I do Automation with late-bound variables. If you have an Excel reference, you can certainly do it with early-binding.

    Again, you missed the "xls.Quit" step is why Excel stayed loaded.

    Good Luck,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    32

    Re: closing excel problem

    Thanks for your replies - I am giving them a try. I still haven't solved my problem but I will work thru the tutorial and hopefully I can work out a solution.

    How do I mark this as resolved?

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    32

    Re: [RESOLVED] closing excel problem

    Re resolved, I have just found where to do this!

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: closing excel problem

    Quote Originally Posted by hunter55 View Post
    Thanks for your replies - I am giving them a try. I still haven't solved my problem but I will work thru the tutorial and hopefully I can work out a solution.

    How do I mark this as resolved?
    If your query is not resolved then you do not need to mark it as resolved
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  8. #8
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,989

    Re: [RESOLVED] closing excel problem

    I have not tested this but try this. This will not leave an instance.

    Code:
    Sub main()
        Dim oXLApp As Object, oXLWB As Object, oXLSht As Object
        Dim QuitIt As Boolean
        
        '~~> Establish an EXCEL application object
        '~~> Check if an excel application is open
        '~~> If not, then create an instance
        On Error Resume Next
        Set oXLApp = GetObject(, "Excel.Application")
        
        If Err.Number <> 0 Then
            Set oXLApp = CreateObject("Excel.Application")
            QuitIt = True
        End If
        Err.Clear
        On Error GoTo 0
        
        '~~> Open csv file
        Set oXLWB = Workbooks.Open(Filename:=xlFilename)
        Set oXLSht = oXLWB.Sheets(1)
        
        With oXLSht
            '
            '~~> Extract Data
            '
        End With
        
        '~~> Close csv file
        oXLWB.Close savechanges:=False
        
        '~~> Open xlsm
        Set oXLWB = Workbooks.Open(Filename:=xlFilename)
        Set oXLSht = oXLWB.Sheets(1)
    
        '~~> write data to xlms
        '.......
    
        '~~>update a chart
        With oXLSht
            .ChartObjects("Chart 1").Activate
            .SeriesCollection(1).Values = "=Summary!$D$2:$D$" & lastrow + 1
            .SeriesCollection(2).Values = "=Summary!$B$2:$B$" & lastrow + 1
            .SeriesCollection(2).XValues = "=Summary!$A$2:$A$" & lastrow + 1
        End With
        
        '~~> Close xlsm and save changes
        oXLWB.Close savechanges:=True
        
        '~~> Quit Excel if you created the instance and flush the toilet
        If QuitIt = True Then
            oXLApp.Quit
            Set oXLSht = Nothing
            Set oXLWB = Nothing
            Set oXLApp = Nothing
        End If
    End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    ACER R7 (Win 8.1+Office 2013+VS2013) || Sony VPCCB-45FN with a Win10+Office 2010. || Mac Book Pro (10.6.8) with Office 2011

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    32

    Re: [RESOLVED] closing excel problem

    Thanks for that Siddarth, I had come to the same sort of solution too. However, I found that if I make excel visible after opening, (oXLApp.Visible = True) then a instance of excel remained when the program finished. Setting oXLApp.Visible = False before closing excel, still left excel running. If I remove oXLApp.Visible = True from the code, everything is OK!

  10. #10
    Hyperactive Member
    Join Date
    Feb 2017
    Posts
    371

    Re: [RESOLVED] closing excel problem

    FWIW: This is what I determined:
    'If User Closes Excel other than from this App
    'Excel is still running in the background, hence
    'oxlApp object reference is still valid
    'SETTING EXCEL NOT VISIBLE PRIOR TO QUIT
    'IS THE KEY TO THE LEFT OVER INSTANCE PROBLEM
    oxlApp.Visible = False
    oxlApp.Quit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width