Excel Get Data from Web (Legacy) connection issues

[Deleted]

Hi everyone,

I have am looking to import data from a web page and need to use the Get Data -> from Web (legacy) system, since the new Power Query option does not support proxy authentication.

I have successfully downloaded the data, but once i save, close and reopen the document, the connection fails, showing a shorter file path (see link #2). I am able to go into the connection and refresh manually, but i want the option for the end user to press the "Refresh All" button. Has anyone encountered excel removing characters from a connection due to it being too long? Is there another open to get around this issue?

Link #1

https://mpr.datamart.ams.usda.gov/ws/report/v1/beef/LM_XB459?filter={"filters":[{"fieldName":"Report...

Link #2 (Excel changes link #1 to this un-usable link)

https://mpr.datamart.ams.usda.gov/ws/report/v1/beef/LM_XB459?filter={"filters":[{"fieldName":"Report...

I have attempted to use the website below' solution, but i have not been able to connect (excel has been adding a "25" after the percent sign....)

https://bensullins.com/hacking-excel-web-queries/

Thanks for you help!

Chris

[#2]

For those having this issues - It seems that excel is cutting off the URL's at 115 characters. One way around this is to change your file type to the 97-2003 version: ".xls"

Below is the link that I found this workaround at. If anyone finds another way to fix this issue in Excel 2016, please let me know!

https://techcommunity.microsoft.com/t5/Excel/Please-help-Excel-2016-truncates-URL-of-REST-API-call-t...