如何使用 Excel VBA 将项目添加到 Sharepoint 列表? [英] How to add items to a Sharepoint list using Excel VBA?

问题描述

我有一个基于 Excel 的应用程序，它收集一些用户输入，并根据该用户输入进行一些计算.应用程序本身不存储任何用户输入或计算；目前，每当用户运行应用程序时，它都会将数据发送到 Access 数据库，并将一行插入到链接到 Sharepoint 列表的 Access 表 xlTable 中.相关代码为:

I have an Excel-based application that gathers some user input, and makes some calculations based on that user input. The application itself doesn't store any of the user input or calculations; currently whenever a user runs the application, it sends the data to an Access database and inserts a row into an Access table xlTable that's linked to a Sharepoint list. The relevant code is:

sub sendToSharepoint(userName as string, folderPath as string, calculatedValue as long)

dim db as DAO.database
dim insertStr as string

'open connection to Access db
set db=OpenDatabase(myDatabasePath)

'build insert string
insertStr="insert into xlTable (userName,folderPath,calculatedValue,workDate) values (""" & userName & """,""" & folderPath & """," & calculatedValue & ","#" & Now & "#)"

'insert values into xlTable, which adds them to the Sharepoint list
db.execute insertStr,dbFailonError

end sub

因为我们在 Access 与 Sharepoint 断开连接时遇到了一些问题，因此无法填充列表，并且通常希望简化我们的数据传输过程，所以我想不使用 Access 直接将数据从 Excel 发送到 Sharepoint.我已经阅读了一些关于使用 Web 服务更新 Sharepoint 的内容，但我一直无法弄清楚这些是如何工作的，或者如何在 VBA 中实现它们.

Because we've had some issues with Access disconnecting from Sharepoint and therefore not populating the list, and in general want to simplify our data transfer process, I'd like to send the data directly from Excel to Sharepoint without using Access. I've read some stuff on SO about using Web Services to update Sharepoint, but I haven't been able to figure out how these work exactly, or how to implement them in VBA.

我需要关于我的 Sharepoint 列表的哪些信息才能从类似于上述代码的 Excel VBA 操作它?我需要添加任何引用吗?

What info would I need about my Sharepoint list to manipulate it from Excel VBA similar to the above code? Do I need to add any references?

推荐答案

所有数据操作都可以通过 SharePoint 列出名为 lists.asmx 的 Web 服务.您必须调用该 Web 服务.例如 UpdateListItems 方法 应该做 MS Access 现在做的事情.

All data manipulations can be done through SharePoint Lists Web service named lists.asmx. You have to call into that web sevice. For instance UpdateListItems method should do what MS Access does now.

您可以用来访问列表网络服务的一个选项是Microsoft SOAP Toolkit"，它必须作为办公组件安装(从未安装过，但这里有一篇文章描述它:http://oreilly.com/pub/h/1306

One option you can use to access the lists web sevice, is "Microsoft SOAP Toolkit" which has to be installed as office component (never done that, but here is an article describing it: http://oreilly.com/pub/h/1306

另一种选择是使用 MSXML 库(始终可用)并将 SOAP 请求作为简单的 HTTP 请求发送.然后将结果解析为xml.有一篇文章展示了如何做到这一点:http://blogs.msdn.com/b/jpsanders/archive/2007/06/14/how-to-send-soap-call-using-msxml-替换-stk.aspx

Another option is to use MSXML library (which is always available) and sending SOAP requests as simple HTTP requests. Then parsing the results as xml. There is an article showing examples how to do it: http://blogs.msdn.com/b/jpsanders/archive/2007/06/14/how-to-send-soap-call-using-msxml-replace-stk.aspx

这篇关于如何使用 Excel VBA 将项目添加到 Sharepoint 列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！