python 操作excel 调用vb_使用win32com.client编写并执行excel VB宏

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:

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: '' 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.

解决方案

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.