How to add items to a Sharepoint list using Excel VBA?
Solution 1
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
Solution 2
You could use the Camelot .NET Connector to query SharePoint directly from VB/ASP using the COM+ component that comes with the next version (2.0). See http://bendsoft.com/net-sharepoint-connector/.
'define connection string
Dim connectionString
connectionString = "Server=mysharepointserver.com;Database=sites/test;Domain=;User=xxxx;Password=xxxx;Authentication=Ntlm;TimeOut=50;RecursiveMode=RecursiveAll;DecodeName=True;NoListFilters=False;ExpandUserFields=False;StrictMode=true;DefaultLimit=1000"
'activate connector com+
Dim connector
Set Connector = CreateObject("Camelot.SharePointConnector.Com.Connector")
' your query
Dim sql
sql = "insert into sharepointlist (userName, folderPath, calculatedValue, workDate) values ('" & userName & "', '" & folderPath & "', " & calculatedValue & ", '" & Now & "')"
' execute query
connector.ExecuteNonQuery(sql, connectionString)
sigil
Updated on July 31, 2022Comments
-
sigil almost 2 years
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?
-
John Saunders about 12 yearsA better way is to write a .NET component that uses "Add Service Reference" to access the service, thne expose this component as a COM object. That way, no obsolete software is used.
-
sigil about 12 yearsthat's using VB; I need to find a solution that works from VBA.
-
sigil over 11 yearsI ended up using the MSXML library to do this. Sorry for not marking the question as answered sooner.
-
Ulf over 11 yearsOk, I understand that, but COM+ can still be used with VBA right?