COM issue with Bloomberg Add-in not being imported

[KB]

Hi there (again),

If this is the wrong forum for COM/Excel questions please redirect me.
I'm assuming if this isn't the place then perhaps the general python
forum would be appropriate, but since it is to do with python and
Excel thought I should try here first.

I am trying to instantiate a simple Bloomberg formula into a
spreadsheet. The code runs, saves the file, and if I open the file
manually, the formula executes as one would expect.

The issue comes from when I try and access the data from within python
after the formula is entered via python. It appears from using
Visible=1 that Bloomberg is not being loaded. (normally there is a
Bloomberg tab in Excel, and when I run with Visible=1 this tab is
missing and the formula returns the dreaded #NAME).

Here is the code:

***********************
import win32com.client

xlApp = win32com.client.Dispatch("Excel.Application")
xlApp.Visible=1
xlApp.DisplayAlerts=False
xlApp.Workbooks.Add()
cell = xlApp.Worksheets("Sheet1")
cell.Range("A1").Value = '=BDS("MSFT US Equity","ERN_ANN_DT_AND_PER")'

print "Entered formula..."
#xlApp.ActiveWorkbook.Save()

start_row=1
start_col=1

my_range=cell.Range(cell.Cells(start_row,start_col),cell.Cells(start_row
+20,start_col+1))

print my_range # <--- *** THIS RETURNS THE EXCEL CODE I HAVE SEEN FOR
#NAME (-2144XXXX) AND THEN EMPTY CELLS.

filename = r'C:\Users\Me\Desktop\mySpreadSheet2.xlsx'
xlApp.ActiveWorkbook.SaveAs(filename)

#Note these commented out so I could visually inspect the file during
execution.
#xlApp.ActiveWorkbook.Close(SaveChanges=1)
#xlApp.Quit()
*************************
Any ideas on how to get the Bloomberg Add-in to be included when
python launches Excel.

Note when I run this python script, and if I un-comment the Close and
Quit commands, I can then open the file in Excel manually and the
results are what I expected (data from Bloomberg and the tab from
Bloomberg is there). I am using the same userid to launch the script
and to manually launch excel.

Is there something I need to do to set up python to have Excel run
under my "settings" ?

TIA.

[Chris Withers]

KB wrote:
> I am trying to instantiate a simple Bloomberg formula into a
> spreadsheet. The code runs, saves the file, and if I open the file
> manually, the formula executes as one would expect.

Sounds like something to ask Bloomberg support...

It might also be worth asking on the python win32 list..

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[KB]

> > I am trying to instantiate a simple Bloomberg formula into a
> > spreadsheet. The code runs, saves the file, and if I open the file
> > manually, the formula executes as one would expect.
>
> Sounds like something to ask Bloomberg support...
>
> It might also be worth asking on the python win32 list..
>

Yes, it was indeed more of a python win32 problem. In case someone
else comes here looking for the same issue, here is how I fixed it.

After launching the application (Dispatch) I had to run the XLA for
Bloomberg....

xlApp.Workbooks.Open(r"C:\blp\API\Office Tools
\BloombergUI.xla").RunAutoMacros(1)

Everything then works as intended (note: you do need a time.sleep(xx)
to wait for network lag, but 10seconds is more than generous).

I only include the above for completeness, since it REALLY wasn't a
Bloomberg problem, but in retrospect, Chris, you are exactly right, I
will also post my findings on win32.