I am trying to work with Excel Workbooks in Python using xlwings. I would like to create a GUI where users can choose what they want to do by clicking a button. For example, Load an Additional Column, or something like that. The idea is:
Open a large xlsx file via xlwings. Load data into a dataframe. Allow users to do something (Add Columns / Sum Columns / etc.) - it should be done on the dataframe. Save the updated dataframe to the opened xlsx file. I would like to give users the opportunity to change values between choosing options (Add Columns / Sum Columns / etc.), but it is problematic because if I want to be sure that the dataframe and the sheet are up to date, I have to load a lot of data from the sheet to the dataframe every single time when the user chooses any option.
It takes a lot of time, so I would like to optimize the process by checking when the sheet was last edited by a user and comparing this datetime with the datetime of the pasted dataframe. However, I can't find an easy way to send the datetime of when the sheet is edited to Python each time an edit occurs. I found a solution in VBA that allows me to save the edit datetime in an additional sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim infoSheet As Worksheet
Set infoSheet = ThisWorkbook.Sheets("INFO")
Dim currentDateTime As String
currentDateTime = Format(Now, "yyyy-mm-dd hh:mm:ss AM/PM")
infoSheet.Range("A1").Value = currentDateTime
End Sub
This saves the datetime every single time I edit Sheet1, but I am looking for a solution that does not involve VBA.