Skip to content

Instantly share code, notes, and snippets.

@wichopy
Created March 19, 2017 15:28
Show Gist options
  • Save wichopy/db1c6289a719839dc107266fad8fca00 to your computer and use it in GitHub Desktop.
Save wichopy/db1c6289a719839dc107266fad8fca00 to your computer and use it in GitHub Desktop.
Script to extract data from excel checkboxes.
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
# -*- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment