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]

Hi!

What is it you're trying to achieve?



From what you've written, it seems:

You have a workbook that you're adding VBA code to. I'll call this SOURCE_Workbook


In this code you want to:

Open a DIFFERENT workbook (I'll call this TARGET_Workbook).

Presumably the TARGETWorkbook has a pre-existing Web QueryTable (i.e. "Get External Data >> From Web") that retrieves data from a Bloomberg website?

You're wanting this data (the Bloombery Web_QueryTable) refreshed every 30 seconds.



Is that correct?

If so, did you want to do anything with the data once it's refreshed (or is it in the format you want it in?)

 

[acap22]

Apologies, I probably should have explained in detail. I have SOURCE_Workbook in which I am writing the code. My intentions are to open the TARGET_Workbook over night, recalculate the formulas because I have today() formulas in there, and then I have =BDH formulas that populate from BBG's API to fill in the end of day data, and lastly, to save and close this file.

I just want this to run once every 24 hours, I had put the Application.OnTime snippet because I wanted to give the workbook time to refresh the BBG data if that is correct? No need to do anything else with the data, its already in the desired format.

Thank you very much

 

[MartyS]

Here's a sample you might like to try.

It's currently set as a DEMO version to test functionality to make sure I understand what you're trying to achieve..

i.e. When you click on INITIATE, after selecting the Target_Workbook, it will set a timer event for 30 seconds from the current time.

(you can change it to be longer if you wish)


After the elapsed time, it should Open the Target_workbook, recalculate ALL calculations in ALL open workbooks.. then Save the Target_Workbook, then Close the Target_workbook

and finally.. save this workbook.

It also has a LOG sheet to record "success" or "failure" so, should this spreadsheet be of interest to you (and we change it from the DEMO to the LIVE version) it will have a log of the time it opened the Target_workbook, did the recalc, the save, etc.


https://www.dropbox.com/s/dum9w9egbiwdjbg/Bloomberg Example.xlsm?dl=0



The only difference between the two "versions" (demo/live) is one line of code (clearly highlighted) that, when changed, will set it so the event is initiated according to this rule:

[Tomorrow] + [Initiate At] time


e.g. if you change the one line of code, enter 00:00:30 into the green field in the MAIN sheet and then initialise (having already set the path and filename)..

You'll have set an event for tomorrow at thirty seconds past midnight.

NOTE: You might want to make sure you don't have lots of other workbooks open as the Recalc recalcuates all workbooks.
NOTE: Currently, there isn't any code to "wait" for calculations to finish. There may be no need for this. If you're happy to give it a trial run, it could always be added if needed.

Hope that all made sense.

 

[acap22]

Thank you for the reply, that made sense. The code works well, but an issue is that it saves and closes the file in a split second. I am pulling data from Bloomberg and I am told I should use Application.Run "RefreshAllWorkbooks"(id eventually refresh multiple workbooks) to request the data. One issue is that Bloomberg cannot request the data while the macro is running, so I was told to use Application.OnTime to allow the macro to pause and let the data populate. In the code above, I reference "processSheet", but I actually do not need to process anything besides let the last row populate. After that, the file can save and close. Wondering if you have any insight into that process?

 

[MartyS]

If I understand correctly, that shouldn't be a problem at all..

It's quite late here.. so I'll make the amendment tomorrow morning, if that's ok?!

So.. instead of Recalc... we can change it to application.run "RefreshAllWorkbooks"

and instead of saving right away.. we can add in a timed event to allow the refreshing to take place.. then save after the delay-period when the new timer-event triggers.

How long do you estimate the delay should be. Are we talking seconds, minutes, hours?

If the refresh takes seconds, we could give the timer a 5 minute window.
If 2~3 minutes to refresh, a 10 minute timer window.. and so on.. - erring on the side of safety!

 

[acap22]

That is more than ok! It takes about 20-30 seconds, so maybe a few minutes?

 

[MartyS]

Hi,

Application.Run "MACRONAME" will run the macro called "MACRONAME".

You want to run a macro called RefreshAllWorkbooks


Could you let me know which workbook this macro is in and supply the code, please?

(Is it to be included in the MAIN workbook.. or is it to be in the one that will be opened just after midnight?) Hopefully the MAIN workbook, otherwise there may be a few problems opening a macro-enabled workbook!

 

[acap22]

Hi,

It will be included in the mainworkbook. The plan is to have a workbook that task scheduler opens up and fires up the macro, which will open up a different workbook after midnight. The code is the same as above, but after running through the code line by line, I have realized the code stops after opening up and recalculating. It will not execute the Application.Run command and so forth.

Thank you.

Sub Testing1O()
'
' Testing1O Macro
'
Application.DisplayAlerts = False
'prevent pop ups e.g. save file
Workbooks.Open Filename:="File Path + Workbook name"
'open workbook
Application.Calculate
'will just recalculate all open workbooks, specifically today() formula
 
Application.Run "RefreshAllWorkbooks"
Application.OnTime (Now + TimeValue("00:0:30")), "processSheet"
End Sub
Sub processSheet()
    Application.DisplayAlerts = False
    Savechanges = True
    Workbooks("US Equity - Daily ETF flows.xlsm").Close
    
    Application.DisplayAlerts = True
End Sub

 

[MartyS]

It will be included in the mainworkbook.

That's perfect.



Ok.. sorry if I wasn't clear enough.


Can we look at just one line of code. The line you mentioned has to be run.

Application.Run "RefreshAllWorkbooks"

All that line does is say "run the subroutine called RefreshAllWorkbooks"


In order for that to work, there has to be a subroutine called RefreshAllWorkbooks!


If someone has said that has to be run.. there must also be the following code:

Sub RefreshAllWorkbooks

    'Some code to be run

End Sub