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?


Answer: 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

