Hey there! I get it—automating those user-facing Excel GUI interactions (like clicking ribbon buttons, selecting radio options, and filling input boxes) can feel tricky when win32com alone doesn’t cut it. Since win32com excels at workbook-level operations but not UI elements, we’ll pair it with a Windows GUI automation library to fill the gap. Let’s break down two reliable approaches: PyAutoGUI (screenshot-based) and Pywinauto (control-based, more robust).

Option 1: PyAutoGUI (Quick Screenshot-Based Setup) #

This is a fast way to get started if you don’t want to dig into control properties. It works by matching screenshots of UI elements and simulating mouse clicks/keyboard inputs.

Step 1: Install PyAutoGUI #

pip install pyautogui opencv-python

(OpenCV adds confidence matching for screenshots, which handles minor visual variations)

Step 2: Integrate with Your Existing Code #

Here’s how to modify your script to handle the full GUI flow:

import os
import sys
import win32com.client as win32
import json
import time
import pyautogui

# Load config
f = open('*ConfidentialPath*')
data = json.load(f)
Path_VBA = data['Path_VBA']

try:
    # Launch Excel and open workbook
    xl = win32.Dispatch("Excel.Application")
    wb = xl.Workbooks.Open(Path_VBA)
    xl.visible = True
    time.sleep(3)  # Wait for Excel to fully load

    # Activate Excel window to ensure PyAutoGUI targets the right app
    excel_windows = pyautogui.getWindowsWithTitle("Excel")
    if excel_windows:
        excel_windows[0].activate()
        time.sleep(1)

    # --- Step 1: Click the ribbon menu button ---
    # Use a clear screenshot of your ribbon button (save as ribbon_button.png)
    button_location = pyautogui.locateOnScreen('ribbon_button.png', confidence=0.8)
    if button_location:
        pyautogui.click(button_location)
        time.sleep(2)  # Wait for the form to pop up
    else:
        print("Couldn't find the ribbon button! Check your screenshot.")

    # --- Step 2: Click the radio button in the popup form ---
    radio_button_location = pyautogui.locateOnScreen('radio_button.png', confidence=0.8)
    if radio_button_location:
        pyautogui.click(radio_button_location)
        time.sleep(1)
    else:
        print("Couldn't find the radio button!")

    # --- Step 3: Fill InputBox with username and password ---
    # InputBox should be focused automatically, but click it first if needed
    # pyautogui.click(input_box_location)
    pyautogui.typewrite("your_username")  # Type username
    pyautogui.press("tab")  # Switch to password field
    pyautogui.typewrite("your_password")
    pyautogui.press("enter")  # Click confirm button

    # Continue with your existing macro execution
    xl.Application.Run("OpenWorkbook")
    time.sleep(5)
    xl.Application.Run("CloseWorkbook")
    time.sleep(5)

    # Cleanup
    xl.Workbooks(1).Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Application.Quit()

except Exception as ex:
    template = "An exception of type {0} occurred. Arguments:\n{1!r}"
    message = template.format(type(ex).__name__, ex.args)
    print(message)
    if 'xl' in locals():
        xl.Application.Quit()
del xl

Quick Tips for PyAutoGUI: #

• Use pyautogui.displayMousePosition() to get exact coordinates if screenshot matching fails (fallback to pyautogui.click(x=100, y=200)).
• Ensure screenshots are cropped tightly to the UI element, with no extra background.

Option 2: Pywinauto (Control-Based, More Reliable) #

This library interacts directly with Windows UI controls via their automation properties, so it’s less prone to breaking if screen resolution or Excel’s layout changes.

Step 1: Install Pywinauto #

pip install pywinauto

Step 2: Prep with the Inspect Tool #

First, use Windows’ built-in Inspect Tool (search for "Inspect" in the Start Menu) to get properties of your UI elements. Look for:

• AutomationId (most reliable)
• Name (visible text on the element)
• ControlType (e.g., "Button", "RadioButton")

Step 3: Integrate with Your Code #

Here’s a sample implementation tailored to your workflow:

import os
import sys
import win32com.client as win32
import json
import time
from pywinauto import Application
from pywinauto.findwindows import ElementNotFoundError

# Load config
f = open('*ConfidentialPath*')
data = json.load(f)
Path_VBA = data['Path_VBA']

try:
    # Launch Excel and open workbook
    xl = win32.Dispatch("Excel.Application")
    wb = xl.Workbooks.Open(Path_VBA)
    xl.visible = True
    time.sleep(3)

    # Connect to Excel with pywinauto
    app = Application().connect(title_re=".*Excel.*", class_name="XLMAIN")
    excel_main = app.window(class_name="XLMAIN")

    # --- Step 1: Click the ribbon menu button ---
    # Replace with your ribbon button's actual AutomationId/Name
    try:
        ribbon_button = excel_main.child_window(automation_id="YourRibbonButtonID", control_type="Button")
        ribbon_button.click()
        time.sleep(2)
    except ElementNotFoundError:
        print("Ribbon button not found! Verify the AutomationId in Inspect.")

    # --- Step 2: Handle the popup form ---
    # Replace with your form's title
    popup_form = app.window(title="YourPopupFormTitle")
    popup_form.wait('ready', timeout=10)

    # Click the radio button (replace with your radio button's properties)
    radio_button = popup_form.child_window(name="YourRadioButtonText", control_type="RadioButton")
    radio_button.click()
    time.sleep(1)

    # --- Step 3: Fill InputBox ---
    # Replace with your InputBox's title
    input_box = app.window(title="YourInputBoxTitle")
    input_box.wait('ready', timeout=10)

    # Username field (use found_index to target the first text box)
    username_field = input_box.child_window(class_name="Edit", found_index=0)
    username_field.set_text("your_username")

    # Password field (target the second text box)
    password_field = input_box.child_window(class_name="Edit", found_index=1)
    password_field.set_text("your_password")

    # Click confirm button
    confirm_button = input_box.child_window(name="OK", control_type="Button")
    confirm_button.click()

    # Continue with macros
    xl.Application.Run("OpenWorkbook")
    time.sleep(5)
    xl.Application.Run("CloseWorkbook")
    time.sleep(5)

    # Cleanup
    xl.Workbooks(1).Close(SaveChanges=1)
    xl.DisplayAlerts = True
    xl.Application.Quit()

except Exception as ex:
    template = "An exception of type {0} occurred. Arguments:\n{1!r}"
    message = template.format(type(ex).__name__, ex.args)
    print(message)
    if 'xl' in locals():
        xl.Application.Quit()
del xl

Key Tips for Pywinauto: #

• Always use wait('ready', timeout=10) to ensure windows/controls are fully loaded before interacting.
• If AutomationId isn’t available, use class_name + found_index to target controls (e.g., multiple text boxes).

Final Notes #

• Test each step individually first (e.g., just clicking the ribbon button) before combining everything.
• Adjust time.sleep() durations based on how fast your Excel instance loads elements.
• If your Excel version or custom ribbon changes, you’ll need to update the control properties/screenshots.

内容的提问来源于stack exchange，提问作者Rooty