Automating Excel with Python
As well as using Python to interact with the filesystem, it’s possible to use COM automation to open Excel and access its functionality. I decided to take a look at Mark Hammond’s PythonWin IDE, which contains the win32com.client package to provide a wrapper for the Excel automation object. For example, the function newest from part 1 could be implemented as follows:
# OpenNewest.py - opens latest Excel file import os, os.path, win32com.client def newest(ext, path=os.getcwd()): '''pre: path a valid absolute filepath (optional), ext a file extension (e.g. '.xls') returns: name of the newest (last modified on *nix systems, last created on Windows) file with extension ext from path ''' file_dict = dict([(fname, \ os.path.getctime(os.path.join(path, fname))) \ for fname in os.listdir(path) \ if os.path.splitext(fname) == ext]) return os.path.join(path, \ max(file_dict, key=lambda x: file_dict[x])) if __name__ == __main__: xlApp = win32com.client.Dispatch("Excel.Application") lastFileName = newest('.xls') lastFile = xlApp.Workbooks.Open(lastFileName) # Do some stuff with lastFile... lastFile.Close() xlApp.Quit() xlApp = None
So, what stuff can you do? One thing that would be nice is to be able to open an Excel file and run a macro in it. I decided to test this out by saving a file named ‘PythonTest.xls’, containing the macro ‘helloFromPython’:
Public Sub helloFromPython() Sheet1.Range("B1:B100").Value = "Hello from Python!" End Sub
The script RunInExcel.py then creates an Excel automation object, which opens the file and calls the macro in it:
# RunInExcel.py - opens named Excel file fName, runs named macro # macName import os, os.path, win32com.client def run_macro(fName, macName, path=os.getcwd()): """ pre: fName is the name of a valid Excel file with macro macName post: fName!macName is run, fName saved and closed """ fName = os.path.join(path, fName) xlApp = win32com.client.Dispatch("Excel.Application") fTest = xlApp.Workbooks.Open(fName) macName = fTest.Name + '!' + macName xlApp.Run(macName) fTest.Close(1) xlApp.Quit() xlApp = None if __name__ == "__main__": run_macro("PythonTest.xls", "helloFromPython")
Run the script, open the file and voila!
What I’m thinking would be really nice is to be able to automate a lot of our regular reporting by doing this – a single script could be run to kick off a batch of reports. I wonder, though, about how efficient using Python to do this would be as opposed to using a VBA/VBScript routine to do the same thing…does anyone else use Python for this sort of task?