Share via

How to change connection string for an Excel File Data Connection?

  • Article

Question

Wednesday, August 31, 2016 7:48 AM

I've been needing to use and configure an Excel Workbook to retrieve data from another workbook (which is uploaded to a Document Library in SharePoint) and upload it to SharePoint 2013. So far, I am successful in retrieving and refreshing the data via the desktop application (Excel 2010) but I am having trouble when it is uploaded to SharePoint. I am receiving the error that "Sheet1$ does not exist".

Based on my troubleshooting, I traced it back to this:

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. I tried manually changing the data source URL but whenever I save the properties, it reverts back and configures a new data source URL automatically. Granted, ANY CHANGE that I make to the connection string is ignored.

Why can't I change it? Is there any way to manually specify my own connection string? Or is Excel really just configured to auto-populate the connection string for an Excel file type of connection?

All replies (3)

Thursday, September 1, 2016 8:45 AM

Hi,

How did you connect to your SharePoint site Excel file to Excel 2010? There are multiple connection types in Excel 2010. It is better to share the detailed connection steps for further analysis. I would follow your steps for more troubleshooting.

Generally, Whichever way you choose to synchronize data between a SharePoint list and Excel 2010, this synchronization is one-way, or unidirectional. Changes made in the SharePoint list can be updated in the spreadsheet program, but any changes that you make in the spreadsheet program are not written to the SharePoint list.

For more information about Synchronize a SharePoint list with a spreadsheet program, please refer to:
https://support.office.com/en-us/article/Synchronize-a-SharePoint-list-with-a-spreadsheet-program-d4337a01-2be4-43b5-bb42-28aefe64eafd?ui=en-US&rs=en-US&ad=US

Regards,

Winnie Liang

Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
If you have feedback for TechNet Subscriber Support, contact [email protected].

Thursday, September 1, 2016 1:59 PM

Hello,

The steps I done is:

  1. Upload DataSource.xlsx to Document Library in SP2013 (e.g. http://contoso.com/sites/demosite/Documents)
  2. Open Excel 2010
  3. In the Ribbon, click Existing Connections
  4. In Existing Connections window, click "Browse for More"
  5. In the explorer tab, navigate to the document library URL "http://contoso.com/sites/demosite/Documents"
  6. Select the DataSource.xlsx
  7. Import data to current sheet
  8. In the Ribbon, click "Connections"
  9. In Connections window, open up the Properties window for the connection
  10. Go to "Definitions" Tab -> Authentication Settings. Change Authentication Settings to None to use the Unattended Service Account.
  11. Save file as "DataReport.xlsx". Upload to Document Library in SharePoint
  12. In SharePoint, go to a Page and insert an Excel Web Access Web Part. Connect the Web Part to the DataReport.xlsx workbook.
  13. 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.

Thursday, September 8, 2016 1:19 PM

Hello.. any updates on this?