Partager via

You can access arbitrary ranges via REST – here’s the trick

  • Article

Reader JJDE asked in this post if it was possible to access arbitrary ranges with REST – you just need to use a pipe character where you would usually place a colon:

https://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet1!A1|Z100')

This will return the Sheet1!A1:Z100 range.

As for why we had to do this.. IIS does not like colons in the path portion of a URL – any colon in the Path causes IIS to not allow the request to go through. Now, there’s a workaround to that by tweaking some registry key, but we did not want admins to do it nor did we want to do this automatically, so we decided to use the pipe symbol instead.

Comments

  • Anonymous
    February 28, 2011
    What if the worksheet has a space in the name? ie "Sheet 1" instead of "Sheet1"   This does not seem to work http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet 1!A1|Z100') it gets converted to http://tsdlt2/_vti_bin/ExcelRest.aspx/Docs/Bugs.xlsx/Model/Ranges('Sheet%201!A1|Z100') and I get a 404 error Thanks, Lewis

  • Anonymous
    May 08, 2012
    thanks for the tip I was looking for this