VBA to update SharePoint list multi-line text box

VBA to update SharePoint list multi-line text box

Channel Link: https://www.youtube.com/vbaa2z
SharePoint Automation with VBA:
https://www.youtube.com/watch?v=KdMM0hgp4q4&list=PLo0aMPtFIFDrcPiWbqJGb3qt3rkOmjDbN

Comments within this blog are not monitored so please comment on the YouTube link.

If you have any questions/feedback/tutorial request, please comment on the video link or directly email me at vbaa2z.team@gmail.com and I will try and come back as soon as possible.

Option Explicit
Sub update2()

'-----------------------------
'Thanks for downloading the code. 
'Please visit our channel for a quick explainer on this code.
'Feel free to update the code as per your need and also share with your friends.
'Channel: Youtube.com/vbaa2z
'Download free codes from http://vbaa2z.blogspot.com
'Subscribe channel: youtube.com/vbaa2z
'Author: L Pamai (vbaa2z.team@gmail.com)
'-----------------------------

'upload file to sp lib
'add new rec to sp list and link it to uploaded file
'add ref to ms activex data objects **.* library

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset 'tb

Dim mySQL As String

Dim filecaption As String
Dim filelink As String

If UploadToSharepoint = False Then
    MsgBox "Sorry upload failed!"
    Exit Sub
End If

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

filelink = [D11].Text
filecaption = [D8].Text

mySQL = "SELECT * FROM [sptb] WHERE [Title] = 'Test 2';"

With cnt
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=YOUR SHAREPOINT SITE URL;LIST={YOUR LIST GUID};"
    .Open
End With

rst.Open mySQL, cnt, adOpenDynamic, adLockOptimistic

While Not rst.EOF
   'rst.Fields("Title") = Sheets("Form").Range("D4").Text
    'rst.Fields("FileLink") = filecaption & filelink
    rst.Fields("Comments") = rst.Fields("Comments") & vbNewLine & _
    "This is test" & _
    vbNewLine & "This is line 2" & _
    vbNewLine & "This is line 3"
    rst.Update
    rst.MoveNext
Wend

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
MsgBox "Complete!"
End Sub