Hooking into Refresh/RefreshAll events from listobject

[Mareen Philip]

I have an excel listobject that refreshes data from an external data source,
allows users to edit, and then publish back to the data source. Users
interact with the list through an add-in that provides ribbon options for
publish, refresh, etc.
The only access to the data source is through a custom .NET API - ADO.NET
and OLAP interfaces are not available. I do not have control over the APIs
exposed from the data source.

I need to hook the refresh into the "Refresh" and "Refresh All" options for
Excel, but I cannot seem to do this because my list is not based on a
QueryTable. The reason I need to hook into Refresh/RefreshAll, is to enable
users who are building pivot tables from this listobject to hook into the UI
refresh options.

Is this possible/supported? Any suggestions appreciated.

Thanks.
Mareen

 

[Joel]

The refresh query is an event and since your custom doesn't have an event you
have to create your own. Remember when the refresh is performed it will take
time where the user will be locked out from making changes. Depending on the
time it takes to perform the refresh will need to be considered in how often
to perform the refresh.

You can create your own event. Here are some examples

1) Refresh when the workbook is opened
2) Use On Time event to perform the update where you specify how often to
automatically refresh the table. See VBA On Timer event.

 

[Patrick Molloy]

did you try disconnecting then reconnecting the list object?

 

[Mareen Philip]

I should clarify :

- I already have a refresh button in the UI that users can call to refresh.
Unfortunately, users are expecting to click refresh-all to refresh all lists
and pivots, including mine [there may be multiple lists in the sheet].

- I do not want to automatically refresh the data on my list when the data
changes on the server. I specifically want to hook into the refresh UI calls
in Excel [say, clicking Refresh on the ribbon or right-click context menu].

Thank you.
-Mareen.