Synchronous Refresh Methods for VBA in Excel
Hi there,
I am currently trying to implement a button that simply refreshes the data in my workbook and then exports a specific sheet as a PDF. Everything works fine except that the refresh does not finish until after the PDF is created. The link below seemed to be an answer but it still does not work:
The line
Application.Run "PLRefreshWorkbookEventHandler"
works properly for refreshing the workbook but it starts, gets interrupted by my code to export as a PDF, and then finishes. The article I linked states that
Application.Run "EikonRefreshWorkbook"
should be used if I wanted synchronous behaviour and for the refresh to finish before anything else occurs. However, when I use the button with this line, nothing happens. I thought maybe it was my version and that mine does not allow for the second type of refreshing but I am not sure how I can check.
Any help would be appreciated.
I have to disagree with the answer by @Zhenya Kovalyov. EikonRefreshWorkbook macro is specifically designed to refresh all =TR functions in a workbook synchronously. The call NumFuncRefreshed = Application.Run("EikonRefreshWorkbook") returns the number of functions that have been refreshed when the call returns due to the job finished or due to timeout.
@jingwersen, it would be very helpful if you shared an example spreadsheet where running Application.Run("EikonRefreshWorkbook") has no effect. Or at least share an example of worksheet function that doesn't get refreshed. Note that running EikonRefreshWorkbook macro has no effect on =TF functions.
EikonRefreshWorkbook macro is available in Eikon version 4.0.23 and higher. You can check the Product Version in Help - About available in Thomson Reuters ribbon, though I don't believe the version of Eikon is the issue here. If EikonRefreshWorkbook macro were not available on your machine I would expect Application.Run("EikonRefreshWorkbook") to produce VBA runtime error 1004: Cannot run macro 'EikonRefreshWorkbook'...
Thank you! Unfortunately I just started working on this excel software a couple days ago so I am not too familiar. I think my issue is that the function calls to refresh the data are not =TR. When I checked "Manage Requests" to see what my company was using to refresh data, it is a function called =DSGRID. I am assuming that "PLRefreshWorkbookEventHandler" can handle this but "EikonRefreshWorkbook" cannot. If it helps, the requests are of type TimeSeries. I apologise on my lack of knowledge, I only started interning and was assigned this project to make it easier to refresh with a button.
You're correct that "PLRefreshWorkbookEventHandler" can refresh =DSGRID functions while "EikonRefreshWorkbook" cannot. However in my experience =DSGRID functions are always refreshed synchronously. I created a test spreadsheet containing a handful of =DSGRID functions and ran the following code
Debug.Print VBA.Now Application.Run "PLRefreshWorkbookEventHandler" Debug.Print VBA.NowI can see that the second timestamp print is several seconds after the first. I think I need more details of the original problem you're trying to solve, and ideally a way to reproduce it.
If you could post an example illustrating the issue, that would be most helpful.
@jingwersen in my opinion, what you are trying to do is asynchronous by nature, so it is quite difficult to propose anything without looking at the file. But you can explore either of these:
- You can create a logical function, that will return True when all the data is updated on your spreadsheet, and monitor the value of this cell in Worksheet_change() event handler, then generate the pdf.
- Substitute you data delivery routine with COM API calls, such as DEX2, RHistory or AdfinX Real-time.
- Redesign the workflow to leverage the Python API;
- Optimize/chain your spreadsheet functions so, that the last function to be executed will call the pdf generation routine with the MACRO param of the TR function.(For example, =TR("EUR=","BID",{"UPDFRQ","2S";"MACRO","examplemacro.xlsm!Sheet1.SimpleMacro"},,)
I would go for 2 or 3, let me know if you require any further info.