4

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 randomfrom openpyxl import load_workbookimport os, os.path, win32com.clientwbi = 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 = Nonedef IBP():    ibp = wsi.cell('G12')    ibpv = random.randint(0,45)    ibp.value = ibpv    return ibp.valuedef BP10():    bp10 = wsi.cell('G13')    bpv10 = random.randint(30,50)    bp10.value = bpv10    return bp10.valuefor n in range(6):    IBP()    print IBP()    BP10()    run_macro('Input.xlsm','macro1')    wbo.save('Output.xlsx')

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

The output:

Qt: Untested Windows version 6.2 detected!354Traceback (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?

askedDec 18, 2012 at 21:08
Emkan's user avatar
4
  • 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 programCommentedDec 19, 2012 at 6: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 tryDispatchEx to make sure you always get a new instance of Excel.CommentedDec 19, 2012 at 7:49
  • Thanks for helping me - but it did not workCommentedDec 19, 2012 at 16:55
  • 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 . . .CommentedDec 19, 2012 at 20:20

1 Answer1

1

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

import win32com.clientxl = 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 linewb = 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

answeredDec 19, 2012 at 20:40
MikeHunter's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.