Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 28 of 28

Thread: Close All Instances Of Excel Without Saving Any Changes

  1. #21
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,879
    Location
    I want to put this code into my existing signed project, but was wondering exactly where one puts the following part
    Explain that little (or a lot) more

    Everything in my little test was in a single standard module, the Declares, the Type UUID, macros, etc.



    Okay, I solved my previous issue by placing everything except the below into a module.
    What happens if you put that in the module? I had everything in just one module


    In that sub there are no 'With' satements, so I'm guessing that an object is not getting Set. The only objects are the xlApps() array

    All I can suggest is

    1. Put a break point on the line and then Add Watch to investigate xlApps

    Also

    2. Single step through the code to see if

                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
    is not setting the xlApps(n) entry

    Again the Watch should show how many instances are being found

    This is mine with only one instance (different system)

    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    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

  2. #22
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    385
    Location
    I tested the code shown by Paul. I had 4 Excel instances open, including one hidden.
    The big surprise for me was this piece of code:
    With xlApps(i)
                    If .HinstancePtr <> hRunningApp Then
    ...
    Assuming that the xlApps variable contains several different instances, the above condition never meets, because for all xlApps (i) .HinstancePtr = hRunningApp.
    I looked at the help and Bill wrote in it:
    Application.Hinstance property (Excel)
    Returns a handle to the instance of Excel represented by the Application object.
    Since these are different instances, we should get different handles.
    That's why I used a different handle - Application.hwnd.


    Corrected code that works for me:
    Option Explicit
    
    'https://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel
    'http://www.vbaexpress.com/forum/showthread.php?68893-Close-All-Instances-Of-Excel-Without-Saving-Any-Changes
    
    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 hRunningApp As LongPtr '<--to remove
    
        hRunningApp = Application.HinstancePtr '<--to remove
    
    
        If GetAllExcelInstances(xlApps) Then
            Debug.Print String(30, "-")
            
            For i = LBound(xlApps) To UBound(xlApps)
                With xlApps(i)
                    '============= For test only ==============
                    Debug.Print "Hinstance: " & .HinstancePtr & " | hRunApp:" & hRunningApp    'Application.HinstancePtr
                    Debug.Print "Found instance.Hwnd: " & .Hwnd & " | This instance.Hwnd: " & Application.Hwnd
                    Debug.Print "WrkBks.Count: " & .Workbooks.Count
                    Debug.Print .Caption
                    Debug.Print "Is visible: " & .Visible
                    Debug.Print String(30, "-")
                    '==========================================
                    
                    If .Hwnd <> Application.Hwnd Then
                        .DisplayAlerts = False
                        .Quit
                    End If
                End With
            Next i
            
            Debug.Print String(30, "=")
        End If
    
    
        Application.DisplayAlerts = False
        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 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
    Artik

  3. #23
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,879
    Location
    Thanks!!!

    I'm using Office 365 and I couldn't get multiple Excel instances to open so I could test

    I think that the code I 'borrowed' could be tightened up a bit, but since I couldn't see any way to test, I tried to leave things alone
    Last edited by Paul_Hossler; 06-17-2021 at 05:47 PM.
    ---------------------------------------------------------------------------------------------------------------------

    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

  4. #24
    VBAX Tutor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    284
    Location
    Many thanks to you both!

    The stuff that you are mentioning is way over my head I'm afraid to say.

    I signed back in to work and made the modifications as suggested and can say it works brilliantly!!

    I'm one very happy chap that doesn't have to systematically go through every workbook instance and click the prompt before closing.

    Thank you!!

    For anyone else using this code it should come with a massive word of warning as it will obviously exit Excel completely without saving anything.

    Regards
    Steve

  5. #25
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    385
    Location
    Quote Originally Posted by Paul_Hossler View Post
    I'm using Office 365 and I couldn't get multiple Excel instances to open so I could test
    Paul, but that doesn't excuse you
    I think you forgot the code
    Sub NewExcelInstance()
      Dim xlApp As Excel.Application
      
      Set xlApp = New Excel.Application
      xlApp.Workbooks.Add
      xlApp.Visible = True
      
    End Sub
    Artik

  6. #26
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,879
    Location


    Didn't think about creating them programmaically
    ---------------------------------------------------------------------------------------------------------------------

    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

  7. #27
    Paul: Immediately after launching Excel, press and hold the alt key.

    Steve: If it does not have to be VBA and all you want is shut down all Excel windows belonging to one Excel.exe without saving, simply Shift+click the close box in the top-right corner. Then when the first save prompt comes, hold shift and click No.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  8. #28
    VBAX Tutor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    284
    Location
    It does have to be VBA Jan, as lots of other things are happening with data being pulled from these Workbooks. The scenario that it is being used in it is safer so as to not confuse data.

Posting Permissions

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