Running an Excel macro via Python

Question

I m trying to run a macro via python but I m not sure how to get it working     I ve got the following code so far  but it s not working   import win32com client xl win32com client Dispatch  Excel Application   xl Workbooks Open Filename  C  test xlsm  ReadOnly 1  xl Application Run  macrohere   xl Workbooks 1  Close SaveChanges 0  xl Application Quit   xl 0   I get the following traceback   Traceback  most recent call last     File  C  test py   line 4  in  lt module gt      xl Application Run  macrohere     File   lt COMObject  lt unknown gt  gt    line 14  in Run   File  C  Python27 lib site-packages win32com client dynamic py   line 282  in  ApplyTypes      result   self  oleobj  InvokeTypes   dispid  LCID  wFlags  retType  argTypes    args  com error   -2147352567   Exception occurred     0  u Microsoft Excel   u Cannot run the macro  macrohere   The macro may not be available in this workbook or all macros may be disabled    u xlmain11 chm   0  -2146827284   None    EDIT  import win32com client xl win32com client Dispatch  Excel Application   xl Workbooks Open Filename  C  test xlsm  ReadOnly 1  try      xl Application Run  test xlsm testmacro testmacro         It does run like this    but we get the following error        Traceback  most recent call last             File  C  test py   line 7  in  lt module gt            xl Workbooks 1  Close SaveChanges 0            File  C  Python27 lib site-packages win32com client dynamic py   line 192  in   call             return self  get good object  self  oleobj  Invoke  allArgs  self  olerepr  defaultDispatchName None        com error   -2147352567   Exception occurred     0  None  None  None  0  -2147352565   None  except        Except isn t catching the above error           xl Workbooks 1  Close SaveChanges 0      xl Application Quit       xl 0

User · Answer

I tried the win32com way and xlwings way but I didn't get any luck. I use PyCharm and didn't see the .WorkBook option in the autocompletion for win32com. I got the -2147352567 error when I tried to pass a workbook as variable.

Then, I found a work around using vba shell to run my Python script. Write something on the XLS file you are working with when everything is done. So that Excel knows that it's time to run the VBA macro.

But the vba Application.wait function will take up 100% cpu which is wierd. Some people said that using the windows Sleep function would fix it.

 Import xlsxwriter


 Shell "C:\xxxxx\python.exe 
 C:/Users/xxxxx/pythonscript.py"

 exitLoop = 0

wait for Python to finish its work.

  Do

  waitTime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 30)
  Application.Wait waitTime
  Set wb2 = Workbooks.Open("D:\xxxxx.xlsx", ReadOnly:=True)
  exitLoop = wb2.Worksheets("blablabla").Cells(50, 1)
  wb2.Close exitLoop

  Loop While exitLoop <> 1




  Call VbaScript

User · Answer

A variation on SMNALLY s code that doesn t quit Excel if you already have it open  import os  os path import win32com client      if os path exists  quot excelsheet xlsm quot        xl win32com client Dispatch  quot Excel Application quot       wb   xl Workbooks Open os path abspath  quot excelsheet xlsm quot    ReadOnly 1   create a workbook object     xl Application Run  quot excelsheet xlsm modulename macroname quot       wb Close False   close the work sheet object rather than quitting excel     del wb     del xl

User · Answer

Just a quick note with a xlsm with spaces    file    file with spaces xlsm  excel macro Application Run        file            Module1 Macro1

User · Answer

I would expect the error is to do with the macro you re calling  try the following bit of code   Code  import os  os path import win32com client  if os path exists  excelsheet xlsm        xl win32com client Dispatch  Excel Application       xl Workbooks Open os path abspath  excelsheet xlsm    ReadOnly 1      xl Application Run  excelsheet xlsm modulename macroname         xl Application Save     if you want to save then uncomment this line and change delete the    ReadOnly 1  part from the open function      xl Application Quit     Comment this out if your excel script closes     del xl

User · Answer

I suspect you haven t authorize your Excel installation to run macro from an automated Excel  It is a security protection by default at installation  To change this    File   Options   Trust Center Click on Trust Center Settings    button Macro Settings   Check Enable all macros

User · Answer

I did some modification to the SMNALLY s code so it can run in Python 3 5 2  This is my result     Import the following library to make use of the DispatchEx to run the macro import win32com client as wincl  def runMacro         if os path exists  C   Users  Dev  Desktop  Development  completed apps  My Macr Generates Data xlsm           DispatchEx is required in the newest versions of Python      excel macro   wincl DispatchEx  Excel application       excel path   os path expanduser  C   Users  Dev  Desktop  Development  completed apps  My Macr Generates Data xlsm       workbook   excel macro Workbooks Open Filename   excel path  ReadOnly  1      excel macro Application Run            ThisWorkbook Template2G        Save the results in case you have generated data     workbook Save       excel macro Application Quit         del excel macro

User · Answer

For Python 3 7 or later  2018-10-10   I have to combine both  Alejandro BR and SMNALLY s answer  coz  Alejandro forget to define wincl   import os  os path import win32com client if os path exists  C  Users jz Desktop test xlsm        excel macro   win32com client DispatchEx  Excel Application     DispatchEx is required in the newest versions of Python      excel path   os path expanduser  C  Users jz Desktop test xlsm       workbook   excel macro Workbooks Open Filename   excel path  ReadOnly  1      excel macro Application Run  test xlsm Module1 Macro1     update Module1 with your module  Macro1 with your macro     workbook Save       excel macro Application Quit         del excel macro

User · Answer

Hmm i was having some trouble with that part  yes still xD    xl Application Run  excelsheet xlsm macroname macroname    cos im not using excel often  same with vb or macros  but i need it to use femap with python  so i finaly resolved it checking macro list   Developer -  Macros  there i saw that  this macroname macroname should be sheet name macroname like in  Macros  list    i spend something like 30min-1h trying to solve it  so it may be helpful for noobs like me in excel  xD

[python] Running an Excel macro via Python?

Examples related to python

Examples related to excel

Examples related to vba

Examples related to python-2.7