Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 28

Thread: Close All Instances Of Excel Without Saving Any Changes

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

    Close All Instances Of Excel Without Saving Any Changes

    I understand that this might seem a bit of an unusual one to request, but I would really appreciate some help in trying to achieve this.

    I have the following so far, which doesn't quite close everything. The workbook that holds the sub exits and closes, but other workbooks that have been used, that have the default Book1, Book2, Book3 etc. and still contain data that have not been saved, still remain open.

    Private Sub cmdExit_Click()
    
        For Each wb In Workbooks
            If wb.Name <> ThisWorkbook.Name Then
                wb.Close SaveChanges:=False
            End If
        Next
        
        For Each wb In Workbooks
            If Not wb.FullName = ThisWorkbook.FullName Then wb.Close False
        Next
        
        ThisWorkbook.Saved = True
        Application.Quit
        
    End Sub
    Many thanks!
    Steve

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Nit Pick --

    Close All Instances Of Excel Without Saving Any Changes
    You're really not closing all instances.

    You're closing all open workbooks



    Option Explicit
    
    
    Sub cmdExit_Click()
        Dim wb As Workbook
    
    
        For Each wb In Workbooks
            If Not wb Is ThisWorkbook Then wb.Close SaveChanges:=False
        Next
        
        ThisWorkbook.Saved = True
        Application.Quit
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Nit Pick --


    You're really not closing all instances.

    You're closing all open workbooks
    Many thanks, Paul and point well made.

    Steve

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      workbooks.close
    End Sub

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post
    Sub M_snb()
      workbooks.close
    End Sub
    Prompts for unsaved WBs

    Doesn't close Excel
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      Application.displayalerts=false
      Application.quit
    End Sub

  7. #7
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Thanks to you both for the further updates.

    I was going to have to open the thread again with Paul's suggestion, as this didn't want to close the other separate instances of Excel with their respective (unsaved) Book1, Book2, Book3 etc.

    I'm not sure if this would make a difference if the other workbooks were in separate instances of Excel, which is the scenario that I have?

    Anyhow, I'm going to have to wait until tomorrow to try SNBs post at #6 when I'm back in the office. Fingers crossed that it closes all instances of Excel without saving anything.

    Thanks!
    Steve

  8. #8
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Well I used snb's code today and to my surprise, it still didn't have the desired effect.

    It closed the Workbook that had the sub in it, but the other workbooks stayed open and as they were.

    Any other ideas?

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by HTSCF Fareha View Post
    Any other ideas?
    Did the code in post #2 work?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    It is possible that some workbooks are open in other instances of the application. Then you will have to search for these instances. But this OP has to investigate.
    All of the above codes close workbooks from only one instance.

    Artik

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    IIRC only Excel 2013 allowed multiple instances of Excel (i.e. 2+ enteries in Task Manager) so I'd guess that there's still something else going on

    https://docs.microsoft.com/en-us/off...rface-in-excel

    This closes all visible and hidden workbooks in the instance and is only a slight tweak of the original macro

    Option Explicit
    
    Sub cmdExit_Click()
        Dim wb As Workbook
    
        For Each wb In Workbooks
            If Not wb Is ThisWorkbook Then wb.Close SaveChanges:=False
        Next
        
        ThisWorkbook.Saved = True
        Application.Quit
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Paul, in answer to your question in #9, your code produced the same result as the other suggested solutions.

    I think that Artik has probably hit the nail on the head in that our bespoke programme is using the option to "Export date to Excel" which is producing the Book1, Book2, Book3 etc.

    With my obviously limited knowledge of Excel, I hadn't thought of this, but now it has been mentioned by Artik then this seems that this is the logical reason why.

    I'm guessing that this changes things somewhat? Sorry!

    Steve

  13. #13
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Must have been typing at the same time Paul.

    I will give your suggestion a go today and get back to you later.

    Many thanks!

    Steve

    [Edit]
    I've just read the link that you provided and this is exactly what is happening. I think work is using Office 2016, so this supports the "creation of multiple SDI".
    Last edited by HTSCF Fareha; 06-16-2021 at 10:21 PM.

  14. #14
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    This can only be run in Word.
    The question to save changed files can't be hidden and has to be answered by the user.

    Sub M_snb()
       For Each it In Tasks
         If InStr(it.Name, ".xls") Then
            it.Activate
            it.Close
         End If
       Next
    End Sub

  15. #15
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Okay, so Paul's suggestion didn't work.

    In relation to yours snb, would this need to be combined with other code to actually close everything, or will it run as is?

  16. #16
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Please check it, starting the macro in Word.

  17. #17
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Okay, have run this from word. It gave a prompt for the first Workbook and closed it, but did nothing after that with the others that were still open. I thought it might be the file extension, so tried adjusting that to. xlsx, but still no go

    (Edit) The only file for which the prompt was asked for was the .xlsm one.
    Last edited by HTSCF Fareha; 06-17-2021 at 06:11 AM.

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. I can't test this since I don't have a multi-instance capability

    2. In a single instance, with multiple workbooks open it seems to work

    3. I modified the StackOverflow code a bit, so you can blame me if it doesn't work

    4. Good luck

    edit - miscopied code in original post

    Option Explicit
    
    
    'https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel
    
    
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal Hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As LongPtr) As LongPtr
    Private Declare PtrSafe Function IIDFromString Lib "ole32" (ByVal lpsz As LongPtr, ByRef lpiid As UUID) As LongPtr
    Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" (ByVal Hwnd As LongPtr, ByVal dwId As LongPtr, ByRef riid As UUID, ByRef ppvObject As Object) As LongPtr
    
    
    Type UUID 'GUID
        Data1 As Long
        Data2 As Integer
        Data3 As Integer
        Data4(7) As Byte
    End Type
    
    
    Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
    Const OBJID_NATIVEOM As LongPtr = &HFFFFFFF0
    
    
    ' Run as entry point of example
    Sub CloseAllWorkbooksWithoutSaving()
        Dim i As Long
        Dim xlApps() As Application
        Dim wb As Workbook
        Dim hRunningApp As LongPtr
    
    
        hRunningApp = Application.HinstancePtr
    
    
        If GetAllExcelInstances(xlApps) Then
            For i = LBound(xlApps) To UBound(xlApps)
                With xlApps(i)
                    If .HinstancePtr <> hRunningApp Then
                        For Each wb In .Workbooks
                            wb.Close SaveChanges:=False
                        Next
        
                        .ActiveWorkbook.Saved = True
                        .Quit
                    End If
                End With
            Next
        End If
    
    
        For Each wb In Application.Workbooks
            If Not wb Is ThisWorkbook Then wb.Close SaveChanges:=False
        Next
    
    
        Application.ActiveWorkbook.Saved = True
        Application.Quit
    End Sub
    
    
    ' Actual public facing function to be called in other code
    Public Function GetAllExcelInstances(xlApps() As Application) As Long
        Dim n As Long
        Dim app As Application
        Dim hWndMain As LongPtr
    
    
        On Error GoTo MyErrorHandler
    
    
        ' Cater for 100 potential Excel instances, clearly could be better
        ReDim xlApps(1 To 100)
    
    
        hWndMain = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
    
    
        Do While hWndMain <> 0
            Set app = GetExcelObjectFromHwnd(hWndMain)
            If Not (app Is Nothing) Then
                If n = 0 Then
                    n = n + 1
                    Set xlApps(n) = app
                ElseIf checkHwnds(xlApps, app.Hwnd) Then
                    n = n + 1
                    Set xlApps(n) = app
                End If
            End If
            hWndMain = FindWindowEx(0&, hWndMain, "XLMAIN", vbNullString)
        Loop
    
    
        If n Then
            ReDim Preserve xlApps(1 To n)
            GetAllExcelInstances = n
        Else
            Erase xlApps
        End If
    
    
        Exit Function
    
    
    MyErrorHandler:
        MsgBox "GetAllExcelInstances" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description
    
    
    End Function
    
    
    Private Function checkHwnds(xlApps() As Application, Hwnd As LongPtr) As Boolean
        Dim i As Integer
    
    
        For i = LBound(xlApps) To UBound(xlApps)
            If xlApps(i).Hwnd = Hwnd Then
                checkHwnds = False
                Exit Function
            End If
        Next i
    
    
        checkHwnds = True
    
    
    End Function
    
    
    Private Function GetExcelObjectFromHwnd(ByVal hWndMain As LongPtr) As Application
        Dim hWndDesk As LongPtr
        Dim Hwnd As LongPtr
        Dim strText As String
        Dim lngRet As Long
        Dim iid As UUID
        Dim obj As Object
    
    
    
    
        On Error GoTo MyErrorHandler
    
    
        hWndDesk = FindWindowEx(hWndMain, 0&, "XLDESK", vbNullString)
    
    
        If hWndDesk <> 0 Then
    
    
            Hwnd = FindWindowEx(hWndDesk, 0, vbNullString, vbNullString)
    
    
            Do While Hwnd <> 0
    
    
            strText = String$(100, Chr$(0))
            lngRet = CLng(GetClassName(Hwnd, strText, 100))
    
    
            If Left$(strText, lngRet) = "EXCEL7" Then
    
    
                Call IIDFromString(StrPtr(IID_IDispatch), iid)
    
    
                If AccessibleObjectFromWindow(Hwnd, OBJID_NATIVEOM, iid, obj) = 0 Then 'S_OK
    
    
                    Set GetExcelObjectFromHwnd = obj.Application
                    Exit Function
    
    
                End If
    
    
            End If
    
    
            Hwnd = FindWindowEx(hWndDesk, Hwnd, vbNullString, vbNullString)
            Loop
    
    
            On Error Resume Next
    
    
        End If
    
    
        Exit Function
    
    
    MyErrorHandler:
        MsgBox "GetExcelObjectFromHwnd" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description
    
    
    End Function
    Attached Files Attached Files
    Last edited by Paul_Hossler; 06-17-2021 at 10:07 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Very many thanks, Paul for coming up with this.

    I'm going to sign back into work just to see if this works as I'm itching to try it.

    As with all sensible businesses, all the macro enabled templates that are used have to be digitally signed for approval and use. I want to put this code into my existing signed project, but was wondering exactly where one puts the following part
    'https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal Hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As LongPtr) As LongPtr
    Private Declare PtrSafe Function IIDFromString Lib "ole32" (ByVal lpsz As LongPtr, ByRef lpiid As UUID) As LongPtr
    Private Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" (ByVal Hwnd As LongPtr, ByVal dwId As LongPtr, ByRef riid As UUID, ByRef ppvObject As Object) As LongPtr
    
    Type UUID 'GUID
        Data1 As Long
        Data2 As Integer
        Data3 As Integer
        Data4(7) As Byte
    End Type
    
    Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
    Const OBJID_NATIVEOM As LongPtr = &HFFFFFFF0
    If I place it directly at the top under 'Option Explicit' and then run it, I get a Compile error: Cannot define a Public user-defined type within an object module' on this line

    Type UUID

  20. #20
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Okay, I solved my previous issue by placing everything except the below into a module.

    ' Run as entry point of example
    Sub CloseAllWorkbooksWithoutSaving()
        Dim i As Long
        Dim xlApps() As Application
        Dim wb As Workbook
        Dim hRunningApp As LongPtr
    
    
        hRunningApp = Application.HinstancePtr
    
    
        If GetAllExcelInstances(xlApps) Then
            For i = LBound(xlApps) To UBound(xlApps)
                With xlApps(i)
                    If .HinstancePtr <> hRunningApp Then
                        For Each wb In .Workbooks
                            wb.Close SaveChanges:=False
                        Next
        
                        .ActiveWorkbook.Saved = True
                        .Quit
                    End If
                End With
            Next
        End If
    
    
        For Each wb In Application.Workbooks
            If Not wb Is ThisWorkbook Then wb.Close SaveChanges:=False
        Next
    
    
        Application.ActiveWorkbook.Saved = True
        Application.Quit
    End Sub
    Running it after signing back into work, after producing 6 workbooks, which are all unsaved and are as Book1, Book2, Book3 etc. I get the following error message as per the attached.
    Attached Images Attached Images

Posting Permissions

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