I have a spreadsheet in Excel that connects to an internal DB using the odbc driver for the software (Action Request System). That works fine. Now I'm trying to move the Excel file to a SharePoint site so that our team can review the data and make notes in the same spreadsheet.
我在Excel中有一个电子表格,它使用软件的odbc驱动程序(Action Request System)连接到内部DB。这很好。现在,我正在尝试将Excel文件移动到SharePoint网站,以便我们的团队可以查看数据并在同一电子表格中做笔记。
So, first I tried just moving the Excel file to the server, but realized that the connection saved in the file was pointing to the connection file on my computer. So I moved that to the SharePoint site, and this seems to be working... Except:
所以,首先我尝试将Excel文件移动到服务器,但意识到保存在文件中的连接指向我计算机上的连接文件。所以我把它移到了SharePoint站点,这看起来很有效......除了:
1) On some of the computers, it opens just fine. But I'd prefer it if the dialog that asks for the login and pw for the DB was customized.
1)在某些计算机上,它打开就好了。但是,如果要求登录的对话框和数据库的pw是自定义的,我更喜欢它。
2) On other computers, the software and drivers are installed, but not showing up in the user source list, so it throws an error. When the user goes to create a new connection, the driver is there, (so its not in the first list, but it is in the larger list).
2)在其他计算机上,软件和驱动程序已安装,但未显示在用户源列表中,因此会引发错误。当用户创建新连接时,驱动程序就在那里(因此它不在第一个列表中,但它在较大的列表中)。
3) Macs can open the file, but Excel 2008 can't do the macros and connections.
3)Mac可以打开文件,但Excel 2008无法执行宏和连接。
4) Opening the same file from the same site throws the Trust Center warning each time, even on the same computer/same user.
4)从同一站点打开相同的文件每次都会抛出信任中心警告,即使在同一台计算机/同一用户上也是如此。
Is there a way to do any of the following?
有办法做以下任何一种吗?
-
Have a non-macro that keeps the user from saving (and thus overwriting) the sheet until it is confirmed that the user won't make changes to the connections/macros?
有一个非宏,使用户不会保存(并因此覆盖)工作表,直到确认用户不会更改连接/宏为止?
-
Allow the user to connect from a local config (in case they can set one up that works when the main one fails) so that they can connect using that one but not screw it up for everyone else.
允许用户从本地配置进行连接(如果他们可以设置一个在主要配置失败时工作的配置),以便他们可以使用该连接进行连接,但不能将其用于其他所有人。
-
Set the trust settings (local or on server, I suppose) so that any files from a specific SharePoint are always trusted?
设置信任设置(本地或服务器,我想),以便来自特定SharePoint的任何文件始终受信任?
Finally, and this is the big one...
最后,这是最重要的......
Can you create a connection using a driver not on the local machine? If the driver is installed on a remote server, can Excel use that one? If that were the case, that would solve almost all other problems (Unless you're on a Mac).
您可以使用不在本地计算机上的驱动程序创建连接吗?如果驱动程序安装在远程服务器上,Excel可以使用那个吗?如果是这样的话,这将解决几乎所有其他问题(除非你在Mac上)。
I feel like I'm going at this slightly wrong, but since the team all need to see each others notes, our only other solution is to upload the data (from the DB) into a MySQL db so that they can make notes from their browser. This makes it more universally available, but forces us to script a lot of functions that are standard in Excel and create redundant DBs.
我觉得我有点错误,但由于团队都需要看到彼此的注意事项,我们唯一的另一个解决方案是将数据(从数据库)上传到MySQL数据库中,这样他们就可以从他们的数据库中做出记录。浏览器。这使得它更普遍可用,但迫使我们编写许多Excel中标准的函数并创建冗余DB。
3 个解决方案
#1
Wow! Access to the ARS via ODBC! I suggested that feature to the architect when I worked at Remedy HQ back in the nineties. Great times.
哇!通过ODBC访问ARS!当我在九十年代在Remedy HQ工作时,我向建筑师提出了这个功能。美好的时光。
Anyhow, I think your approach of sharing an Excel sheet that connects to an ODBC source is not the easiest path. (As you've been discovering.) ]
无论如何,我认为您共享连接到ODBC源的Excel工作表的方法并不是最简单的方法。 (正如你一直在发现的那样。)]
ODBC is always a local driver, afaik. A client locally connects to ODBC. ODBC then can use the network to connect to the DB.
ODBC总是本地驱动程序,afaik。客户端本地连接到ODBC。 ODBC然后可以使用网络连接到DB。
Instead, I suggest that you investigate setting up Access with a table that is remotely connected to the ARS system. You could then remotely open the Access table via file sharing as opposed to a dbms-connection. I believe that this would be easier to get going than your current path.
相反,我建议您调查使用远程连接到ARS系统的表来设置Access。然后,您可以通过文件共享远程打开Access表,而不是dbms连接。我相信这比你现在的路径更容易上手。
HTH,
Larry
#2
It sounds like you are using a User DSN to make the connection. You can switch to a DSN less connection by using a connection string. If you need some help with that, can you post the code that needs modified?
听起来您正在使用用户DSN进行连接。您可以使用连接字符串切换到DSN less连接。如果您需要一些帮助,可以发布需要修改的代码吗?
#3
First, thanks to both users that provided answers. Both of you were really helpful in getting my mind around the problem.
首先,感谢两位提供答案的用户。你们俩真的很有帮助我解决这个问题。
So, it turns out that while you can't easily connect to Action Request DB directly, AR does support Web Services.
因此,事实证明,虽然您无法直接连接到Action Request DB,但AR确实支持Web服务。
Rather than attempt to make Excel, Access, PHP, or any other system deal with creating a Soap Client and handle the work themselves, I came up with an alternative that I think is fairly clever, if I do say so myself:
而不是尝试使Excel,Access,PHP或任何其他系统处理创建Soap客户端并自己处理工作,我提出了一个我认为相当聪明的替代方案,如果我自己这样说:
1) Created a php script that calls the Web Service with the requested data,
1)创建了一个php脚本,用所请求的数据调用Web Service,
2) The script parses the response into a more generic XML form,
2)脚本将响应解析为更通用的XML表单,
3) script echoes out the xml
3)脚本回显xml
4) Name the script "AR_Data_Request.xml"
4)将脚本命名为“AR_Data_Request.xml”
5) Configure the directory, via the .htaccess file, to treat xml files like php
5)通过.htaccess文件配置目录,以处理像php这样的xml文件
Now, I have a static xml file which always contains the most up-to-date data from Action Request. I can point any other apps (specifically Excel) to that xml file instead of having to write VBA code to query the web service directly (and still have to do it again for php).
现在,我有一个静态xml文件,它始终包含Action Request中最新的数据。我可以将任何其他应用程序(特别是Excel)指向该xml文件,而不必编写VBA代码直接查询Web服务(并且仍然必须再次为php执行此操作)。
The only problem is that I can't get the php to connect to the Web Service (doh!) but that will be my very next question.
唯一的问题是我无法让php连接到Web服务(doh!)但这将是我的下一个问题。
Thanks again!
#1
Wow! Access to the ARS via ODBC! I suggested that feature to the architect when I worked at Remedy HQ back in the nineties. Great times.
哇!通过ODBC访问ARS!当我在九十年代在Remedy HQ工作时,我向建筑师提出了这个功能。美好的时光。
Anyhow, I think your approach of sharing an Excel sheet that connects to an ODBC source is not the easiest path. (As you've been discovering.) ]
无论如何,我认为您共享连接到ODBC源的Excel工作表的方法并不是最简单的方法。 (正如你一直在发现的那样。)]
ODBC is always a local driver, afaik. A client locally connects to ODBC. ODBC then can use the network to connect to the DB.
ODBC总是本地驱动程序,afaik。客户端本地连接到ODBC。 ODBC然后可以使用网络连接到DB。
Instead, I suggest that you investigate setting up Access with a table that is remotely connected to the ARS system. You could then remotely open the Access table via file sharing as opposed to a dbms-connection. I believe that this would be easier to get going than your current path.
相反,我建议您调查使用远程连接到ARS系统的表来设置Access。然后,您可以通过文件共享远程打开Access表,而不是dbms连接。我相信这比你现在的路径更容易上手。
HTH,
Larry
#2
It sounds like you are using a User DSN to make the connection. You can switch to a DSN less connection by using a connection string. If you need some help with that, can you post the code that needs modified?
听起来您正在使用用户DSN进行连接。您可以使用连接字符串切换到DSN less连接。如果您需要一些帮助,可以发布需要修改的代码吗?
#3
First, thanks to both users that provided answers. Both of you were really helpful in getting my mind around the problem.
首先,感谢两位提供答案的用户。你们俩真的很有帮助我解决这个问题。
So, it turns out that while you can't easily connect to Action Request DB directly, AR does support Web Services.
因此,事实证明,虽然您无法直接连接到Action Request DB,但AR确实支持Web服务。
Rather than attempt to make Excel, Access, PHP, or any other system deal with creating a Soap Client and handle the work themselves, I came up with an alternative that I think is fairly clever, if I do say so myself:
而不是尝试使Excel,Access,PHP或任何其他系统处理创建Soap客户端并自己处理工作,我提出了一个我认为相当聪明的替代方案,如果我自己这样说:
1) Created a php script that calls the Web Service with the requested data,
1)创建了一个php脚本,用所请求的数据调用Web Service,
2) The script parses the response into a more generic XML form,
2)脚本将响应解析为更通用的XML表单,
3) script echoes out the xml
3)脚本回显xml
4) Name the script "AR_Data_Request.xml"
4)将脚本命名为“AR_Data_Request.xml”
5) Configure the directory, via the .htaccess file, to treat xml files like php
5)通过.htaccess文件配置目录,以处理像php这样的xml文件
Now, I have a static xml file which always contains the most up-to-date data from Action Request. I can point any other apps (specifically Excel) to that xml file instead of having to write VBA code to query the web service directly (and still have to do it again for php).
现在,我有一个静态xml文件,它始终包含Action Request中最新的数据。我可以将任何其他应用程序(特别是Excel)指向该xml文件,而不必编写VBA代码直接查询Web服务(并且仍然必须再次为php执行此操作)。
The only problem is that I can't get the php to connect to the Web Service (doh!) but that will be my very next question.
唯一的问题是我无法让php连接到Web服务(doh!)但这将是我的下一个问题。
Thanks again!