I have one excel with sheets ( Sheet1 and Sheet2) uploaded in sharepoint.
Requirement : to create a URL to access the uploaded excel document and open specific sheet (say Sheet2)
Any solutions here?
-
Do you want a URL to open exact sheet in browser using Excel Web App? What is your SP version & edition? – Aziz Kabyshev Feb 23 '16 at 10:01
-
SP 2007 and Version 12.0.0.6421 – Pravin B Feb 23 '16 at 10:06
-
Yes want to open exact sheet . doesn't matter if it opens the sheet in excel client. – Pravin B Feb 23 '16 at 10:08
-
I tried opening the sheet by adding hyperlink for URL (path/to/Workbook.xls#SheetName!a1) in worddocument. It opens specific sheet ; however, it does not open if i direct access the same URL from browser. – Pravin B Feb 23 '16 at 10:12
That # symbol in URL usually divides an address and fragment location, and fragment location is never transmitted to server, but handled by client. See https://en.wikipedia.org/wiki/Fragment_identifier :
In URIs a hashmark # introduces the optional fragment near the end of the URL ... The fragment identifier functions differently than the rest of the URI: namely, its processing is exclusively client-side with no participation from the web server
So there's nothing SharePoint Server should do here, cause handling of #... part is a client app's job. And regarding those clients: You can use https://site/lib/workbook.xlsx#'Sheet1'!A1 links in some Office application. I can confirm those specific links are handled well in Excel / Word / PowerPoint, and had never seen them work in Project / Visio / Outlook.
This is how it actually works: fragment-aware application parses the URL, and launches Excel telling to open the document at https://site.lib/workbook.xlsx and then passes 'navigate to specific object' command with #... part of URL as an argument to Excel. In VBA this is done with something like Application.Goto Worksheets("Sheet1").Range("A1").
That clients are 'smart'. Browsers are not.
So the answer is yes, you can create that kind of links to Excel files stored in SharePoint with #...!... notation, but they will only work in context of other Office application.
The URL will be something like:
http://path/to/Workbook.xls#SheetName!a1
-
1I did try with path/to/Workbook.xls#SheetName!a1 also tried with path/to/Workbook.xls#'SheetName'!a1 , but no luck. Whenever i try with URL , it downloads the file from the browser and when you open it opens the last saved sheet – Pravin B Feb 23 '16 at 9:53
First create a named range for the table or data on the spreadsheet you want to link to. Now that I think of it I don't think it's necessary. But do it if all else fails. :-)
At the end of your file path add this ?web=1?range='YourWorksheetName' Where YourWorksheetName will be the name of the worksheet or the table range name.
Your final path will look something like this assuming it's an excel you are linking to in a SharePoint data library...
http://xxx/xxxxxx/xx/archmetrics/Data%20Library/3Q%202018%20Reporting.xlsx?web=1?range='TimeInPhase'
