1

I have tried to collect all codes I could have done and it still not work for me. What I want to do is to Schedule Task of my Excel file and I have code "RunExcel.vbs" as attached but still not working.

Reference Link: How to set recurring schedule for xlsm file using Windows Task Scheduler

Reference Link: https://www.mrexcel.com/forum/excel-questions/794869-vb-script-refresh-bloomberg-feed-excel.html

  1. Open file “PriceRealTIme.xlsm”(Macro-enabled workbook) which is inside “TEst folder”.
  2. Ignore to update link
  3. Let it “Refresh Bloomberg Data” and “wait for at 1 minutes or until it done refreshing”.
  4. Once it’s done. I want to copy paste Value of those columns by using Macro named “CopyPaste”.
  5. Finally, let it “Save” and “Close” file.
  '   a .vbs file is just a text file containing visual basic code that has the extension renamed from .txt  to .vbs

'Write Excel.xls  Sheet's full path here
strPath = "C:\Users\chaic\OneDrive\Desktop\TEst\PriceRealTIme.xlsm" 

'Write the macro name - could try including module name
strMacro = "Sheet1.CopyPaste" 

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

  'Open workbook; Run Bloomberg Addin; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath) 

Private Const BRG_ADDIN As String = "BloombergUI.xla"
Private Const BRG_REFRESH As String = "!RefreshAllStaticData"
Private TimePassed As Integer

Sub StartAutomation()
    Dim oAddin As Workbook
    On Error Resume Next
    Set oAddin = Workbooks(BRG_ADDIN)
    On Error GoTo 0
    If Not oAddin Is Nothing Then
        Application.Run BRG_ADDIN & BRG_REFRESH
        StartTimer
    End If
End Sub

Private Sub StartTimer()
    TimePassed = 10
    WaitTillUpdateComplete
End Sub

Sub WaitTillUpdateComplete()

    If WorksheetFunction.CountIf(ThisWorkbook.Names("BloombergDataRange").RefersToRange,"#VALUE!") = 0 Then
        Application.StatusBar = "Data update used " & TimePassed & "seconds, automation started at " & Now
    Else
        Application.StatusBar = "Waiting for Bloomberg Data to finish updating (" & TimePassed & " seconds)..."
        TimePassed = TimePassed + 1
        Application.OnTime Now + TimeSerial(0, 0, 1), "WaitTillUpdateComplete"
    End If

End Sub

objApp.Run strMacro     '   wbToRun.Name & "!" & strMacro 
wbToRun.Save 
wbToRun.Close 
objApp.Quit 

   'Leaves an onscreen message!
MsgBox strPath & " " & strMacro & " macro and .vbs successfully completed!",         vbInformation
Community
  • 1
  • 1
mingmac
  • 11
  • 1
  • 3
  • Correct for wait duration I want just 10 seconds for Bloomberg to refresh data. – mingmac Mar 14 '18 at 03:32
  • you are waiting only 1 second for Bloomberg to refresh in this line `Application.OnTime Now + TimeSerial(0, 0, 1), "WaitTillUpdateComplete".` you are passing one second in this fuction `TimeSerial( hour, minute, second )`. I think you need to pass `TimePassed ` variable in place of `1` – Maddy Mar 14 '18 at 08:15
  • What do you mean that I have to pass TImepassed variable in place of 1? Thanks. – mingmac Mar 15 '18 at 02:59
  • You are calling WaitTillUpdateComplete sub each and every second. bloomberg need more time to refresh. – Maddy Mar 15 '18 at 03:02

1 Answers1

1

This is an old threat, but maybe this answer will help others. The code below is working for me. The computer is set for it to never sleep or lock the screen.

The computer is using Office 365 and excel 2016.

      '   a .vbs file is just a text file containing visual basic code that has the extension renamed from .txt  to .vbs

'Write Excel.xls  Sheet's full path here
strPath = "myPath" 


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

Set wbToRun = objApp.Workbooks.Open(strPath) 


StartAutomation

Sub StartAutomation()
    Dim oAddin
    Set oAddin = objApp.Workbooks.Open("C:\blp\API\Office Tools\BloombergUI.xla")
    objApp.Addins("Bloomberg Excel Tools").Installed = False
    objApp.Addins("Bloomberg Excel Tools").Installed = True

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

        WaitTillUpdateComplete

    End If
End Sub

Dim t
t = 0

Private Sub WaitTillUpdateComplete()
    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


wbToRun.Save 
wbToRun.Close
objApp.DisplayAlerts = False 
objApp.Quit
haas
  • 23
  • 5