Question on Excel Macros

Morzanapy
Morzanapy Newcomer
edited January 22 in Eikon COM

Hello the Live Chat couldn't answer my question and referred me to this Forum.

It regards to Excel which, to my amazement, is not included in the "Select a category" menu above.

I have a macro that calls 10 RICs, one after the other in a loop, downloads the same basic data for each RIC, copy paste the results in another part of the macro and does the same for the next RIc until the 10 Rics are done.

The problems lies in the fact that the macro performs too fast and doesn't leave enough time between each Ric to download/refresh the information before copying the data.

I tries to introduce a line "Application.wait" for 10 seconds to give it time but it changes nothing.

My question is two fold:

  • is there a VBA command specifically for Excel Workspace that allows to pause a macro meanwhile Workpsace downloads/refreshes data before continuing ?
  • is there a list of all VBA commands that pertains to Workspace that can be consulted somewhere on the Refinitv site ?

I do not know if any users member of this community has ever encountered this issue but I am hoping someone can answer me.

Thank you in advance.

Answers

  • Jirapongse
    Jirapongse ✭✭✭✭✭

    @Morzanapy

    Thank you for reaching out to us.

    I found that you have submitted this issue to the support team.

    As far as I know, the Workspace Excel provides macros to refresh data, as mentioned on this discussion.

    I have contacted the case owner to reopen the case and escalate it to the Workspace Excel team to answer the following questions.

    • is there a VBA command specifically for Excel Workspace that allows to pause a macro meanwhile Workpsace downloads/refreshes data before continuing ?
    • is there a list of all VBA commands that pertains to Workspace that can be consulted somewhere on the Refinitv site ?

    Otherwise, I may need to submit this issue to the Workspace Excel support team on your behalf.

    For now, please share the excel file or code that you are using. Therefore, we will know the functions or macros that you are using.

  • thank you for your reply. How do i share the file ?

  • Jirapongse
    Jirapongse ✭✭✭✭✭
  • It's an .xlsm file as it contains a macro and the system doesnt allow me to attach it !

  • Jirapongse
    Jirapongse ✭✭✭✭✭

    Did you compress it with the zip tool?

    I could attach it, as shown on this discussion.

  • I managed to upload it.

    There are two macros, one without the "Applicatrion.wait" line and one with it.

    Start with the first one and see that there is not enough time for the worksheet to refresh the data before continuing the macro.

    When using the other one with the Application.wait line which has a wait of 10 seconds, it doesn't change anything.

    i need to find a VBA command that allows the worksheet to refresh the data before the instructions to continue the copy/paste command is implemented.

  • Jirapongse
    Jirapongse ✭✭✭✭✭
    edited January 22

    @Morzanapy

    Is it this one?

    Sub FUTPRICES()
    Dim StartingTime As Single
    StartingTime = Timer
    Dim i As Long
    For i = 8 To 24
    If Range("A" & i).Value > 0 Then
    Range("FUT").Value = Range("A" & i)
    Application.CalculateFull
    WSRefreshWorkbook
    Sheets("DASHBOARD").Range("I" & i & ":AC" & i) = Sheets("DASHBOARD").Range("RESULT").Value
    Application.CalculateFull
    End If
    Next i
    Range("A1").Value = Timer - StartingTime
    Range("FUT").Value = Range("A8")
    Application.CalculateFull
    MsgBox "Done"
    End Sub Sub WSRefreshWorkbook()
    DoEvents
    Application.Run "WorkspaceRefreshWorkbook", True, 5000
    DoEvents
    End Sub
  • Thank you so much !

    I increased the time parameter from 5000 to 10000 and it almost worked flawlessly.

    Well done and thank you again.