Excel Add-in - New item in list and list reordering issue
I am facing an issue with the excel add-in, I am not sure how to solve it or if there are any workarounds.
The context is, I am using an excel template with over 10 read-only connection for reporting purpose. In some instances, when refreshing "multiple connections", the refresh fails.
After running a lot of scenarios, I found the reason why the import failed:
- a new item is inserted into a list while excel is open (which seems to be a bug)
- the list is reordered
Test steps to recreate the error when a new item is inserted into a list:
1) Create a module with one dimension and one line item
2) Create a view for the module and import it into excel as a "read-only connection - Import is sucessfull
3) Add an item in the list and refresh the excel file. Ana plan will show an error: "Refresh failed due to an unexpected error"
4) Save the excel file, close excel, re-open excel and "refresh". The import is sucessfull: "refresh completed successfully with 2 message(s)), "Row dimension list has 1 new item" and "A new row has been inserted into the view".
It seems that a user will have to restart excel each time a new item is added to a list.
Test steps to re-create the error message when a list is reorder:
1) Same as above, create a module with a view that is imported in Excel
2) Reorder some items of the list
3) Refresh multiple connections. The refresh will fail with the following error: "The dimensions of this view have changed. Preview the connection before refreshing".
In this scenario, a user will have to go through the 10 excel tabs one by one, click on "preview" , "Ok" and "process with the Pivot".
First, I wonder if the scenario 1 and scenario 2 are bugs from the excel add-in? Also, What is the best way to have a good user experience without asking the user to refresh manually 10 tabs?
Thanks a lot