Can I suppress message box from VBA using xlwings?

Question

I am calling macros from VBA using xlwings (python 3.7). When run, the macro populates a message box

I was wondering if there's a way to suppress that (e.g. not showing the message box at all or click ok automatically) from the xlwings end (can't change macro, locked). My current setting looks like this:

app = xw.apps.active    # open application instance
app.visible = False  # Excel application not visible
app.display_alerts = False   # supress alert messages
app.screen_updating = False  # supress screen updates

Thanks!

Answer 1

As you can't change the macro, the left option is to click the OK button automatically. Obviously, you can't do it in the main process since it gets stuck once the message box occurs. So you need to create a child thread to do it concurrently, getting the main process back from stuck. In summary, you need two things:

• a child thread, e.g. threading.Thread
• a GUI automation library to catch the message box, e.g. pywin32, pywinauto or PyAutoGUI.

As you're using xlwings, pywin32 should be installed already as a dependency. So, use it here for example.

The whole process looks like:

import xlwings as xw
from listener import MsgBoxListener

# start child thread
listener = MsgBoxListener('Message-box-title-in-your-case', 3)
listener.start()

# main process as you did before
app = xw.apps.active    # open application instance
app.visible = False  # Excel application not visible
app.display_alerts = False   # supress alert messages
app.screen_updating = False  # supress screen updates
...

# stop listener thread
listener.stop()

Where MsgBoxListener is the child thread to catch and close the message box:

• title is the title of message box, as hidden in your screenshot
• interval is the frequency of detecting if exists a message box

# listener.py

import time
from threading import Thread, Event
import win32gui
import win32con


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):
        # 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)


if __name__=='__main__':
    t = MsgBoxListener('Microsoft Excel', 1)
    t.start()
    time.sleep(10)
    t.stop()