Solved

What's the best way to save and close all open Excel instances

Posted on 2006-11-02
19
1,272 Views
Last Modified: 2008-12-01
I just need a VBA code...

Thanks a lot!

Alex
0
Comment
Question by:paquicuba
  • 7
  • 6
  • 3
  • +1
19 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 150 total points
ID: 17860918
Sub Excel_CloseWorkBook(xlApp As Excel.Application, Optional bSaveChanges As Boolean = False)
Dim wb As Excel.Workbook
    On Error Resume Next
    If xlApp.Name > "" Then
      ' forces an error if the xlApp object has not been Set
    End If
    If Err.Number <> 0 Then Exit Sub ' nothing to do.
    On Error GoTo 0
        For Each wb In xlApp.Workbooks 'Close all open workbooks
            wb.Close bSaveChanges
        Next wb
    xlApp.UserControl = False
    Set xlApp = Nothing
End Sub
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17861070
Thanks a lot capricorn1 for your quick response. I'm not an Access or Excel expert, so please bear with me.

The following parameter is not optional -->> xlApp As Excel.Application <<--- What I'm supposed to pass here?

Here is what I'm doing:

Dim xlWorkbook As Excel.Workbook

Set xlWorkbook = Excel.Workbooks.Open("Path to my Excel file")
...
... ' changes to the spreadsheet here
...
xlWorkbook.Save
xlWorkbook.Close
Set xlWorkbook = Nothing


If I have one or more instances of Excel open the 'changes to the spreadsheet' don't get saved, instead I get a read-write message.

If I close all running Excel instances and try again, the all the 'changes to the spreadsheet' get saved.

So, I'll I want to do is close all running instances --without knowing their names-- before making 'changes to the spreadsheet'


Thanks a bunch!





 
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 17861078
Or to cycle all instances.. something like

Function CloseAllExcel()
On Error Resume Next

    Dim objEx As Object
    Dim objBk as Object
    Dim dblErr As Double

    Do Until dblErr <> 0
        Set objEx = GetObject(, "Excel.Application")
        dblErr = err
        For Each objBk In objEx.Workbooks
            objBk.Close bSaveChanges
        Next
        objEx.Application.Quit
        DoEvents
        Set objEx = Nothing
    Loop
   
End Function
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17861095
Or with an actual value instead of Cap's arguement... :-)

    objBk.Close SaveChanges:=True
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17861392
Thanks LPurvis for the code. However, it doesn't seem to work,

If I open a new workbook, it's named Book1 by Excel. After running your code Book1 is still open...

Any thoughts?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17861415
Is the workbook saved at all - or a totally blank one?
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17861465
In my case above, it's totally blank. But, I may have others open at the same time that may required to be saved before implicitly closing it .
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17861476
try

    Dim xlObj As Excel.Application
    Set xlObj = CreateObject("Excel.Application")
   
      xlObj.Workbooks.Open("Path to my Excel file")






    Excel_CloseWorkBook xlObj, True
    Set xlObj = Nothing
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17861484
Indeed.  So if there's a workbook open - that's never been saved - and the Excel instance is to be closed - where (and how) do you want that workbook to be saved/treated.
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17861546
Let's say that I just want to kill all Excel instances (forget about saving changes). so, if I go to Task-Manager I won't see an instance running under Applications or a process running under Processes.

Can the above be done using VBA?

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17861554
the codes posted above will do it for you
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17861570
So does the function not work if changed to abandon the changes?

Function CloseAllExcel()
On Error Resume Next

    Dim objEx As Object
    Dim objBk as Object
    Dim dblErr As Double

    Do Until dblErr <> 0
        Set objEx = GetObject(, "Excel.Application")
        dblErr = err
        For Each objBk In objEx.Workbooks
            objBk.Close SaveChanges:=False
        Next
        objEx.Application.Quit
        DoEvents
        Set objEx = Nothing
    Loop
   
End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862083
I find shutdown reboot works. Im surprised LPurvis hasnt mentioned that yet !
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17862112
I would never do so... that's old school Rocki.
Modern thinking uses the BFH method.

(H stands for "Hammer"... I'll let you work the rest out :-)
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17862190
@capricorn1

  Your code is doing the same thing: If I have an Excel instance open, other than the one I'm manipulating from
                                                     within Access, it opens the Excel file and tells me that it's
                                                     available for editing. When I click read-write, Access doesn't output all the data.
                                                     Then, if I close Excel its process doesn't get killed and I have to manually kill it
                                                     using Task Manager.

@LPurvis

   Your code only kills one instance if the database is open after Excel. Otherwise it does nothing...


¿...?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17862244
ha! LOL!

0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17862471
Hmm it should kill any running instance - but is only killing those (potentially several) opened after the mdb...
(Which I admit is odd and unexpected!)
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 17862654
ha, 50 way to kill Excel. Now there's something u dont see everyday
http://www.devcity.net/PrintArticle.aspx?ArticleID=239
only thing is, its .Net
but u can adapt some to vba

anyways time for bed! good nite all, tomorrow is Friday. What a nice way to finish off the week and drink some Krony!!!
0
 
LVL 23

Author Comment

by:paquicuba
ID: 17862741
I'm gonna use this code: http://www.experts-exchange.com/Databases/MS_Access/Q_21144270.html?query=check+excel+open&topics=39, to force users to close Excel.

I really appreciate all your help!

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now