lundi 31 juillet 2017

Check Excel VBA Checkbox with Python 2.7

I am using Python 2.7 to attempt to check VBA checkboxes in an Excel document. I generally use pywin32 in order to do this, but these checkboxes seem to be purely VBA objects not tied to a cell. I have used the following script to attempt to read the names of the checkboxes so I can assign Value:

import win32com.client as win32
import os
import ctypes.wintypes


CSIDL_PERSONAL = 5       
SHGFP_TYPE_CURRENT = 0
buf= ctypes.create_unicode_buffer(ctypes.wintypes.MAX_PATH)
ctypes.windll.shell32.SHGetFolderPathW(None, CSIDL_PERSONAL, \
                                       None, SHGFP_TYPE_CURRENT, buf)
dirstart = buf.value
changedir = dirstart + '\Xcel Docs'
os.chdir(changedir)
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Interactive = False
excel.Visible = False
wb = excel.Workbooks.Open(r'\\dv-panzura\dv-home$\jswordy\Documents\Xcel 
Docs\Copy of Work Order InfoSheetEx - 2-16-16.xls')
ws = wb.Worksheets("WO Cover Sheet")
for cb in ws.CheckBoxes():
    print cb.Name

Unfortunately this returns an error only:

Traceback (most recent call last): File "C:\Python27\ArcGIS10.3\Scripts\infosheet.pyw", line 18, in ws = wb.Worksheets("WO Cover Sheet") File "C:\Users\jswordy\AppData\Local\Temp\gen_py\2.7\00020813-0000-0000-C000-000000000046x0x1x9\Sheets.py", line 120, in call ret = self.oleobj.InvokeTypes(0, LCID, 2, (9, 0), ((12, 1),),Index com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)

Which seems to indicate it cannot locate these items (probably because they are not linked to cells). Would anyone have any idea how to check these checkboxs? Perhaps editing the VBA to have the objects checked? Any ideas would be appreciated.




Aucun commentaire:

Enregistrer un commentaire