Run-time error 1004 - couldn't find BloombergUI

Question

I'm running a batch file that executes a VBScript that runs a macro in an Excel sheet on Bloomberg Terminal.

The excel sheet contains a lot of BDP formulas in cells. These all work fine. Initially, I a had a problem updating the data from Bloomberg and running the macro but this was solved by using bloombergui.xla.RefreshAllStaticData + a timer.

The macro runs perfectly when executed manually in excel but I am getting a "run-time error 1004 couldn't find bloombergui.xla..." when trying to automate it via batch & VBS.

Any ideas how to solve this? I think I have exhausted all options via google.

Macro:

Sub UpdateWeekly()

Application.Run "bloombergUI.xla!RefreshAllStaticData"
Application.OnTime (Now + TimeValue("00:00:25")), "WeeklyPDF"

End Sub

Sub WeeklyPDF()

Application.ScreenUpdating = True

ActiveSheet.Range("A1:V225").Select
Selection.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="O:\LOCATION" & Format(Date, "MMMM-DD-YYYY") & " " & "Weekly", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

Application.PrintCommunication = False

End Sub

VBScript:

Dim args, objExcel

Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open args(0)
objExcel.Visible = True

objExcel.Run "UpdateCreditWeekly"

objExcel.ActiveWorkbook.Close(0)
objExcel.Quit

Error comming from Application.Run "bloombergUI.xla!RefreshAllStaticData" — Andrew, Mar 06 '18 at 11:25
I think before calling `Application.Run "bloombergUI.xla!RefreshAllStaticData"` you need to check whether add-in is loaded or not — Maddy, Mar 06 '18 at 11:30
Did you try reading the documentation or searching for an answer before posting the question? There are plenty of examples on [so] and elsewhere... Kinda feels like I wasted my time putting the answer together for you.. — ashleedawg, Mar 06 '18 at 12:01
Thanks for your help ashlee, I've tried a lot of options on here and none have worked. Any suggestion which I should try? — Andrew, Mar 06 '18 at 12:05
@Andrew Sometimes bloomberg Add-in fails to load when Excel is instantiated programmatically ... I think you need to load it again every time. please check https://stackoverflow.com/questions/213375/loading-addins-when-excel-is-instantiated-programmatically — Maddy, Mar 06 '18 at 12:11

score 1 · Accepted Answer · answered Mar 06 '18 at 12:36

Bloomberg Add-in fails to load when Excel is instantiated programmatically ... I think you need to load it again every time.

Ensure the AddIn is there before calling Application.Run "bloombergUI.xla!RefreshAllStaticData"

    Sub UpdateWeekly()
        Dim blpAddin As Workbook
        On Error Resume Next
        Set blpAddin = Workbooks("bloombergUI.xla")
        On Error GoTo 0
        If Not blpAddin Is Nothing Then ' Check if add-in is loaded or not 
            Application.Run "bloombergUI.xla!RefreshAllStaticData" ' refresh Bloomberg formulas
            Application.OnTime (Now + TimeValue("00:00:25")), "WeeklyPDF" ' wait till bloomberg Formulas calculation complete
        Else
            Debug.Print "Bloomberg Add-in is not loaded"
        End If
    End Sub

If Add-in is not there then you need to Add/load add-in

    'To load the Add-in
    Application.Addins.Add(file path & name)

OR

    AddIns("Add-In Name").Installed = True

score 0 · Answer 2 · answered Mar 06 '18 at 12:00

I believe your syntax is incorrect.

Application.Run can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.

The macro you specify can be either a string with the macro name, or a Range object indicating where the function is, or a register ID for a registered DLL (XLL) function. If a string is used, the string will be evaluated in the context of the active sheet.

Also, if you're using a newer version of Excel you're going to have further problems because I believe XLA is an older filetype, since replaced by XLAM.

See the links below for more information, especially this one and this one.

More Info:

MSDN: Application.Run method (Excel)
Ron de Bruin : How do I use Application.Run in Excel
Stack Overflow : Run Excel Macro from Outside Excel Using VBScript From Command Line
Stack Overflow : How do I call an xll addin function from vba?

Run-time error 1004 - couldn't find BloombergUI

2 Answers2

More Info: