wilmott.com

Does anyone know how to solve this problem :I have several Excel files which contain hundreds of links to Bloomberg like “=blp(Ticker, Field)”.If I open one of the files and then close it, open it again on the same Excel and then close it again, Excel crashes.The problem happens whether this is done on a Bloomberg terminal or not. Several of my colleagues also have this problem.Thanks a lot

In order to have not this problem , you can open (before your files) the files of demonstration of Bloombergs.I hope this might help you

Thanks for your answer battantI’ve already tried that, and it’s true that that’s one way around the problem. The thing is, it takes just as long to open the demo file as it does to close your Excel and open a new one .If you know of some other possibility please let me know.

Here's an example file, if anyone can figure it out...

GallagYour sheet works for me. Are you sure you have the right version? In your Bloomberg terminal typeUPGR<go>this will tell whether you have up to date API versions.IIn the past I have also found that killing the bbcomm process can help.Mark

Thanks Mark,I killed the bbcomm process and checked UPGR. Everything is "Up To Date".I'm running Excel 2000.I still have the problem when I close the file for the second time on a same Excel (whether I'm on a BB terminal or just on standard computer without BB).However, I've just noticed something new: the crashing only happens on old Excel files (i.e. files that were built some time ago). If I build a new file with exactly the same formulas as the one attached earlier, the file doesn't crash...

What you may be experiencing is an artifact of the changes to way Excel calculates sheets.You may find that if you rename the sheets in your workbook, the problem just goes away.

A while ago I experienced the same problem. As DCFC suggests, Excel's calculation order seemed to be the cause. It seems that Excel tries to build its dependency graph starting with Worksheets in the alphabetic order of their names. When there are a lot of DDE links (like calls to =blp(...)) and when the dependency graph among sheets is cyclic, Excel overflows its internal stack and crashes. My fix (so far) was to rename and reorder the sheets, and make sure the sheet dependencies in one direction (Config -> Feed -> Pricer).I hope this helps.Jim

DCFC, Jim,Thanks a lot for your answers.The thing is there's only one sheet in the workbook. I've tried renaming it but I still get the same problem.Here's the error message that I get when I shut the file for the second time... it might give someone an idea.

I notice the sheet displayed is labelled "Sheet11" Are you sure there aren't any hidden sheets?

That was because I tried out playing about with the sheet's name before posting it. Unfortunately, there has only ever been one sheet in the file…

gallag,I faced the same problem some time ago. In fact, the problem occured on many users machines within the same organization. In our case, it turns out that the problem was caused by one or more old Excel file having been used as a source template for new files (like a template). When viewing the Excel files as text in Notepad - we came accross some very old Reuters' links in plain text. These were not visible in Excel, but were obviously still saved within the file structure itself and this structure was propagated to new files everytime a new file was created from an old one.In our case, the solution was to save our old file to Excel 95 format, renamed, then back to Excel 2002 format. This would purge the old link.You may also want to check out willow solution's forum, I remember some similar problem being discussed there.

I had this problem once, too. Solved it by creating a macro that calculates the bloomberg values directly by calling the bloomberg subscribe function (or something like that) and passing in the securities and fields as arrays. It's much more efficient, and actually a _lot_ faster. The analysts also liked it because they could control exactly when the data updated from bloomberg by pressing the update button. We dedicated a page to statistics about the update, it had a timestamp at the top that showed the last time the data in the workbook was updated. You can also try to use a timer to launch the update automatically at certain times, but if you do it from excel you have to insure that the workbook is open.The downside of all of this is that you have write something to keep track of what cells you are going to query from bloomberg. -t.

PinballWizard, You put your finger right on the problem. Amazing. When saving the file in “Excel 5.0/95” format and then putting it back to “Excel 97-2000 & 5.0/95”, the crashing disappears!!!! I do work with both Reuters and BB so there may have be some sort of hidden Reuters links in there. Also, most of my files are just copies of other source/template files.Tristanreid, good advice. I’m currently in the process of migrating most of my Excel files with blp links in them to ActiveX/BLP_DATA_CTRLLib.BlpData links. I’m still testing, but as you say, the gain in speed is impressive.Thanks everyone