question

Upvotes
Accepted
1 0 0 1

Refresh RHistory when workbook open via API

Dears,

I would like to launch query when workbook open and save as new excel file after all value update.

However, it can't refresh all values before auto save as a new excel file

Here is my VBA code (modify RHistoryAPI.xlsm)

Private Sub Workbook_Open()
	Dim MyWS As Worksheet
	Dim l_historyManager As HistoryManager
        Dim l_historyQuery As HistoryQuery
        Dim l_destinationRange As Range
        Dim l_result As String
	Set MyWS = Worksheets("Reuters")
	Set l_historyManager = RHistoryVBA.GetHistoryManager
	Set l_historyQuery = l_historyManager.CreateQuery("$D$6") 
	Set l_destinationRange = MyWS.Range("D6").Offset(2, 0)
	....
	...Subscribe Setting.....
	....
	l_result = l_historyQuery.Subscribe(a_instrumentIdList, a_fieldList, _
           a_requestParams, a_refreshParams, a_displayParams, l_destinationRange, 2)
	MyWS.Range("D6").Value = l_destinationRange.Address
	....
	...SaveAs Coding....
	...
	Thisworkbook.Save
	Thisworkbook.Close
End Sub
eikoneikon-com-api
icon clock
10 |1500

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

1 Answer

Upvotes
Accepted
4.6k 26 7 22

I would advise to fully utilize the event model of the RHistoryQuery object and save the sheet on the OnUpdate() event. This way it would be easier to troubleshoot the issue that you are having with Excel.

icon clock
10 |1500

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

Click below to post an Idea Post Idea