Looking for Excel Macro code to auto refresh JRS Live Data data
Accepted answer
The raw results that comes back from the JRS query have extra rows and columns and the event handler qt_AfterRefresh() shifts the results to remove those columns/rows. You could insert the REST API calls at the end of this macro.
3 other answers
To refresh the results in the QueryResults tab, either click Refresh All in the Data command section or click the button Refresh the query results in the RunQuery tab.
If you want to automatically update the results periodically (after you have clicked Make the results dynamic), go to the Data section and click on Connections. By default there should only be a single connection for the JRS query. Select the connection and click Properties. You should be able to schedule a refresh at regular intervals.
Comments
@Kevin Thank you for this information, it is part of what I need. But beyond that, I am looking for a way to automatically click the Refresh button. We have customers who want to periodically on a regular basis, refresh te data and send it to their data warehouse.
This would mean having some kind of Timer in the spreadsheet which wakes up every so often, clicks on Connections, clicks on properties and executes a macro that does a Restful API call to post the data to their data warehouse.
Here is an example:
Asked Anagha (anaghagala@in.ibm.com) and Shane (shane.hoey@ie.ibm.com) for their testing of your answer regarding automatic, periodic refresh of the JRS Exported data and I await their response. I will ask the question in the comment above as a separate question.
Kevin: I tested this (setting up a schedule in the connections property)....I have Office 2003, and cannot find this option. Which version have you ? The other functionality is fine...(Make results dynamic, Refresh query results)
The in the Data tab, click Connections, select the "Connection" entry and click Properties. In the connection properties, set the refresh interval as you desire. I set the interval for 1 minute and every minute a request was sent to JRS to refresh the query results. See the attached screen shot of the Excel dialogs.
For a user's custom data warehouse, I am not an expert in Excel so I do not know how you would extract the data from the updated Query Results tab and make a REST call to their warehouse.
Comments
@Kevin I am asking where in the JRS Excel macros, should our customer insert her Restful API calls. I agree that their DWH is custom and not something you should know about, but where in the JRS macro code should we insert the RESTful API calls to send the data to their warehouse? When that data is periodically refreshed by setting the parameters in Excel, we want the data which comes refreshed from RRDI through JRS to be sent right up to the customer's data warehouse. So we want that to be part of the refresh loop too.