Honestly, configuring this has been such a hard experience. I encountered many errors already, one after another but am stuck now. To illustrate what I've come across so far, I've had problems with user permission (I've now configured an Unattended Service Account) then ran into problems with missing OLE DB (installed correct version then added to Trusted Data Providers). I'm experiencing a new problem now and have become stuck as I have no idea how to fix it or why it is like that.
Based on my troubleshooting using SharePoint Log Viewer, my workbook cannot refresh because "The Microsoft Access Database Engine could not find the object 'Sheet1$'". I've checked the original source file (which is uploaded into a Document Library) and its there! Also, I have no trouble refreshing the data using the desktop application, its only when its uploaded to SharePoint that it throws out that error.
I've observed the connection string from my Excel file and saw that it was pointing to a Data Source on my local drive instead of the one in the Document Library. Here is what it looks like:
It seems that what happens is, everytime I try to refresh the data from SharePoint, it 'creates' a temporary file as specified there in the connection string above instead of using the original Excel file (which is also uploaded into SharePoint). I tried accessing the file but it seemed to be corrupted or locked by some service / user.
The problem is that I CANNOT change anything on the connection string. I've tried changing the Data Source URL manuallly to point to the data file (it's the one in the "Connection File"). However, any change I make just reverts back to an automatically generated connection string whenever I save the properties.
Can anyone enlighten me on this and why it is this way? Or if there is an alternative approach to doing this (or is it possible to use External Workbook as a data source at all)? Any one have similar experiences and solved it?
EDIT:
The steps I done is:
- Upload DataSource.xlsx to Document Library in SP2013 (e.g. [sitename]/Documents)
- Open Excel 2010
In the Ribbon, click Existing Connections
In Existing Connections window,click "Browse for More"
In the explorer tab, navigate to the document library URL "[sitename]/Documents" Select the DataSource.xlsx
Import data to current sheet
In the Ribbon, click "Connections"
In Connections window, open up the Properties window for the connection
Go to "Definitions" Tab -> Authentication Settings. Change Authentication Settings to None to use the Unattended Service Account.
Save file as "DataReport.xlsx". Upload to Document Library in SharePoint
In SharePoint, go to a Page and insert an Excel Web Access Web Part. Connect the Web Part to the DataReport.xlsx workbook.
Try to refresh data. It should result in an "We were unable to refresh external data..." error message.
Note: I've configured and confirmed that the Unattended Service Account and Data Refresh is working, by configuring another workbook which pulls out data instead from SQL Server. I've also encountered a missing OLE DB driver error message, but have fixed that already.
