Allow another user to Refresh Excel file with Data connections

0
votes

In my Excel 2016 File, I have set up connections to SQL views to get data. (There are about 6 sheets and each one connects to a different SQL query View) However, when I give this file to another user to Refresh data, they get an error because the odc files are saved in My data Sources folder.

Is the best solution to copy over my 'My data Sources folder' to a folder they have access to and manually point each connection to a odc file in the My data Sources folder I have copied over?

Is there a better way?

1
excel

1 Answers

0
votes

On the Microsoft Office Support site it is said that

If the external data source that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permission, or other connection information.

In other words: Create a database on a server and ask your administrator to help you with it.

EDIT:

If the database is already somewhere on your LAN, then you should be able to use an odc file on the network. Here is an answer how to do that for Excel 2010. Hope it works for 2016 as well.

If the link dies, here is a copy of the most important parts of the text:

Connection files on the network

This list can be created from the following:

•A set of folders on your local network, the location of which can be deployed across the network as part of the deployment of Microsoft Office group policies.

•An Excel Services Data Connection Library (DCL) on a SharePoint Foundation site. For more information about DCLs, see the section Publishing to Excel Services securely.

And a step by step for the first option:

Here's the steps to publish a DCL using a GPO.

  1. Right click on the Group Policy Object folder and select the New option from the popup menu. Enter an identifier for the shared link, such as Enterprise Connections and leave the Source Starter GPO set to (none).

  2. Right click on the newly created GPO and select Edit from the popup menu.

  3. Expand the User Configuration, Preferences, and Windows Settings nodes in the tree on the left hand side to expose the Registry entry.
  4. Right-click on the Registry node and select the New|Registry Entry from the popup menu.
  5. Change the Action property to Create.
  6. Leave the Hive value set on HKEY_CURRENT_USER and enter Software\Microsoft\Office\14.0\Common\Server Links\Published\My Site\Sharepoint Connections as the Key Path.
  7. For the Value name, enter URL.
  8. Leave the Value type as the default of REG_SZ and enter the URL of your SharePoint DCL, i.e http://testsite/Data Connections for PerformancePoint.

  9. Create three additional registry entries with the Name, Data Type, and Values given in the table below.

    Name Data Type Value
    IsMember REG_DWORD 0 IsPublished REG_DWORD 1
    LinkType REG_QWORD DCL: 00 00 00 04 00 00 00 00

    Document Library: 00 20 00 00 00 00 00 00