Why I’d like Python at work, part 2

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)[1] == 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?