Python win32com - modifying XLSM file, and running macros

Python Help
1

Hi,

I’m using the code below to modify a macro enabled excel file, and I need to run a macro, than modify some cells, then run another macro.
Unfortunatelly i’m stuck at the following error:

image
image1109×347 25.4 KB

Can you guys help me with this?
And please take it easy on me, i’m a VBA programmer, but just started learning Python. Also the file is open when I run the script.

#bash pip install openpyxl pywin32

import os
import win32com.client as win32

def isWorkbookOpen(xl, filePath):
    filePath = os.path.abspath(filePath)
    for wb in xl.Workbooks:
        if wb.FullName == filePath:
            return True
    return False

def runExcelMacro(xl, filePath, macroName):
    filePath = os.path.abspath(filePath)
    if not isWorkbookOpen(xl, filePath):
        xl.Workbooks.Open(filePath)

    try:
        xl.Application.Run(macroName)
    except Exception as e:
        print(f"Handled error: {e}")
    finally:
        if not isWorkbookOpen(xl, filePath):
            xl.Quit()
        else:
            xl.EnableEvents = True

def setNamedRange(xl, filePath, namedRangeName, value, isSetAsFormula=False, isPasteValue=False):
    filePath = os.path.abspath(filePath)
    if not isWorkbookOpen(xl, filePath):
        xl.Workbooks.Open(filePath)
    sheet = xl.ActiveWorkbook.Sheets("Country Inputs")

    # Get the named range and update its value
    named_range = xl.ActiveWorkbook.Names(namedRangeName)
    cell_range = named_range.RefersToRange

    if isSetAsFormula:
        cell_range.Formula = value
        if isPasteValue:
            cell_range.Copy()
            cell_range.PasteSpecial(Paste=-4163)  # PASTE VALUES
    else:
        cell_range.Value = value

def fillCountryInputs(xl, filePath):
    # RUN MACRO TO UNHIDE ALL COUNTRIES
    runExcelMacro(xl, filePath, "CI_Countries_Unhide")

    # FILL IN INPUTS
    setNamedRange(xl, filePath, "CI_country_list_check", True)
    setNamedRange(xl, filePath, "CI_num_sites_list", 10)
    setNamedRange(xl, filePath, "CI_num_patients", 100)

    # GET NUMBER OF LANGUAGES
    setNamedRange(xl, filePath, "CI_num_patients", "=countLanguages()", True, True)

    # RUN MACRO TO REBUILD FORMULAS
    runExcelMacro(xl, filePath, "CI_RebuildFormulasAfterAddingCountries")

def main():
    filePath = r"C:\Users\q850728\Desktop\@WORK\@POP\POP_v3.0.0_dev\POP_v3.0.0_Dev183_27Jul2023_BLANK-ddu_MAIN_PPT-2507.xlsm"
    xl = win32.Dispatch("Excel.Application")
    
    fillCountryInputs(xl, filePath)

    xl.ActiveWorkbook.Save()
    xl.Quit()

if __name__ == "__main__":
    main()