VBA Question on Application.Run Range("PLPauseResumeEventHandler") and Application.Run Range("TRExce
1. For Application.Run Range("PLPauseResumeEventHandler") and Application.Run Range("TRExcelReCalculateUpdatedFctManualMode"), instead of running for certain range is there a way to write it so it can pause and resume the 1 excel workbook?
2. Also, after we have started refreshing Refinitiv's data into excel, is there a way to know if the event has been completed?
i.e. if Application.Run ("PLPauseResumeEventHandler")=xldone then "do something" ?
so that we can process something once all the data has been updated?
Thank you in advance!
Best Answer
-
@Alex.Conti Thanks for your questions.
So for your first point regarding PLPauseResumeEventHandler. From this Tutorial you can see right at the bottom point 10:
-------------------------------------------------------------------------------------------------------------------------10. Although it is not part of the PLSynchronizationMgr library, there is an Eikon - Microsoft Office macro available to toggle between Pause and Resume Updates - this replicates clicking the Pause Updates/Resume Updates button on the Thomson Reuters Excel ribbon. Using this macro and the update status value, one can control the state of Eikon - Microsoft Office.
Application.Run "PLPauseResumeEventHandler"
------------------------------------------------------------------------------------------------------------------------
My understanding is that this did pause/resume updates for the entire workbook. What someone seems to have done on your side is to modify that macro into just applying those to specific ranges - hence the modified function you are running:
Application.Run Range("PLPauseResumeEventHandler")
If you were to try Application.Run "PLPauseResumeEventHandler" this should evert to affecting the whole workbook.
Regarding your second point, you need to be careful. I believe the event handler is just a toggle on or off - however you can use this with the current status of updating (paused, streaming or none) to determine state (from the above tutorial link):
---------------------------------------------------------------------------------------------------------------------
7. ConnectionMode, ConnectionState and UpdateStatus provide information about the type of connection (Internet, Managed or None), the current state of the connection (whether offline, online etc) and the the current status of updating (paused, streaming or none), respectively. Note in the code sample below where a check is done for the .ConnectionState - if it is 2 ("PL_Connection_State_Waiting _For_Login") then the .Login method is applied.
Dim WithEvents myPLSM As PLSynchronizationMgrLib.SynchronizationMgr
Private Sub checkSynchronization()
Dim strConnMode As String, strConnState As String, strUpdateStatus As String
If Not myPLSM Is Nothing Then Set myPLSM = Nothing
Set myPLSM = CreateSynchronizationMgr()
With myPLSM
Select Case .ConnectionMode
'PLConnectionModeType'
'PL_CONNECTION_MODE_NONE 0'
'PL_CONNECTION_MODE_MPLS 1'
'PL_CONNECTION_MODE_INTERNET 2'
Case 0
strConnMode = "0 - PL_CONNECTION_MODE_NONE"
Case 1
strConnMode = "1 - PL_CONNECTION_MODE_MPLS"
Case 2
strConnMode = "2 - PL_CONNECTION_MODE_INTERNET"
End Select
Select Case .ConnectionState
'PLConnectionStateType'
'PL_CONNECTION_STATE_NONE 0'
'PL_CONNECTION_STATE_MINIMAL 1'
'PL_CONNECTION_STATE_WAITING_FOR_LOGIN 2'
'PL_CONNECTION_STATE_ONLINE 3'
'PL_CONNECTION_STATE_OFFLINE 4'
'PL_CONNECTION_STATE_LOCAL_MODE 5'
Case 0
strConnState = "0 - PL_CONNECTION_STATE_NONE"
Case 1
strConnState = "1 - PL_CONNECTION_STATE_MINIMAL"
Case 2
strConnState = "2 - PL_CONNECTION_STATE_WAITING_FOR_LOGIN"
Case 3
strConnState = "3 - PL_CONNECTION_STATE_ONLINE"
Case 4
strConnState = "4 - PL_CONNECTION_STATE_OFFLINE"
Case 5
strConnState = "5 - PL_CONNECTION_STATE_LOCAL_MODE"
End Select
Select Case .UpdatesStatus
'PLUpdateStatusType'
'PL_UPDATES_STATUS_NONE 0'
'PL_UPDATES_STATUS_STREAMING 1'
'PL_UPDATES_STATUS_PAUSED 2'
Case 0
strUpdateStatus = "0 - PL_UPDATES_STATUS_NONE"
Case 1
strUpdateStatus = "1 - PL_UPDATES_STATUS_STREAMING"
Case 2
strUpdateStatus = "2 - PL_UPDATES_STATUS_PAUSED"
End Select
MsgBox "Connection Mode: " & strConnMode & Chr(13) & _
"Connection State: " & strConnState & Chr(13) & _
"Updates Status: " & strUpdateStatus
If .ConnectionState = 2 Then
' If the user log in details are already set for automatic log in, this will log in Eikon - Microsoft Office,'
' Or provide the log in dialogue box otherwise.'
.Login
End If
End With
End Sub-----------------------------------------------------------------------------------------------------------------------------
So please use the UpdatesStatus to determine status - if streaming is true then do something. However, you need to be careful as you seem to be using Manual Calculation mode as well. There are some workbook refresh options referred to in the source code contained in the tutorial link above including one as follows:
Private Sub myPLSM_OnRefresh(ByVal a_refreshType As PLSynchronizationMgrLib.PLRefreshType)
Dim arrRefreshTypes() As Variant
arrRefreshTypes = Array("0 - PL_REFRESH_SELECTION", "1 - PL_REFRESH_ACTIVEWORKSHEET", _
"2 - PL_REFRESH_ACTIVEWORKBOOK", "3 - PL_REFRESH_ALLWORKBOOKS")
MsgBox "Refreshed: " & arrRefreshTypes(a_refreshType)
Debug.Print "Refreshed: " & arrRefreshTypes(a_refreshType)
End Sub
I hope this can help.1
Answers
-
Thanks a lot!0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛