Oct 16 2017 12:34 AM
Oct 16 2017 12:34 AM
Dear all,
I have created a number of Excelsheets that are using calls to a REST API for importing tables into the Workbook. (Through Developer Tools -> XML -> Import and then entering the URL.) I created these workbooks in Excel 2010 and they continue to update fine when I click "Refresh data" or "Refresh all" in Excel 2010 - but in Excel 2016 the URL gets truncated which leads to an error message and the data not getting updated.
I would be extremely grateful for any pointers as to how this can be resolved.
Thank you very much in advance for any help you may be able to provide.
All the best,
Stefan
Oct 16 2017 06:27 AM
Oct 16 2017 05:24 PM
Hi Jan-Karel, Thanks for replying! Would love to - but it includes our company's account details (Account and API Keys) for our Customer Relationship Management system. Will see if I can find any public API call to simulate the same behaviour. But as far as I can tell, the URL always gets cut off at 115 characters. So I'm hoping for some registry key to fix this. Would you have any ideas where to start looking for this?
Oct 17 2017 02:25 AM - edited Oct 17 2017 02:26 AM
I'm afraid not, I googled using a couple of keywords but was unable to find something. Have you tried copying the original URL and adding a new connection from 2016?
Oct 18 2017 06:50 PM
Hi Jan-Karel,
Thanks for even trying! (It's more than Office 365 support was willing to do...) The bizarre thing is that you can establish the data link using that same URL within Excel 2016, save the file and refresh the data as many times as you like - until you close and reopen the file, at which point you start getting that error message and the truncation issue. That file, created in Excel 2016 and effectively 'broken' in 2016 will then still be working in Excel 2010. So it's something to do with the way 2016 opens the file and reads out the value of the URL saved in the internal connextions.xml file under the xl path within the zip file that is the .xlsx file.
But in the meantime I have found the most unlikely workaround: saving the file as a 97-2003 .xls produces a compatibility warning but removes the issue, i.e. Excel 2016 is able to open the file and refresh the data without any problems.
I still believe that Microsoft should kindly acknowledge the issue and provide a proper fix for it.
Cheers,
Stefan
Oct 19 2017 12:51 AM
Oct 19 2017 12:52 AM