question

Upvotes
Accepted
monika.braszczok avatar image
1 ●0 ●0 ●0

Macro "Application.Run "EikonRefreshAll", True, 30000" not working

I am using Eikon Refinitiv in Excel as add-in.

Office 365.

VBA.

Windows 10.


I have a workbook with numerous worksheets, in which there are Refinitiv formulas. There are two types of formulas used:

1) Screeners in the form of formula TR(Screen.

2) data retrieval in form of RHistory.

3) data retrieval in form of TR

When trying to run the following macro I do not get results for all interations (manually I get results for each date) or when data is retrieved through macro it shows bad results (not the same when I carry out calculations manually instead of by macro).

It seems as if the Refinitiv formulas do not refresh properly. I tried different time intervals for Application.Run "EikonRefresAll",True (from 30000 to 300000) with no positive solution.

Please let mi know what formula should I use to update Refinitiv formulas in workbook, so that I do not have to manually perform 28 calculations each time for different dates analyzed.


Macro:

Sub new_macro()

'MACRO TO COPY CALCULATED RESULTS
Dim licznik_dat As Integer
For licznik_dat = 1 To 28
Worksheets("DATES TO BE CHECKED").Range("C2").Offset(licznik_dat - 1, 0).Copy
Worksheets("Choose").Range("C7").PasteSpecial xlValues

DoEvents
Application.Run "EikonRefreshAll", True, 30000
DoEvents

Worksheets("Choose").Range("C39").Copy
Worksheets("DATES TO BE CHECKED").Range("D2").Offset(licznik_dat - 1, 0).PasteSpecial xlValues

Next licznik_dat

End Sub



eikon#technologyvbamacros
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jirapongse avatar image   Jirapongse

@monika.braszczok

Hi,

Thank you for your participation in the forum.

Is the reply below satisfactory in answering your question?

If yes please click the 'Accept' text next to the reply. This will guide all community members who have a similar question.

Otherwise please post again offering further insight into your question.

Thanks,

AHS

Jirapongse avatar image   Jirapongse

@monika.braszczok

Hi,

Please be informed that a reply has been verified as correct in answering the question, and marked as such.

Thanks,

AHS

1 Answer

· Write an Answer
Upvotes
Accepted
jason.ramchandani01 avatar image
10.2k ●18 ●6 ●9

@monika.braszczok So without seeing your sheets its a bit difficult to see where or if there is a bottleneck. So just to be clear - this was working before and has now stopped working? You have the local spreadsheet on Manual Calc - and then wanting to refresh the data periodically followed by a recalc? There are different options for the refresh function - please see this thread for details. Also see this guide here

From the thread you can see the following 4 functions operate asynchronously:

1 - Application.Run "PLRefreshAllEventHandler" to refresh functions in all open workbooks

2 - Application.Run "PLRefreshWorkbookEventHandler" to refresh functions in the current workbook

3 - Application.Run "PLRefreshWorksheetEventHandler" to refresh functions in the current worksheet

4 - Application.Run "PLRefreshSelectionEventHandler" to refresh functions in the selected range

The following Refresh commands allow for a synchronous refresh to occur (one line of VBA code needs to finish executing before the next line can begin). Note they do not refresh TF functions:

1 - Application.Run "EikonRefreshSelection"

2 - Application.Run "EikonRefreshWorksheet"

3 - Application.Run "EikonRefreshWorkbook"

4 - Application.Run "EikonRefreshAll"

I don't know how large your sheets are or what if any race conditions are occurring or leading to timeout. Are you able to try the asynch functions to see if they can help (they dont have the refresh parameters? Also maybe try to see if this is working for 1 sheet properly and try sheet by sheet as there might be an issue with some instrument or screener query taking a long time to complete. Let us know if this helps at all.

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.