Question on Excel Macros

Morzanapy

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.

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.

Morzanapy

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

Jirapongse

https://community.developers.refinitiv.com/discussion/comment/123687#Comment_123687

You can zip it and upload the zip file in the reply.

Morzanapy

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

Jirapongse

https://community.developers.refinitiv.com/discussion/comment/123692#Comment_123692

Did you compress it with the zip tool?

I could attach it, as shown on this discussion.

Morzanapy

ST - Try - Refinitiv.zip

Morzanapy

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

@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

Morzanapy

Thank you so much !

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

Well done and thank you again.