Forum Discussion

Anonymous's avatar
Anonymous
Oct 16, 2017

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

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 cr...
excel
office 365
xml
Anonymous's avatar
Anonymous
Oct 17, 2017

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?

JKPieterse's avatar
JKPieterse
Silver Contributor
Oct 17, 2017

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?

  • Anonymous's avatar
    Anonymous
    Oct 19, 2017

    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

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Oct 19, 2017
      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?
    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Oct 19, 2017
      What about saving the file as xlsb file?

Resources