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

问题描述

我有一个基于Excel的应用程序，收集一些用户输入，并根据该用户输入进行一些计算。应用程序本身不存储任何用户输入或计算;目前每当用户运行应用程序时，它将数据发送到Access数据库，并将一行插入到与Accesspoint列表相关联的Access表 xlTable 中。相关代码是：

  sub sendToSharepoint（userName as string，folderPath as string，calculatedValue as long）
 
 dim db as DAO.database 
 dim insertStr as string 
 
'打开连接到Access db 
 set db = OpenDatabase（myDatabasePath）
 
'构建插入字符串
 insertStr =insert into xlTable（userName，folderPath，calculatedValue，workDate）values（& userName&，&folderPath&，& ; calculateValue&，＃& Now&＃）
 
'将值插入到xlTable中，将它们添加到Sharepoint列表中
 db.execute insertStr，dbFailonError 
 
 end sub 
  

由于我们从Accesspoint断开连接的某些问题因此没有填写列表，一般来说，想简化数据传输过程，我想直接从Excel发送数据到Sharepoint而不使用Access。我已经阅读了关于使用我们的一些东西b更新Sharepoint的服务，但是我无法弄清楚这些工作是如何正常工作的，或者如何在VBA中实现。

我需要什么信息？我的Sharepoint列表从Excel VBA操作类似上面的代码？我需要添加任何引用吗？

解决方案

所有数据操作都可以通过SharePoint 列出Web服务命名为 lists.asmx 。你必须调用这个web服务。例如 UpdateListItems 方法应该做什么MS Access现在。

您可以使用一个选项访问列表Web服务，是Microsoft SOAP Toolkit必须作为办公组件安装（从来没有这样做，但这里是一篇描述它的文章： 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-replace-stk.aspx

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

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.

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?

解决方案

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.

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

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屋！