Name errors with excel addin loaded through VBScript

[solitude]

I'm trying to run some excel macros through VBScript. The code also uses Bloomberg addin. Now, I know that VBScript by default switches addins off so in the script I have lines that add Bloomberg addin.

Problem is - while debugging, I get 'NAME' errors in cells that use Bloomberg functions. Though when I go to each cell and do "F2+Enter", it works and I get the desired values. The Bloomberg functions even appear in a drop-down when I go to a cell and start typing a formula. So, I'm not sure what's wrong.

The code I'm using is this -

Dim xlApp
Dim bbgAddin
set xlApp = CreateObject("Excel.Application")
Set bbgAddin = xlApp.AddIns.Add("path to bloomberg addin")
xlApp.Addins("Bloomberg Excel Tool").Installed = False    ' Need addin's name here
xlApp.Addins("Bloomberg Excel Tool").Installed = True

I'm using Excel 2007 on Windows Xp (shared terminal).
Any help is much appreciated.

 

[solitude]

Forgot to add:
[1.] The addin I'm trying to use is BloombergUI.xla (this is what goes in the path)
[2.] The macro that runs refers to BloombergUI.xla!RefreshEntireWorksheet
[3.] The sheet has bunch of 'BDP' Bloomberg function that give me 'Name' errors.

When I load the Bloomberg addin manually and run the macro myself, everything works fine. But fails when run through VBScript!

Cheers

 

[solitude]

Bump.

 

[solitude]

Anyone? Please!

 

[RoryA]

Are you sure that is the correct addin? (perhaps you need two Bloomberg addins?)

 

[solitude]

Thank you for your reply Rorya! While searching on this topic I've come across quite a many posts by you, so I hope you'll bring your magic here too!

About your question - well, since I'm doing the same thing with another addin in a separate file and getting the same error. Most likely there is something fundamentally wrong in what I'm doing.

If vbscript is slightly tricky to debug - could you suggest some other reliable way to automatically run a couple of excel macros on a daily basis. Will using something like "Workbook_Open()' be robust enough?

 

[RoryA]

Workbook_Open would be fine if you want the macros to run whenever you open the file.

My suspicion (and a quick Google seems to back this up) is that Bloomberg uses several addins, so you'd need to install them all. What you could do is just enable all installed add-ins and see if that works:

Dim xlApp
Dim bbgAddin
set xlApp = CreateObject("Excel.Application")
For each bbgAddin in xlApp.AddIns
with bbgaddin
   If .Installed then 
      .Installed = False
      .Installed = True
   End If
End With
Next bbgaddin

 

[Jaafar Tribak]

Now, I know that VBScript by default switches addins off so in the script I have lines that add Bloomberg addin.

Have you thought of Shelling the excel application to load all the installed addins as opposed to the CreateObject function ? You can still get a pointer to the launched excel application by using the GetObject function afterwards.


Something along these lines :

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "excel.exe"

set XL =GetObject("","Excel.Application")

 

[solitude]

Thank you Jaafar and Rorya, I'll try both of these tomorrow in office.

 

[marconi8232]

Have you thought of Shelling the excel application to load all the installed addins as opposed to the CreateObject function ? You can still get a pointer to the launched excel application by using the GetObject function afterwards.


Something along these lines :

Set WshShell = WScript.CreateObject("WScript.Shell")

WshShell.Run "excel.exe"

set XL =GetObject("","Excel.Application")

Hi,


It does not seem to work properly according to your instructions. My problem is that the BDP function keeps on recalculating and does not stop with recalculation even after 90 seconds.
Could someone please post a vbscript code that works properly in recalculating an Excel file with Bloomberg's BDP functions?
Many thanks in advance.