Unable to iterate over VBA macros from Python

Question

I use VBA in conjunction with Python.

I imported the OS module and for working with excel - openpyxl. The problem occurs when it iterates over a function to run a VBA macro from Excel.

import random from openpyxl import load_workbook import os, os.path, win32com.client wbi = load_workbook('Input.xlsm') wsi = wbi.get_active_sheet() wbo = load_workbook('Output.xlsx') wso = wbo.get_active_sheet() def run_macro(fName, macName, path=os.getcwd()): """ pre: fName is the name of a valid Excel file with macro macName post: fName!macName is run, fName saved and closed """ fName = os.path.join(path, fName) xlApp = win32com.client.Dispatch("Excel.Application") fTest = xlApp.Workbooks.Open(fName) macName = fTest.Name + '!' + macName xlApp.Run(macName) fTest.Close(1) xlApp.Quit() xlApp = None def IBP(): ibp = wsi.cell('G12') ibpv = random.randint(0,45) ibp.value = ibpv return ibp.value def BP10(): bp10 = wsi.cell('G13') bpv10 = random.randint(30,50) bp10.value = bpv10 return bp10.value for n in range(6): IBP() print IBP() BP10() run_macro('Input.xlsm','macro1') wbo.save('Output.xlsx') 

I think the error is in run_macro('Input.xlsm','macro1') - it cannot iterate.

Output:

 Qt: Untested Windows version 6.2 detected! 35 4 Traceback (most recent call last): File "C:\Users\User\Desktop\Python Exp\Pr 1.py", line 77, in <module> run_macro('Input.xlsm','macro1') File "C:\Users\User\Desktop\Python Exp\Pr 1.py", line 18, in run_macro fTest = xlApp.Workbooks.Open(fName) File "C:\Python27\lib\site-packages\win32com\client\dynamic.py", line 522, in __getattr__ raise AttributeError("%s.%s" % (self._username_, attr)) AttributeError: Excel.Application.Workbooks 

What am I doing wrong?

Answer 1

I'm not sure if this will help, but you can try early binding. Run this script and then try again:

 import win32com.client xl = win32com.client.gencache.EnsureDispatch ("Excel.Application") print xl.__module__ 

If this does not work, you can return to late binding by connecting to Excel as follows:

 xl = win32com.client.dynamic.Dispatch("Excel.Application") 

or simply by deleting this folder: C:\Python27\Lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x7

From the error message, it looks like your problem is on the line wb = xlApp.Workbooks.Open(fname) . If Python intercepts Excel com servers, they are working correctly, then this line will not throw an exception. I see nothing wrong with the code where the exception occurred. Sometimes early binding helps in such situations.

luck

Mike