VBA to open Excel file, refresh bloomberg data, save, close

Question

I'm trying to write a vba script that gets called in a batch file to open an excel file, refresh bloomberg data, save the file, and then quit excel.
There was a historical question which asked something similar, but the suggested answer didn't seem to work - I can open the file and refresh the data, but it doesn't save the file or close excel.
I tried also putting in as a macro with the workbook_open file, but then ran into a problem where excel is saving and closing the file before refreshing the data. Any suggestions would be much appreciated.
Immediately below is the modified vba code that refreshes the data, but doesn't save or close the excel workbook.

 'Write Excel.xls  Sheet's full path here
 strPath = "C:\MngXL\testbook.xlsm" 

 'Create an Excel instance and set visibility of the instance
 Set objApp = CreateObject("Excel.Application") 
 objApp.Visible = True

 Set wbToRun = objApp.Workbooks.Open(strPath) 

 StartAutomation
 DoneNow

 Sub StartAutomation()
     Dim oAddin
     Set oAddin = objApp.Workbooks.Open("C:\blp\API\Office      Tools\BloombergUI.xla")

     If Not oAddin Is Nothing Then
         objApp.DisplayAlerts = False
         objApp.Calculate
         objApp.Run "RefreshAllStaticData"
         objApp.Calculate
         objApp.Run "RefreshAllStaticData"
        'WaitTillUpdateComplete
    End If

    dim count 
    dim updated 
    updated = false 
    for count = 0 to 12 
        if updated = false then
            if      objApp.WorksheetFunction.CountIf(objApp.Range("rng_inWorkbook"),"#N/A Requesting Data...") = 0      Then
                updated = true
            else
                Application.OnTime Now + TimeValue("00:00:15"),      WaitTillUpdateComplete
            end if
        end if
    next

 End Sub

 Private Sub WaitTillUpdateComplete()
     Dim t
    t = 0
    objApp.Calculate
     If      objApp.WorksheetFunction.CountIf(objApp.Range("rng_inWorkbook"),"#NAME?") > 0      Then
         Application.OnTime Now + TimeValue("00:00:15"),      "WaitTillUpdateComplete"
     ElseIf      objApp.WorksheetFunction.CountIf(objApp.Range("rng_inWorkbook"),"#N/A") > 0 Then
         Application.OnTime Now + TimeValue("00:00:15"),      "WaitTillUpdateComplete"
     ElseIf      objApp.WorksheetFunction.CountIf(objApp.Range("rng_inWorkbook"),"#N/A Requesting      Data...") > 0 Then
         If t < 5 Then
             t = t+ 1
             waitlonger
         Else
             Exit Sub
         End If
     Else
         Exit Sub
     End If

 End Sub

 Sub waitlonger()
     Dim x
     x = Now + TimeValue("00:00:40")
     Do While x > Now
     Loop
     objApp.Calculate
 End Sub

 Sub DoneNow()
    wbToRun.Save 
    wbToRun.Close
    objApp.DisplayAlerts = False 
    objApp.Quit 
    MsgBox strPath & " " & strMacro & " macro and .vbs successfully      completed!!!!", vbInformation
 End Sub

elliot svensson elliot svensson · Accepted Answer · 2018-08-13T23:29:42

You need a strategy to let the refresh of Bloomberg data take about the right amount of time.

Currently, your program seems to allow only certain small amounts of time to pass with no feedback. Instead, you need to make a loop that cycles once every 10 seconds (or whatever makes sense) and checks to see if the program is done.

I like to do it this way:

dim count as integer
dim updated as boolean

updated = false
for count = 1 to 12 'or any value you choose
   if updated = false then
      if objApp.WorksheetFunction.CountIf(objApp.Range("rng_inWorkbook"),"#NAME?") = 0 Then
         updated = true
      else
         Application.OnTime Now + TimeValue("00:00:15"), "WaitTillUpdateComplete"
      end if
   end if      
next

VBA to open Excel file, refresh bloomberg data, save, close

1 Answers