Struggling with code and Bloomberg Refresh

[acap22]

Hi,

I've taken a look around some of the previously posted solutions, but am having trouble understanding why my code will not work. I am four hours new to VBA, so please forgive me if this is all wrong. I am able to open the file and the data refresh, but I am unsure of whether its refreshing on its own or via the BBG command below and it will not save/close the file.

Any help would be greatly appreciated.

Sub Testing1O()
'
' Testing1O Macro

Application.DisplayAlerts = False

Workbooks.Open Filename:="Path+File Name"
 

Application.Calculate
'recalculate open workbooks

End Sub
 
Sub refreshSheet()
    Application.Run "RefreshAllWorkbooks"
    Application.OnTime (Now + TimeValue("00:0:30")), "processSheet"
End Sub

Sub processSheet()
    Application.Calculate
End Sub

Sub Testing11()
    Savechanges = True
    Application.DisplayAlerts = True
End Sub

 

[MartyS]

Try this: https://www.dropbox.com/s/wdo8vpk0nlytqhy/Bloomberg Example V2.xlsm?dl=0

 

[acap22]

From what I was told, that command executes the refresh, not as a sub routine.

Sub refreshWorkbook()
   Application.Run ""RefreshAllWorkbooks"
   Application.OnTime (Now + TimeValue("00:01:00")), "processSheet"
End Sub

Sub processSheet()
   'perform processing here
End Sub

I do not actually need to perform any processing minus allowing the last row to populate with data.

The code below was given to me, but a bit more complex than I really need it to be. I believe they are refreshing the sheet and then checking to make sure that it is complete and not still requesting.

Option Explicit
Dim securities() As Variant
Dim secCounter As Integer
Dim refCounter As Integer
Sub initialize()
    secCounter = 1
    With sh3
        securities = .Range(.[SecList].Offset(1, 0), .[SecList].End(xlDown))
        .Range(.[SecList].Offset(1, 1), .[SecList].Offset(UBound(securities(), 1), 1)).ClearContents
    End With
    Call RefreshLoop
End Sub
Sub RefreshLoop()
    If secCounter <= UBound(securities(), 1) Then
        sh3.[Security].Value = securities(secCounter, 1)
        Application.Run "RefreshEntireWorksheet"
        Application.OnTime (Now() + TimeValue("00:00:01")), "hasRefreshed"
    End If
End Sub
Sub hasRefreshed()
    If sh3.[Security].Offset(1, -1).Value = "#N/A Requesting Data..." And refCounter < 10 Then
        Application.OnTime (Now() + TimeValue("00:00:01")), "hasRefreshed"
        refCounter = refCounter + 1
    ElseIf refCounter >= 10 Then
        MsgBox "It took too long to refresh or Manual Calculation mode enabled"
    Else
        Call processData
    End If
    
End Sub
Sub processData()
    sh3.[SecList].Offset(secCounter, 1).Value = Application.WorksheetFunction.Average(sh3.[TableLoop])
    secCounter = secCounter + 1
    Call RefreshLoop
End Sub

 

[acap22]

will try this, thank you

 

[acap22]

Worked flawlessly! it just popped up a window to click save, but I think I can handle disabling that and saving. Thank you so much, please let me know if I can upvote you or something.

 

[MartyS]

Hi..

In that version, I used Application.Calculate (and not Application.Run "RefreshAllWorkbooks")

If that worked.. and you're 100% certain it's ok.. that's great!!



Alternatively, below is a link for a version with Application.Run "RefreshAllWorkbooks" instead.

[I understand, now, that the RefreshAllWorkbook is part of the Bloomberg code!!]



https://www.dropbox.com/s/4d99d83496coww1/Bloomberg Example V2.xlsm?dl=0



If you have any problems with altering the Save functionality, or need anything further, just let me know!

And thanks for asking about the "upvote".. Not sure if that's a thing on here.. maybe the "thank you" or "like this this post" thing at the bottom of each post has something to do with that(??), but I've not paid much attention to those. Just happy to help where I can!

 

[acap22]

Hi Marty,

Thanks again for the help on this. I was wondering if you might be able to recommend the best way to modify the script to run multiple files and add more if needed.

Is it fine to just copy and paste (in a sense) the code so that the script is repeated for every file? Or would the script need to be rewritten?