We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

paquicuba
paquicuba asked
on
Medium Priority
1,323 Views
Last Modified: 2008-12-01
I just need a VBA code...

Thanks a lot!

Alex
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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!





 
Database Developer
CERTIFIED EXPERT
Commented:
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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Or with an actual value instead of Cap's arguement... :-)

    objBk.Close SaveChanges:=True

Author

Commented:
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?
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Is the workbook saved at all - or a totally blank one?

Author

Commented:
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 .
CERTIFIED EXPERT
Top Expert 2016

Commented:
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
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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?

CERTIFIED EXPERT
Top Expert 2016

Commented:
the codes posted above will do it for you
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2006

Commented:
I find shutdown reboot works. Im surprised LPurvis hasnt mentioned that yet !
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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 :-)

Author

Commented:
@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...


¿...?
CERTIFIED EXPERT
Top Expert 2006

Commented:
ha! LOL!

Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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!)
CERTIFIED EXPERT
Top Expert 2006
Commented:
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!!!

Author

Commented:
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!

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.