Banging my head against the wall on this one and every option I try has a problem with what i am trying to achieve.
在这个问题上我的头撞在墙上,我尝试的每一个选项都与我想要达到的目标有关。
So I have a pivot table in excel that is connected to a database. What I want to be able to do is have a link in another excel sheet to that pivot table, so that it picks up any changes to it in terms of data. The reason I am doing this is because I am putting the excel file up on sharepoint, but if a user downloads it, it retains the connection to the SQL Database, and because they are not authenticated, it doesn't refresh. I need them to be able to do this because it will allow them to customise their pivot table as needed, and it will retain that when they next open it. But the original pivot table, connected to the datasource, won't be affected.
所以我在excel中有一个连接到数据库的数据透视表。我希望能够做的是在另一个excel表中有一个到该数据透视表的链接,以便它根据数据获取对它的任何更改。我这样做的原因是因为我将excel文件放在sharepoint上,但是如果用户下载它,它会保留与SQL数据库的连接,并且因为它们未经过身份验证,所以它不会刷新。我需要他们能够这样做,因为它将允许他们根据需要自定义他们的数据透视表,并且当他们下次打开它时将保留它。但是连接到数据源的原始数据透视表不会受到影响。
I have tried:
我试过了:
Copying Pivot Table: I have copied the pivot table and pasted it in a new excel file, and this retains the connection to the SQL Database so doesn't work.
复制数据透视表:我复制了数据透视表并将其粘贴到新的Excel文件中,这样就保留了与SQL数据库的连接,因此不起作用。
Moving Worksheet: I have tried moving the whole worksheet to a new excel file and this does again retains the link to the SQL Database.
移动工作表:我已经尝试将整个工作表移动到一个新的excel文件,这确实再次保留了SQL数据库的链接。
Creating connection to excel file: This connected to the excel file, but the pivot table is lost and it simply shows the data in standard excel format.
创建与excel文件的连接:这连接到excel文件,但数据透视表丢失,它只是以标准excel格式显示数据。
Slicers: I have looked at slicers but this won't work either, because it means if he makes a change it is reflected in the original pivot table.
Slicers:我看过切片器,但这也不起作用,因为这意味着如果他进行了更改,它会反映在原始数据透视表中。
Sharepoint: I have looked at excel services on sharepoint. Although I can get him to view the data on the online excel, again, if he downloads it it loses the connection.
Sharepoint:我看过sharepoint上的excel服务。虽然我可以让他查看在线excel上的数据,但是如果他下载它就会失去连接。
I guess I could change the authentication settings on SQLServer so that rather than using windows authentication I create a password and then that password is retained as I copy the file. Because this would only allow access to the view in the SQL Database this would prevent any detrimental activity. However, what I would really like to do is just give a copy of the excel file that connects to the original pivot table (so that when this updates this data is reflected in the copy on refresh), but any changes in the copy (i.e. formatting etc) only affect the copy.
我想我可以更改SQLServer上的身份验证设置,这样我就不会使用Windows身份验证创建密码,然后在复制文件时保留该密码。因为这只允许访问SQL数据库中的视图,所以这可以防止任何有害的活动。但是,我真正想做的只是提供连接到原始数据透视表的excel文件的副本(这样当更新此数据时会反映在刷新副本中),但副本中的任何更改(即格式化等)只影响副本。
If anyone knows how to do this I would be most appreciative of your help.
如果有人知道如何做到这一点,我将非常感谢你的帮助。
1 个解决方案
#1
I may said something incorrect here, so pardon my lack of knowledge, but with the option:
我可能会说一些不正确的东西,所以请原谅我缺乏知识,但有选择权:
Creating connection to excel file: This connected to the excel file, but the pivot table is lost and it simply shows the data in standard excel format.
创建与excel文件的连接:这连接到excel文件,但数据透视表丢失,它只是以标准excel格式显示数据。
If I understand correctly, Creating connection to excel file provides a copy of the database in an excel file with all the source data from the original pivottable, and if by "connected to the excel file" you mean that any changes to the original pivottable is updated in this connected file in the copy of the database.
如果我理解正确,创建与excel文件的连接将在excel文件中提供数据库的副本,其中包含来自原始数据透视表的所有源数据,如果通过“连接到excel文件”,则表示对原始数据透视表的任何更改都是在数据库副本中的此连接文件中更新。
Then adding a replica of the pivottable in this connected excel file pointing to the copy of the data would not solve the problem?
然后在这个连接的excel文件中添加一个指向数据副本的pivottable的副本将无法解决问题?
Am I missing something?
我错过了什么吗?
#1
I may said something incorrect here, so pardon my lack of knowledge, but with the option:
我可能会说一些不正确的东西,所以请原谅我缺乏知识,但有选择权:
Creating connection to excel file: This connected to the excel file, but the pivot table is lost and it simply shows the data in standard excel format.
创建与excel文件的连接:这连接到excel文件,但数据透视表丢失,它只是以标准excel格式显示数据。
If I understand correctly, Creating connection to excel file provides a copy of the database in an excel file with all the source data from the original pivottable, and if by "connected to the excel file" you mean that any changes to the original pivottable is updated in this connected file in the copy of the database.
如果我理解正确,创建与excel文件的连接将在excel文件中提供数据库的副本,其中包含来自原始数据透视表的所有源数据,如果通过“连接到excel文件”,则表示对原始数据透视表的任何更改都是在数据库副本中的此连接文件中更新。
Then adding a replica of the pivottable in this connected excel file pointing to the copy of the data would not solve the problem?
然后在这个连接的excel文件中添加一个指向数据副本的pivottable的副本将无法解决问题?
Am I missing something?
我错过了什么吗?