This is what I ended up with:
# # excel_info.py # Find worksheets, defined names and CheckBox1 value in Excel WorkBooks. # Loop thru directory and write each WorkBook's name, Last Modification Date, # CheckBox1 value (to see if uncertainties are calculated), Total # of # WorkSheets, the names of all Worksheets, Total # of Defined Names,and a # list of all Defined Names into CSV # # In the CSV file: # Column 1 = WorkBook name # Column 2 = Last Modified Date # Column 3 = CheckBox1 value # Column 4 = Total # of WorkSheets in WorkBook # Column 5 = All Worksheet names # Column 6 = Total # of Defined Names in WorkBook # Column 7 = All WorkBook Defined Names #
import win32com.client as win32 import os import glob import csv import datetime from collections import Counter wb_def_names = Counter() ws_names = Counter() os.chdir('C:\origin') xlfiles = sorted(glob.glob("*.xls")) outputFile = open('output.csv', 'w', newline='') outputWriter = csv.writer(outputFile) xlApp = win32.gencache.EnsureDispatch('Excel.Application') xlApp.EnableEvents = False #xlApp.ScreenUpdating = False cwd = os.getcwd() this_file = 0 for xlfile in xlfiles: showunc_val = 0 unccalc_val = bool(False) showunc = 0 found = 0 checkbox1_caption = "None" this_file = this_file + 1 if this_file%100 == 0: print("Processing ",this_file) if this_file >= 1: open_ok = 1 try: xlApp.DisplayAlerts = False xlwb = xlApp.Workbooks.Open(cwd+"\\"+ xlfile) except: open_ok = -1 print("error in open xlfile") try: xlwb_name = xlApp.ActiveWorkbook.Name except: open_ok = -1 print("error in open ActiveWorkbook.Name") if open_ok: cntr = 1 try: for n in xlApp.ActiveWorkbook.Names: wb_def_names[(n.Name)] += cntr found = (str.find(n.Name, "showunc")) found2 = (str.find(n.Name, "unccalc")) if found == 0 or found2 == 0: this_n = str(n) dummy = this_n.split("!") dummy2 = str(dummy).replace("=", "") dummy2 = str(dummy2).replace("'", "") if found == 0: dummy3 = str(dummy) showunc_val = xlApp.Sheets(dummy2).Range(dummy3).Value if found2 == 0: checkbox1_caption = xlApp.Sheets(dummy2).OLEObjects("CheckBox1").Object.Caption unccalc_val = xlApp.Sheets(dummy2).OLEObjects("CheckBox1").Object.Value cntr = cntr + 1 list_wb_def_names = [v for v in wb_def_names.keys()] except: open_ok = -1 print("error in ActiveWorkbook.Names",n,n.Name,cntr) cntr2 = 1 try: for s in xlApp.ActiveWorkbook.Sheets: ws_names[(s.Name)] +=cntr2 cntr2 = cntr2 + 1 list_ws_names = [v for v in ws_names.keys()] except: open_ok = -1 try: xlApp.ActiveWorkbook.Saved = True xlApp.ActiveWorkbook.Close() except: open_ok = -1 print("error in closing workbook") try: last_modified_date = datetime.datetime.fromtimestamp(os.path.getctime(cwd+"\\"+ xlfile)) except OSError: last_modified_date = 0 last_modified_date = last_modified_date.strftime("%d-%m-%Y") try: outputWriter.writerow([xlwb_name, last_modified_date,unccalc_val,cntr2-1,list_ws_names,cntr-1,list_wb_def_names]) except: print("error writing to outputfile") print("Workbook = ", xlwb_name) wb_def_names.clear() ws_names.clear() checkbox1_caption = "None" else: print("Unable to process ",xlfile) else: dummy5 = 0 #assert isinstance(xlApp.Application, object) #xlApp.Application.Quit() outputFile.close()
I found that everything worked better if Excel was already open before I ran the program - not sure why. I'm retrieving the value for CheckBox1 and not validating it only because I did when testing the code and using the CheckBox Caption property showed that - in my instance - they were all what I thought they were - thank you Dennis Green :)
It doesn't work flawlessly. During runtime I get a quite a few (2 or 3% ??) of the files reporting "error in ActiveWorkbook.Names " from one of my error handlers. It doesn't seem to affect the output, so I haven't bothered to figure out why some files generate the error. I will eventually store just the filenames that create the error and see why.
A more significant error is that certain poorly written Excel macros in the WorkBook generate errors on opening the WorkBook. As I suspected when writing the previous post, moving the statement xlApp.DisplayAlerts=False helped a lot. I added xlApp.EnableEvents=False to try and stop macros from causing a problem, but it too may need to be moved inside the main loop to be effective.
All in all, I know my code isn't up to 'professional' standards, but it works, does what I set out for it to do, and gives me enough knowledge to use it for many other similar projects. Eventually, if I use Python often enough, I'll start building class libraries and user-defined functions, but I'm happy with the results as it is.