Shulou(Shulou.com)06/03 Report--

This article shows you how to use the Python subprocess to close the pop-up window in Excel automation, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

You may encounter a message box / pop-up window (MsgBox) in the process of automating Excel operations with Python, especially when VBA is involved. An artificial response is required at this point, otherwise the code is stuck until it times out [^ 1] [^ 2]. The fundamental solution is not to have a pop-up window in the VBA code, but sometimes we don't have the right to modify the Excel file being manipulated, for example, this is the object for our automated testing. So this article records how to solve this kind of problem from a code point of view.

Hypothetical scenario

Use xlwings (or some other automation library) to open the Excel file test.xlsm and read the contents of the Sheet1roomA1 cell. A very simple operation:

Import xlwings as xw wb = xw.Book ('test.xlsm') msg = wb.sheets (' Sheet1'). Range ('A1'). Value print (msg) wb.close ()

Unfortunately, however, there was a warm welcome when the workbook was opened:

Private Sub Workbook_Open () MsgBox "Welcome" MsgBox "to open" MsgBox "this file." End Sub

The first pop-up window, Welcome, jammed the Excel,Python code and got stuck on the first line.

Basic ideas

It is impossible to deal with or bypass such problems directly in the main program, nor can you expect anyone to squat and click on the next step at any time-then start a subthread to escort it. Therefore, the solution is to use child threads to listen and close the pop-up window at any time until the main program is successfully completed.

To solve this problem, you need the following two knowledge points (extracurricular study of basic knowledge):

Python multithreading (threading.Thread is used in this article)

Python interface automation library (this article involves pywinauto and pywin32)

Pywinauto scheme

Pywinauto is a Windows interface automation library that simulates mouse and keyboard manipulation of forms and controls [^ 3]. Unlike the traditional way of getting the handle and then the property, pywinauto's API is more friendly and pythonic. For example, two lines of code fix the window capture and click:

From pywinauto.application import Application win = Application (backend= "win32") .connect (title='Microsoft Excel') win.Dialog.Button.click ()

This paper uses a custom thread class to automatically execute the run () function after starting the thread to complete the above operation. The specific code is as follows, notice the two parameters in the constructor:

The title of the pop-up window that title needs to capture. For example, the title of Excel default pop-up window is Microsoft Excel.

The frequency of interval snooping, that is, how often is it checked?

# listener.py import time from threading import Thread, Event from pywinauto.application import Application class MsgBoxListener (Thread): def _ _ init__ (self, title:str) Interval:int): Thread.__init__ (self) self._title = title self._interval = interval self._stop_event = Event () def stop (self): self._stop_event.set () @ property def is_running (self): return not self._stop_event.is_set () def run (self): While self.is_running: try: time.sleep (self._interval) self._close_msgbox () except Exception as e: print (e Flush=True) def _ close_msgbox (self):''Close the default Excel MsgBox with title "Microsoft Excel".'' Win = Application (backend= "win32") .connect (title=self._title) win.Dialog.Button.click () if _ _ name__=='__main__': t = MsgBoxListener ('Microsoft Excel', 3) t.start () time.sleep (10) t.stop ()

Therefore, the whole process is divided into three steps:

Startup subthread listening pop-up window

Open Excel in the main thread to start automatic operation

Close child threads

Import xlwings as xw from listener import MsgBoxListener # start listen thread listener = MsgBoxListener ('Microsoft Excel', 3) listener.start () # main process as before wb = xw.Book (' test.xlsm') msg = wb.sheets ('Sheet1'). Range (' A1'). Value print (msg) wb.close () # stop listener thread listener.stop ()

By the time this problem is basically solved, the local operation effect has completely reached the expectation. But my real need is to automate the testing of Excel files on the server as a system service. Later, I found that when running as a system service, pywinauto could not catch the pop-up window! This may be a potential problem for pywinauto [^ 4].

Win32gui scheme

Then we have to turn to the relatively low-level win32gui, which fortunately solves the above problems perfectly.

Win32gui is part of the pywin32 library, so the actual installation command is:

Pip install pywin32

The whole scenario is exactly the same as described earlier, except that it replaces the method of closing the pop-up window in the MsgBoxListener class:

Import win32gui, win32con def _ close_msgbox (self): # find the top window by title hwnd = win32gui.FindWindow (None, self._title) if not hwnd: return # find child button h_btn = win32gui.FindWindowEx (hwnd, None,'Button', None) if not h_btn: return # show text text = win32gui.GetWindowText (h_btn) print (text) # click button win32gui.PostMessage (h_btn Win32con.WM_LBUTTONDOWN, None, None) time.sleep (0.2) win32gui.PostMessage (h_btn, win32con.WM_LBUTTONUP, None, None) time.sleep (0.2) more general scheme

More generally, when there is a pop-up window with both the default title and the custom title, it is not easy to capture with the title. For example

MsgBox "Message with default title.", vbInformation, MsgBox "Message with title My App 1", vbInformation, "My App 1" MsgBox "Message with title My App 2", vbInformation, "My App 2"

Then expand the search and click all the buttons that contain deterministic descriptions (such as OK,Yes,Confirm) to close the pop-up window. Similarly replace the _ close_msgbox () method of the MsgBoxListener class (and the title parameter is no longer needed in the constructor):

Def _ close_msgbox (self):''Click any button ("OK", "Yes" or "Confirm") to close message box.''' # get handles of all top windows h_windows = [] win32gui.EnumWindows (lambda hWnd, param: param.append (hWnd), h_windows) # check each window for h_window in h_windows: # get child button with text OK Yes or Confirm of given window h_btn = win32gui.FindWindowEx (h_window, None,'Button', None) if not h_btn: continue # check button text text = win32gui.GetWindowText (h_btn) if not text.lower () in ('ok',' yes', 'confirm'): continue # click button win32gui.PostMessage (h_btn, win32con.WM_LBUTTONDOWN, None None) time.sleep (0.2) win32gui.PostMessage (h_btn, win32con.WM_LBUTTONUP, None, None) time.sleep (0.2)

Finally, the example demonstration ends, and you don't have to worry about unexpected pop-up windows any more.

[^ 1]: Handling VBA popup message boxes in Microsoft Excel

[^ 2]: Trying to catch MsgBox text and press button in xlwings

[^ 3]: What is pywinauto

[^ 4]: Remote Execution Guide

The above is how to use the Python subprocess to close the pop-up window in Excel automation. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.