防止Excel修改数据关联

Question

我遇到了一个奇怪而令人沮丧的情况:我创建了一个基于Server表的PowerPivot。我已经为SQLNCLI11提供者选择了。

在我的笔记本电脑上，所有的功能都很好，但是当一位同事在他自己的电脑上打开工作表并想刷新数据时，Excel会抱怨提供者没有在机器上注册。这是第一件奇怪的事情:我安装了具有正确位版本的提供程序。但是，即使在重新启动之后，该消息仍然存在。

为了避免这种情况，我使用https://support.microsoft.com/en-us/help/2955369/cannot-modify-the-odata-connections-in-an-excel-2013-powerpivot-workbo中描述的步骤更改了提供程序

现在，奇怪和令人沮丧的事情是:当我(或我的同事)刷新数据时，Excel会更改提供者。为什么？

这是最初的情况：

​

​

然后更改连接:将提供程序更改为用于Server的MS提供程序，对于记录，我还将数据源更改为ip地址：

​

​

我保存Excel工作表，关闭它，然后重新打开它。数据源仍然是修改后的数据源。但是:当我刷新数据，然后再次检查数据源时，它已经恢复到原来的状态。

因此，基本上问题是:如何永久更改现有的连接？

现在我们开始了:有什么想法吗?我的同事pc不断抱怨SQLNCLI11提供商没有注册？

我正在使用Excel2016，Server版本是2014年SP2。

谢谢您抽时间见我

Answer 1

好的，我终于找到了一种(肮脏的)方法来让事情重新开始:我将xlsx扩展重命名为zip，并更改了xl\connections.xml。我改变了所有的

<x15:oledbPr connection

元素具有正确的提供程序并存储我的密码。保存更改后，它再次工作。

我还发现，如果在查询中更改别名，则不能再刷新数据。起初，我只是删除了查询并重新添加了它。但是，您也可以在XML文件中手动更改此操作。只需更改

<x15:dbCommand

元素在同一个connections.xml中。

Answer 2

由于您提到excel 2016，其中一个选项是使用Power Query加载您的Power模型。唯一的缺点是power查询意味着重新创建连接/表，如果已经创建了大量的计算/枢轴表，这可能会很痛苦。

当使用Power Query时，它将处理所有连接/数据转换，而POWER支点只关心返回的结果集。这意味着您可以通过编辑查询在数据源类型之间进行交换，例如从excel -> means > SQL Database作为连接类型，而不必在Power中重新创建连接/表。

Power查询中的大多数操作都是通过GUI完成的，尽管有时能够编辑脚本是很有用的。一个很好的例子是引用一个参数表，该表包含诸如连接和筛选条件之类的信息。

下面作为示例的查询脚本连接到excel文件，该位置定义为脚本中的文件变量。

Source = Excel.Workbook(File.Contents(File_Parameter), null, true),
Data = Source{[Item="Data",Kind="Sheet"]}[Data],

我可以将它交换为使用不同的参数，并对语法进行轻微的编辑，以处理MS访问。

Source = Access.Database(File.Contents(DB_Location), [CreateNavigationProperties=true]),
Data = Source{[Schema="",Item="factSellout"]}[Data]