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.

Answer 1

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.