CODEWITHSUNDEEP
python-3.x
Oliver Wu
Oliver Wu

Reputation: 145

Couldn't execute Excel Macro using Python

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")

enter image description here

Upvotes: 1

Views: 408

Answers (1)

Oliver Wu
Oliver Wu

Reputation: 145

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]))

Upvotes: 1

Related Questions