Python win32com opening Excel with Bloomberg plugin

Question

I'm trying to automate construction of an Excel 2007 spreadsheet that uses the Bloomberg plugin to pull down live prices. The problem is that when I open Excel through win32com the Bloomberg plugin does not load (so all of the formulas end up with "#NAME?" errors).

Manually uninstalling and reinstalling the plugin works, but copying the VBA code from the recorded macro leads to a "Run-time error '13': Type mismatch" error. I can click the End button and everything runs fine, but I want to have this fully automated.

My code is:

import win32com.client
xl = win32com.client.gencache.EnsureDispatch("Excel.Application")
xl.Visible = True
MainWorkBook = xl.Workbooks.Add(1)
xl.AddIns("Bloomberg Excel Tools").Installed = False
xl.AddIns("Bloomberg Excel Tools").Installed = True

Setting DisplayAlerts = False doesn't catch the runtime error.

Answer 1

you have to open the bloomberg.xla file with something like:

from win32com.client import DispatchEx
xl = DispatchEx('Excel.Application')
xl.Workbooks.Open('C:/blp/API/Office Tools/BloombergUI.xla')

see here for more info: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.addin.aspx