Ask HN: A Good Reference to MS Office Automation, Please

by ez77 on 5/15/2010, 12:59 AM with 1 comments

Hello! I know that the web is littered with questions on this matter, but I still haven't been able to apply the answers I can find to my situation. I hence kindly beseech HNers to share your wisdom on this rather outdated issue.

I realize there is VBA, but I always disliked having the program/macro living inside the (say) Excel file, with the resulting bloat, security warnings, etc. I'm thinking along the lines of a VBScript that works on a set of Excel files while leaving them macro-free. Now, I've been able to "paint the first column blue" for all files in a directory following this approach, but I need to do more complex operations (charts, pivot tables, etc.), which could greatly benefit from the additional tools found in VBA.

For this specific example knowing how to remove all macros from all files after processing would be enough, but all suggestions are welcome. Any good references? (+) Any advice on how to best approach external batch processing of Excel files and its Office cousins?

Thanks!

(+) Even if the reference is, "Ask in this other website: _____."

  • by fauxfauxpas on 5/16/2010, 9:36 PM

    http://oreilly.com/catalog/9781565926219 - Python Programming on Win32 has some good info and some stories on "batch processing" of office docs. Good case study on "Applied Biometrics" capturing of drug testing process. I've used the py/com stuff to do some of what you want - build xls files with "macros" for calculations, etc. You have the option of doing it in python or inserting the excel code into cells. You can also find other "excel" controls for python that create csv file versions of spreadsheets (so likely none of the macro capabilities) - same for perl, etc