I have an Excel client and an API and both are under my control. What I want to achieve is a data connection in Excel that can be refreshed by the end user by the click of a button instead of downloading xml/csv/xls files. The benefit would be that the user can have extensive custom charts that don't need to be rebuilt every time a new data set is used, e.g. weekly pivot reports etc.
That is pretty straight forward when using single tenant SQL Server or Access as a data source, however the requirements are a bit different here:
- The data has to come through the API as data transformations have to be done.
- The user should authenticate with every refresh (i.e. basic auth).
- Support for Excel 2010
Data connection "From Web" seems like a good candidate, however this only works with html tables as far as I can tell. OData connections seem to be only offered with Power Query and the Professional Plus packages.
Does it make sense to go down the "From Web" connections route and convert thousands of rows of xml to html or are there other options?
UPDATE
I ended up creating an html table manually which is backwards compatible to Excel 2003 and does not require client-side VBA.
