Please help! Excel 2016 truncates URL of REST API call to import XML data

Deleted
Not applicable

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

6 Replies
Can you perhaps post the URL?

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?

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?

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

What about saving the file as xlsb file?
NB: I'd be interested in having both a working and a not-working copy of that file so I can report a bug with the Excel team. If a working copy is not possible, perhaps just the one that does not work?