1

A bit of backstory - the real problem I am trying to solve is how to package a PowerPivot / PowerView report that uses a SharePoint list as a data source, and have it be deployable to multiple SharePoint sites, and have it always look for the data source list on the same site where the report is deployed. (See my other question about this.)

Using a site relative URL would be ideal, but when playing around with setting up data sources for Power Pivot, it seems that it doesn't like relative URLs, it only accepts absolute URLs.

That's fine though, I have no problem writing some code that uses OpenXML to go in and change the URL of the data source to reflect the current site URL during the deployment process. Except I can't seem to find where that URL is inside the Excel file.

I have a file where I set up the data source by:

  • Clicking on the Power Pivot tab
  • Clicking on the "Manage" button in the ribbon
  • Clicking on "Get External Data" -> "From Data Service" -> "From OData Data Feed"
  • Entering the following URL as the data feed URL:

http://mySharePointServer/sites/mySite/_vti_bin/ListData.svc/NameOfMyList

This seems to work, it reads data from the list, and more importantly, when I upload the file into a SharePoint document library, and open it from there, it retains the connection and I'm able to refresh the data.

(By contrast, if I set up the data source by going to the Data tab, and clicking "New Query" -> "From Other Sources" -> "From SharePoint List", it works as long as the file is local. After I upload the file to SharePoint it loses the connection and won't refresh the data.)

But if I take a copy of the file, rename the extension to .zip, extract the innner XML files to a folder, open that folder with VS Code and search for that connection URL, I can't seem to find it anywhere. If I search just for the list name, I find a data connection in the connections.xml file that has a name of "DataFeed mySharePointServer MyListName", but that doesn't have the full URL.

Where is that stored, and how can I change it programatically?

Dylan Cristy
  • 916
  • 11
  • 29
  • Have you tried making the change you describe, saving the result with another name, and then opening the OpenXML Productivity Tool and diffing the original file and the new file? It usually is pretty good at telling you where to look. The tool is downloadable from Microsoft's Open XML SDK site – Flydog57 Aug 20 '18 at 15:48
  • @Flydog57 I already had the OpenXML tool, and had used it to look through the inner workings of the document before resorting to unzipping it and using VS Code to search through all the files, however, I somehow didn't know about the diff functionality. That's pretty handy, thanks for the tip. However in this case, it did not yield any results, no data source URLs found. – Dylan Cristy Aug 21 '18 at 15:12

0 Answers0