Write and execute excel VB macro with win32com.client

Question

I am attempting to write a script that opens an existing .xlsx file, writes a Visual Basic macro script to auto-size comments, executes said macro, then closes and saves the workbook.

I am quite unfamiliar with win32com.client, and after several hours of digging I have not found good documentation for writing VB macro scripts from python. Therefore, I stitched together a script using feedback from these threads:

https://stackoverflow.com/a/19506287/7547876

https://stackoverflow.com/a/2141981/7547876

Here is a rough representation of the code I've come up with:

import openpyxl, win32com.client as win32, comtypes, comtypes.client

class report:
    def __init__(self,name,dpath,inputs,**kw):
        self.name=name
        self.dpath=dpath
        #ommited scripts builds excel report with openpyxl, then saves it

        self.xl=win32.gencache.EnsureDispatch('Excel.Application')
        self.xl.Visible=False

        self.report=self.xl.Workbooks.Open(dpath+'\\'+self.name+'.xlsx')
        self.report.Worksheets("Audit Assistant Report").Activate()

        self.sheet=self.report.ActiveSheet

        self.xlmodule=self.sheet.VBProject.VBComponents.Add(1)

        self.excelcode="""Sub FitComments()
'Updateby20140325
Dim xComment As Comment
For Each xComment In Application.ActiveSheet.Comments
xComment.Shape.TextFrame.AutoSize = True
Next
End Sub"""

        self.xlmodule.CodeModule.AddFromString(self.excelcode)
        self.report.Run(self.name+'.xlsx!Macro_1')
        self.report.Close(savechanges=True)
        self.xl.Quit()

def main():
    #input definitions omitted
    report("myreport","C:\\somepath\\",inputs)

if__name__=='__main__':
    main()

When I try to run the script, it produces the following traceback:

Traceback (most recent call last):
  File "C:\Python27\lib\site-packages\win32com\client\__init__.py", line 473, in __getattr__
    raise AttributeError("'%s' object has no attribute '%s'" % (repr(self), attr))
AttributeError: '<win32com.gen_py.Microsoft Excel 14.0 Object Library._Worksheet instance at 0x229316888>' object has no attribute 'VBProject'

I've tried updating my PyWin32 package, and determined that was not the issue.

What would need to change in the script to get it to execute, and produce the intended effect?

Thank you in advance for your time and input.

score 2 · Accepted Answer · answered Apr 03 '17 at 22:36

2

The Sheet object does not have a VBProject attribute. It's at the workbook level, so use this:

self.xlmodule=self.report.VBProject.VBComponents.Add(1)

Hope that helps.

answered Apr 03 '17 at 22:36

xidgel

3,085
2
13
22

That did fix the issue. Now I just need to figure out why its doing this :\ com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u'Programmatic access to Visual Basic Project is not trusted\n', u'xlmain11.chm', 0, -2146827284), None) – Allan B Apr 04 '17 at 14:24
2

For security reasons Excel by default does not permit software to create VBA modules. You need to change the default setting. In Excel 2010, you go to the `Developer` tab, click the `Macro Security` icon, the check the `Trust access to the VBA project object model` check box. Should be something similar in other versions. – xidgel Apr 04 '17 at 15:51
Much appreciated. My program now writes properly sized comments, and offers a message box suggesting that the user should follow those steps if the VBA script fails. – Allan B Apr 04 '17 at 16:29

Write and execute excel VB macro with win32com.client

1 Answers1