0

I've scoured the internet for answers to this puzzling problem to no avail and have finally resigned to posting my own thread... It seems as though the internet has popped this one into the 'too hard' basket but I'm optimistic that we can solve this one once and for all...

I'm attempting to create a VBScript to automate a task using the Windows Task Scheduler. We currently have a shared Bloomberg Terminal and a process that requires using this terminal to update an Excel spreadsheet with data derived from Bloomberg Formulas using the Bloomberg Excel Add-in. Instead of manually repeating this task daily I'm trying to automate it to save the clog jam on the terminal (you get the point).

The VBScript is required to run at 8:45am each morning on the terminal, open the Excel spreadsheet, run the macro to refresh the Bloomberg Formulas and then run another macro to send that data in an email. For context, the data is to notify the email recipients of any new news, results and upcoming results from a list of companies that we have on our coverage.

From what I've read VBScript does not initialize any Excel add-ins and so the code is required to initialize and open each respective add-in. I've encompassed this into my code and have added a check that runs through the Application.Addins collection that verifies that the add-ins have installed = True and IsOpen = True. I have tried both opening and setting installed to True in the VBScript and the VBA code and have verified that all addins enabled when the script is running as per when I normally boot up Excel (see debug.print checker I've put in the code to confirm).

I have also tried to implement a wait on the macro which did not work, as well as separating out the Bloomberg refresh function into a separate subroutine that also did not work. The Bloomberg formulas always refresh straight after the macro stops...

I'm thinking this is either one or two things... One, Bloomberg has stops in place to prevent VBScripts from running (likely), or two, something is happening here which I'm missing (also very likely).

If anyone has a copy of a VBScript that does this then please share otherwise any help on this issue is much appreciated.

NOTE: You may be thinking that there are better ways to do this, and perhaps you are right, and perhaps this is sort of 'reinventing the wheel' however I'm determined to figure out exactly what's happening here at the very least for my own understanding and for others who may try this in future.

Apologies for any messy code, relative new to this ...

VBScript Code below -

'Input excel full file path
ExcelFilePath = [*Actual path-file removed*]

'Input module/macro name within the Excel File
MacroPath1 = "Module1.Refresh_Workbook"

'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
ExcelApp.Visible = True

'Prevent any app launch alerts 
ExcelApp.DisplayAlerts = True

'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)    

'Execute Macro Code
ExcelApp.Run MacroPath1

'Save Excel File 
wb.Save

'Reset Display Alerts Before Closing 
ExcelApp.DisplayAlerts = True

'Close Excel File
wb.close

'End instance of Excel
ExcelApp.Quit

'Leaves an onscreen message 
MsgBox "Your Automated Task sucessfully ran at " & TimeValue(Now), vbInformation

VBA Macro in workbook - Refresh_Workbook

> Public Sub Refresh_Workbook()
> 
> 
> 'Checking to see what addIns are installed and status 
> Dim add_ins As Variant
> For Each add_ins In Application.AddIns
>     Debug.Print "Name = " & add_ins.FullName
>     Debug.Print "Is Installed = " & add_ins.Installed
>     Debug.Print "Is Open = " & add_ins.IsOpen Next add_ins
> 
> Application.AddIns(4).Installed = True 'Using reference to refer to
> BBG add-in as two BloombergUI addins exist... .xla and xlam making
> hard to reference
> Application.Run "RefreshAllWorkbooks" 'Bloomberg Add-in function to refresh
>     
>Debug.Print "Refreshed BBG Code. Waiting ... "
> 
> Application.Wait (Now + TimeValue("00:00:15"))
> 
> 'Calling email generator 
> Call Auto_Email 
> 
> Debug.Print "Done"
> 
> End Sub
Vaxcel
  • 21
  • 2
  • I don't think that can work, you probably need to get the data directly within VBA by calling the Bloomberg VBA functions equivalent to BDP/BDS etc. – assylias Oct 14 '22 at 03:45
  • How are you running it in Task Scheduler? – Lundt Oct 14 '22 at 04:24
  • @Lundt - Currently have it scheduled to run in the morning, however I'm testing it by just running the vbs file at the moment – Vaxcel Oct 14 '22 at 05:06
  • @assylias - I've attempted to write the function into VBA manually and that does not work either... I.e. adding a string to the cell to invoke the function – Vaxcel Oct 14 '22 at 05:19
  • There are two (at least) setting in Task Scheduler that can affect Macros, as Macros run in a program designed for a user interface. These are *Run When User Logged On Or Not* and *Run With Highest Privilege*. – Lundt Oct 14 '22 at 06:19
  • 2
    Split the Excel macro into two parts: the first ending at "RefreshAllWorksheets" and the 2nd starting with the email creation. Leave out the Wait ... and instead move the wait to the VB script (https://stackoverflow.com/questions/1729075/how-to-set-delay-in-vbscript) and pause in between the two macro calls. – DS_London Oct 14 '22 at 07:39
  • @Vaxcel https://stackoverflow.com/a/14364491/829571 – assylias Oct 14 '22 at 09:02
  • @DS_London - yep that solved it. The trick is to enter the wait command into the VBScript as it allows Excel to execute the refresh then run the additional macros after that command in VBScript... PS I don't believe that this is a duplicate, and as explained in the intro there is no sufficient solution online... Thanks for your help all! – Vaxcel Oct 16 '22 at 23:02
  • 1
    It all stems from the same source: the Bloomberg Excel functions are asynchronous so don't return immediately. If you are running an Excel macro, the Bloomberg functions can't return until your macro finishes and unblocks the update process. Wait() inside the macro doesn't help (or DoEvents()). You can use Application.OnTime() to schedule a 2nd macro to run: that would work but not for your use case. Fortunately since your VB Script is in a different process, waiting there allows Excel to do whatever it has to do (and unblocks the Bloomberg returns). – DS_London Oct 17 '22 at 07:48

0 Answers0