Home>

There is a macro created by Excel365 that is started by a Windows task and automatically executed, and I was operating it, but if it takes too long, there is a need to terminate it from an external program, and I am struggling.

The corresponding method is considered by using the Win32 API of FindWindow + SendMessage (WM_SYSCOMMAND, SC_CLOSE).
With this method, SC_CLOSE reaches the Excel macro correctly and tries to exit, but I am in trouble because the confirmation dialog "Do I want to save the changes?" Appears.

This dialog is like "NUIDialog" unlike the normal dialog "# 32770", I can't get the handle of the button with FindWindowEx, and I can't use the method of sending BM_CLICK to the button.
The confirmation dialog itself can be obtained with FindWindow, but sending an Enter key event etc. to the dialog has no effect. ..

Since the Excel macro writes the time etc. to the sheet, it seems unavoidable that the save confirmation dialog of the workbook appears when receiving SC_CLOSE, but I want to close it automatically because it is unmanned operation.

I added Workbook_BeforeClose to the ThisWorkbook module, but before it was called, a save confirmation dialog appeared, and control stopped there, and the Workbook_BeforeClose process did not work.

Private Sub Workbook_BeforeClose (Cancel As Boolean)
  Application.DisplayAlerts = False
  ThisWorkbook.Save
  ThisWorkbook.Close
  Application.Quit
End Sub

I added it to Workbook_Open, wondering if I should disable autosave, but it has no effect.

Private Sub Workbook_Open ()
  ThisWorkbook.EnableAutoRecover = False
End Sub

Is there any good way?

  • Answer # 1

    This dialog is like "NUIDialog" unlike the normal dialog "# 32770", I can't get the handle of the button with FindWindowEx, and I can't use the method of sending BM_CLICK to the button.

    It may be solved by using UI Automation. I would like to introduce the similar requests and questions and answers regarding the operation of NUIDialog in Office products.

    How to press buttons on NUIDialog? --MSV Visual Studio

    The implementation using C ++ COM in Visual Studio is introduced in the answer, but when I tried it in my environment, Windows 10/Visual Studio 2017/Excel 2013, based on this, "'Book 1'at the end of Excel" Do you want to save the changes? "I was able to press the" Save (&S) "," Do not save (&N) "and" Cancel "buttons in NUIDialog.

    As a point:

    Specify the button name excluding the access key. "Save", "Do not save", "Cancel".

    Win32 native COM programming, but worked on both Win32 (32-bit) and x64 (64-bit) builds. Excel 2013 is a 32-bit version, but it seems that there is no effect.

    If possible, it may be easier to rewrite the code in a C # project. If it works with AnyCPU build in C # and .NET Framework projects, you don't have to worry about 32-bit and 64-bit.

    is. The questioner's Excel is a new version of Office 365, so it can not be said that the environment is exactly the same, but if it helps.

  • Answer # 2

    I solved it using UI Automation that received comments. The final result is as follows.

    using System;
    using System.Runtime.InteropServices;// DLL Import
    using Automation = System.Windows.Automation;// UIAutomation
    using AutomationElement = System.Windows.Automation.AutomationElement;
    using PropertyCondition = System.Windows.Automation.PropertyCondition;
    using ControlType = System.Windows.Automation.ControlType;
    class DialogCloser
    {
        [DllImport ("user32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        public static extern IntPtr FindWindow (string sClassName, string sWinTitle);
        public static void Main ()
        {
            IntPtr hWndDialog;
            hWndDialog = FindWindow ("NUIDialog", "Microsoft Excel");
            if ((int) hWndDialog! = 0) {
                AutomationElement elemDialog;
                elemDialog = AutomationElement.FromHandle (hWndDialog);
                FindElement (elemDialog, "do not save");
            }
        }
        private static bool FindElement (AutomationElement elemNode, string sFindText)
        {
            AutomationElement.AutomationElementInformation elemInfo;
            elemInfo = elemNode.Current;
            if (elemInfo.Name == sFindText&&elemInfo.ClassName == "NetUIButton") {
                elemNode.SetFocus ();
                object oPattern;
                if (elemNode.TryGetCurrentPattern (Automation.InvokePattern.Pattern, out oPattern)) {
                    ((Automation.InvokePattern) oPattern) .Invoke ();
                }
                return true;
            }
            Automation.AutomationElementCollection elements;
            elements = elemNode.FindAll (Automation.TreeScope.Children, Automation.Condition.TrueCondition);
            foreach (AutomationElement element in elements) {
                if (FindElement (element, sFindText)) {
                    return true;
                }
            }
            return false;
        }
    }

  • Answer # 3

    ThisWorkbook.Save


    To

    ThisWorkbook.Save = True


    And

    Me.Save = True


    Why don't you change it to?

  • Answer # 4

    It's different from the purpose of the question, but ...

    ThisWorkbook.Saved = True


    Then, even if you do not actually save it, it will be treated as saved. (There is no save confirmation dialog)
    Somehow with the flow of forced terminationSaved = TrueIs it something that can't be done?


    Postscript
    Since you can enumerate the running Excel instances in RunningObjectTable,
    It seems that it can be realized by operating Excel with COM from there, but is it too different from the question?