Cannot iterate VBA macros from Python

Question

I am using VBA in conjunction with Python.

I imported the module OS, and for working with excel - openpyxl. The problem occurs when it iterates the function for running the 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 that the error is in run_macro('Input.xlsm','macro1') - it cannot iterate.

The 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?

Nobody can solve it? It is very important to me - please, spend some time and help me - I am new in programming - and it is my first program — Emkan, Dec 19 '12 at 06:23
I am not using Python at the moment. But looking at the error, it seems like you have an error defining file name, can you try defineing the file path like this and see if it is working: `c:\fullpath\test.xls` Then you may try `DispatchEx` to make sure you always get a new instance of Excel. — bonCodigo, Dec 19 '12 at 07:49
I've seen this before, no Workbooks attribute of the xl app instance. But I can't remember what I did to fix it. Let me study a little . . . — MikeHunter, Dec 19 '12 at 20:20

MikeHunter · Answer 1 · 2012-12-19T23:34:13.727

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

import win32com.client

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

If that does not work, you can alway go back to late binding by hooking to Excel like this:

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

or by simply 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 the Python hooks to the Excel com servers were working correctly, then that line would not raise the exception that it did. I don't see anything wrong with the code where the exception occured. Sometimes early binding helps in situations like this.

good luck

Mike

Cannot iterate VBA macros from Python

1 Answers1

Linked