why couldn't I use the script below to run a Macro by Python? Any suggestions would be appreciated.
import xlwings as xw
wb= xw.Book('C:\\myfolder\\path\\myFileName.xlsm')
a = wb.macro("z_ColumnClear.Column_Clear")
Add a comment
|
1 Answer
The idea is from this link. https://www.excelcise.org/running-excel-vba-from-python/
and this link provides several scenario from users when the script don't work. Cannot run the macro... the macro may not be available in this workbook
import os
import win32com.client as win32
def run_excel_macro (file_path_no_extention, VBA_macro_name, VBA_procedure_name):
"""
Execute an Excel macro
:param file_path: path to the Excel file holding the macro
:param VBA_module_name: VBA name
:param VBA_procedure_name: name in the sub routine
:param separator_char: the character used by the operating system to separate pathname components
:return: None
"""
xl = win32.Dispatch('Excel.Application')
xl.Application.visible = False
# os.sep is \
try:
wb = xl.Workbooks.Open(os.path.abspath(file_path_no_extention))
xl.Application.run("'" + file_path_no_extention.split(sep=os.sep)[-1] + "'" + "!" + VBA_macro_name + "." + VBA_procedure_name)
wb.Save()
wb.Close()
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)
xl.Application.Quit()
del xl
print("Completed: " + str(file_path_no_extention.split(sep=os.sep)[-1]))
Comments
