Python: How to use the "win32com" event-class-method-execution within a script (Excel-Butt-CodePudding

In reference to this post Excel button click event in Python I want to not only print out a message to the console, but instead call a function or change a variable value (see below my test-script).

Since the "win32.WithEvents" requires a "class" instead of a "class instance" in its arguments, I am wondering how to access this class instance of the "ButtonEvent" used by this button to get a return value or change a variable ?

In the below example, I am using the "global keepOpen" to exit the while-loop. but using globals is not a good style. How can I change the "keepOpen" variable without "global" ?

I basically would like to change variables or call other functions/class-methods when the button is clicked.

How can I achieve this ?

import win32com.client as win32
import pythoncom
import sys

# use absolute pathes
WORKBOOK = "d:/LabTestPy/labPyCommon/test/test_excel_RegDict.xlsm"
WORKSHEET = "Sheet"

# the button event will open this class
class ButtonEvents:
    # method executed on doubleclick to close while loop
    def OnDblClick(self, *args):
        print("button double clicked")
        global keepOpen
        keepOpen = False

#config win32com excel connection
xlApp = win32.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True
xlWb = xlApp.Workbooks.Open(WORKBOOK)
xlWs = xlWb.Sheets(WORKSHEET)
# define button event callback class
xlButtonEvents=win32.WithEvents(xlWs.OLEObjects("CommandButton1").Object,ButtonEvents)

# a global variable to exit the while-loop
global keepOpen
keepOpen = True

# a while loop to wait until the button in excel is double-clicked
while keepOpen:  #How to avoid a global variable here ?
    pythoncom.PumpWaitingMessages()

    # How to execute a function here, when another button is pushed ?


print("Script finished - closing Excel")

xlWb.Close(False) # False: do not save on close, True: save on close
xlApp.Quit()

CodePudding user response：

I just found out that "xlButtonEvents" is the instance of the class "ButtonEvents", and I simply needed to add "keepOpen" as a class attribute, like this:

import win32com.client as win32
import pythoncom
import sys

# use absolute pathes
WORKBOOK = "d:/LabTestPy/labPyCommon/test/test_excel_RegDict.xlsm"
WORKSHEET = "Sheet"

# the button event will open this class
class ButtonEvents:
    
    def __init__(self):
        self.keepOpen = True        
        self.myvar = 1
        
    # method executed on doubleclick to close while loop
    def OnDblClick(self, *args):
        print("button double clicked")
        self.keepOpen = False
        self.myvar = 3
        

#config win32com excel connection
xlApp = win32.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True
xlWb = xlApp.Workbooks.Open(WORKBOOK)
xlWs = xlWb.Sheets(WORKSHEET)
# define button event callback class
xlButtonEvents=win32.WithEvents(xlWs.OLEObjects("CommandButton1").Object,ButtonEvents)
    
# a while loop to wait until the button in excel is double-clicked
while xlButtonEvents.keepOpen:
    pythoncom.PumpWaitingMessages()

    print(xlButtonEvents.myvar)

print("Script finished - closing Excel")

xlWb.Close(False) # False: do not save on close, True: save on close
xlApp.Quit()

CodePudding user response：

Here is how to call another function from a double clicked button. Button 1 exits the loop and script. Button 2 calls the function "myfunc":

import win32com.client as win32
import pythoncom

# use absolute pathes
WORKBOOK = "d:/LabTestPy/labPyCommon/test/test_excel_RegDict.xlsm"
WORKSHEET = "Sheet"

def myfunc(myarg):
    print("myfunc was executed "   myarg)


# the button 1 will exit the loop
class Button1Events:    
    def __init__(self):
        self.keepOpen = True        
    # method executed on doubleclick to close while loop
    def OnDblClick(self, *args):
        print("button1 double clicked")
        self.keepOpen = False

# button 2 will call a function
class Button2Events:    
    def OnDblClick(self, *args):
        print("button2 double clicked")
        myfunc("from Button 2")

#config win32com excel connection
xlApp = win32.gencache.EnsureDispatch('Excel.Application')
xlApp.Visible = True
xlWb = xlApp.Workbooks.Open(WORKBOOK)
xlWs = xlWb.Sheets(WORKSHEET)
# define button event callback class
xlButton1Events=win32.WithEvents(xlWs.OLEObjects("CommandButton1").Object,Button1Events)
xlButton2Events=win32.WithEvents(xlWs.OLEObjects("CommandButton2").Object,Button2Events)

# a while loop to wait until the button in excel is double-clicked
while xlButton1Events.keepOpen:
    pythoncom.PumpWaitingMessages()


print("Script finished - closing Excel")

xlWb.Close(False) # False: do not save on close, True: save on close
xlApp.Quit()