You may receive the following error when trying to access a SharePoint workbook as a data source using PowerPivot:
Failed to connecto to the server. Reason: Invalid internet address.
This error happens when accessing a data source in SharePoint using a URL. It is caused by a limitation of the data provider when accessing a data source over a web connection. Converting the URL to a UNC path fixes this error. For example, if you are using the following URL in the data source connection:
http://spserver/reporting/report2013.xlsx
change it to the following UNC path:
\\spserver\reporting\report2013.xlsx
This will resolve the error and allow PowerPivot to successfully connect to the SharePoint workbook as a data source.
Another error you may receive when trying to access a SharePoint workbook as a data source is the following:
Failed to connect to the server. Reason: Failure creating file.
This error occurs when accessing the data source using the UNC path to the workbook while the WebClient service is in a stopped state on the client machine. The WebClient service is required to make WebDAV connections.
To start the WebClient service, in Windows go to Start > Control Panel > Administrative Tools and open Services. Right-click the WebClient service and select Start. If the Start option is grayed out, the service may be disabled. To enable the service, right-click WebClient and select Properties, and under Startup type select Automatic and click Apply. Then click Start and click OK.