Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1308
  • Last Modified:

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

I just need a VBA code...

Thanks a lot!

Alex
0
paquicuba
Asked:
paquicuba
  • 7
  • 6
  • 3
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)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
0
 
paquicubaAuthor 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!





 
0
 
Leigh PurvisDatabase DeveloperCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Leigh PurvisDatabase DeveloperCommented:
Or with an actual value instead of Cap's arguement... :-)

    objBk.Close SaveChanges:=True
0
 
paquicubaAuthor 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?
0
 
Leigh PurvisDatabase DeveloperCommented:
Is the workbook saved at all - or a totally blank one?
0
 
paquicubaAuthor 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 .
0
 
Rey Obrero (Capricorn1)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
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
paquicubaAuthor 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?

0
 
Rey Obrero (Capricorn1)Commented:
the codes posted above will do it for you
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
rockiroadsCommented:
I find shutdown reboot works. Im surprised LPurvis hasnt mentioned that yet !
0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
paquicubaAuthor 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...


¿...?
0
 
rockiroadsCommented:
ha! LOL!

0
 
Leigh PurvisDatabase DeveloperCommented:
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
 
rockiroadsCommented:
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
 
paquicubaAuthor 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!

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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now