Script to extract data from excel checkboxes.

checkbox_extract_script.bas

Sub CheckboxLoop()
'Using this as a start:
'PURPOSE: Loop through each Form Control Checkbox on the ActiveSheet
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


Dim cb As Shape
Dim i As Integer



For j = 1 To ActiveWorkbook.Worksheets.Count
    i = 1 
  For Each cb In ThisWorkbook.Worksheets(j).Shapes
    If cb.Type = msoFormControl Then
      If cb.FormControlType = xlCheckBox Then
        If cb.ControlFormat.Value = 1 Then
          ThisWorkbook.Worksheets(j).Cells(i, 13) = cb.Name
          ThisWorkbook.Worksheets(j).Cells(i, 12) = "YYes"
          ThisWorkbook.Worksheets(j).Cells(i, 14) = cb.TopLeftCell.Row
          ThisWorkbook.Worksheets(j).Cells(i, 15) = cb.TopLeftCell.Column
          ThisWorkbook.Worksheets(j).Cells(i, 16) = j
          ThisWorkbook.Worksheets(j).Cells(i, 17) = ThisWorkbook.Worksheets(j).Name
          i = i + 1
        ElseIf cb.ControlFormat.Value = -4146 Then
            ThisWorkbook.Worksheets(j).Cells(i, 13) = cb.Name
            ThisWorkbook.Worksheets(j).Cells(i, 12) = "No"
            ThisWorkbook.Worksheets(j).Cells(i, 14) = cb.TopLeftCell.Row
            ThisWorkbook.Worksheets(j).Cells(i, 15) = cb.TopLeftCell.Column
          i = i + 1
          
        End If
      End If
    End If
  Next cb
Next j
End Sub

checkbox_scan.py

# -*- coding: utf-8 -*-
"""
Created on Tue Jan 31 20:24:28 2017
This script is a great culmunation of all the python and VBA scripts I;ve had to write.
The problem:
    I need to extract data from Excel checkboxes
    Only way to do this is with VBA, could not find a python library that had support for this.
Solution:
    Create a VBA script that would tell me if a checkbox is ticked or not.
    Relate each of these boxes with the row and column to identify its meaning in the spreadsheet.
    Use python to Open excel, open 1000+ spreadsheets, 
    inject the VBA code, 
    run the VBA code, 
    extract the data and store in a Python,
    Export data to .xlsx or .vba
    
This seems counter intutive to go back and forth like this, but I want to write as little VBA as possible.
SETUP EXCEL TO ACCEPT EXTERNAL MACROS
FILE>OPTIONS>TRUST CENTER>TRUST CENTER SETTINGS>MACROSETTINGS> ENable all macros and trust access to VBA project object model.
to turn off external links proompt:
http://stackoverflow.com/questions/14908372/how-to-suppress-update-links-warning
@author: chouw
"""
#Dictioaries of conditions and dsecriptions
condition = {6 : "Good",
             5 : "Fair",
             4: "Poor",
             7: "Photos",
             8: "NA"}

description = {9 : "Desk Chair",
               10: "Guest Chair",
               11: "Conference Chair",
               12: "Lobby Chair",
               13: "Stool",
               14: "Desk",
               15: "File",
               16: "Workstation",
               17: "Table",
               18: "Miscellaneous",
               19: "blank",
               20: "blank"
               }
with open ('vbascript.txt', 'r') as myscript:
    macro = myscript.read()
#print condition[6], description[9]
thesweetdatas = []
errors = []

# INJECT AND RUN VBA CODE
# http://stackoverflow.com/questions/19616205/running-an-excel-macro-via-python
#https://excelicious.wordpress.com/2009/05/09/why-id-like-python-at-work-part-2/
#http://pythonexcels.com/python-excel-mini-cookbook/
def vbarun(filepath):
    import os
    import win32com.client

    xl=win32com.client.Dispatch("Excel.Application")
    xl.Visible = False #if you want to show excel opening.
    xl.DisplayAlerts = False
    progress = ""
    wb = xl.Workbooks.Open(filepath)
    progress = "loaded to excel"
    head,tail = os.path.split(filepath)

    try:
        newModule = wb.VBProject.VBComponents.Add(1)
        progress = "added new module"
        newModule.CodeModule.AddFromString(macro)
        progress = "injected VBA code"
        xl.Application.Run("Module1.CheckboxLoop")
        progress = "ran macro"
        for values in wb.Worksheets("Furn Assessment").Range("L1:O60").Value:
            #print values
            row = []
            count = 0
            for cell in values:
                if count == 3:
                    stuff = condition[int(cell)]
                    row.append(stuff)
                elif count == 2:
                    stuff = description[int(cell)]
                    row.append(stuff)
                else: 
                    
                    row.append(cell)
                count += 1
                progress = "loaded values {} from excel".format(count)
            row.append(tail)
            thesweetdatas.append(row)
    except:
        errors.append([tail,progress])
    #xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
    xl.Application.Quit() # Comment this out if your excel script closes
    del xl
    del wb
    #del newModule
import os

for thisdir,subdir,flist in os.walk('.'):
    #print thisdir
    #print flist
    for fname in flist:
        print fname
        if fname.endswith('.xlsx') and fname[:2] != '~$':
            cur_file = os.path.join(os.getcwd(),fname)
            print "running on {}".format(cur_file)
            vbarun(cur_file)
#%%
import csv
with open('output.csv','wb') as results:
    wr = csv.writer(results, dialect='excel')
    wr.writerows(thesweetdatas)
with open('errors.csv','wb') as errorscsv:
    wr = csv.writer(errorscsv, dialect='excel')
    wr.writerows(errors)