Updating List in Sharepoint Online (o365) via Excel/VBA

Question

I am currently using VBA to update/create new sharepoint list items via SOAP.

My IT dept has advised that they are migrating to SharePoint Online and the same code no longer works, I believe due to different authentication processes.

I have spent close to four days trying to find an alternative solution for o365 via VBA but unfortunately I'm not having much luck.

I am hoping that there is someone out there that may be able to assist, I've included my original VBA/SOAP code as a reference for what I'm trying to replicate.

Many thanks, Riss

Sub addItemtoList()

    Dim LISTNAME As String
    Dim SharepointUrl As String
    Dim ValueVar As String
    Dim FieldNameVar As String
    Dim CurUserName As String, CurPassword As String
    CurUserName = "user.name"
    CurPassword = "password123"

    LISTNAME = "SharepointlistName"      'Or Guild

    SharepointUrl = "https://xxxxxxxx.sharepoint.com/sites/au/finance/xxxxxx/"
    ValueVar = "Testing Value Add"
    FieldNameVar = "'Title'"

    Dim objXMLHTTP As MSXML2.XMLHTTP

    Dim strListNameOrGuid As String
    Dim strBatchXml As String
    Dim strSoapBody As String

    Set objXMLHTTP = New MSXML2.XMLHTTP

    strListNameOrGuid = LISTNAME


    'Add New Item'
    strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='New'><Field Name='ID'>New</Field><Field Name=" + FieldNameVar + ">" + ValueVar + "</Field></Method></Batch>"

    objXMLHTTP.Open "POST", SharepointUrl + "_vti_bin/Lists.asmx", False, CurUserName, CurPassword
    objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
    objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

    strSoapBody = "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " _
     & "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " _
     & "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'><soap:Body><UpdateListItems " _
     & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
     & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

    objXMLHTTP.send strSoapBody

    If objXMLHTTP.Status = 200 Then
    ' Do something with response
    End If

    Set objXMLHTTP = Nothing

    End Sub