Feb 01 2018 12:26 PM
Dear Community,
I managed to create a VBscript to access a Sharepoint-List, get a recordset, add a line and fields etc. with ADO. One thing that I can't get to work is how to set or update a multiple choice field.
When I get a recordset from an existing line the value is a string with the choices separated with ;#. However when I try to set the same line to a new record it fails.
What is the proper way to do that?
Here some sample code, the constants need to set to the actual sharepoint site/list/id. I created a test sharepoint list with the default Title column and one 'Options' column with a choice / Checkboxes with the choices:
Event
Problem
Change
With the below code a new record is created with the Title but with an empty Options field.
Who can help me do that? Thanks ahead for your help!
Public Const strSPCustomerBatchListName = "MyListName"
Public Const strSPSite = "https://MySharepointSite"
Public Const strSPCustomerBatchListID = "the connection ID"
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim mySQL As String
'set connection
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
'sql select statement
mySQL = "SELECT * FROM [" & strSPCustomerBatchListName & "] WHERE [ID] = 1;"
'open connection
With cnt
.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=" & strSPSite & ";LIST={" & strSPCustomerBatchListID & "};"
.Open
End With
'open table / recordset
rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic
rst.AddNew
rst.Fields("Title").Value = "Test3"
rst.Fields("Options").Value = ";#Event;#Change;#"
rst.Update
'close recordset/connection and clean memory
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing
Feb 01 2018 01:02 PM
Feb 01 2018 01:08 PM
Hi Juan,
I use vbscript a lot at Excel, Word, Outlook etc. I have some large script in excel to produce reports. I'd like to have the reports settings on sharepoint.
Feb 11 2019 08:10 PM - edited Feb 11 2019 08:11 PM
Hi Martijn, did you ever find a solution to this? I am facing the exact same problem. Any help would be greatly appreciated.
Feb 12 2019 12:46 AM
Hi @johnweathington John,
In short, no. Funny is that I went for other solutions in the mean time and am just picking up yesterday to use sharepoint lists again. Main reason is that SharePoint is available to me and a proper (SQL) database is not :-). For now I just avoid multi-choice fields and use text fields in stead and let the script do the logic. I forgot about this post to be honest but will update when I find a solution, will you too?
Kind regards,
Martijn.
Feb 12 2019 09:25 AM