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

Answer 1

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