vba 2007 sharepoint 2007 - update sharepoint list

[suzannejb]

Hi -

I have been attempting to update a sharepoint list using vba 2007. I have attempted the following:

• using SQL to treat the list like a database table (using connection strings - per SharePoint connection strings - ConnectionStrings.com). With this method I can write to an Access database that has the sharepoint list linked, but my users do not have MSAccess installed, and thus, the solution does not work for them.
• using soap/xml, but don't really understand it (the code is at end of this message).
• am considering direct access to opening windows and filling out forms, but don't have the access rights to install new tools on my development system (I know, so unfair)

So I have a couple questions:

1. is there some sample code available that I could test?
2. is there a way to see if I just don't have access rights to add to the list using vba? Our IT dept (I'm separated from them, in Engineering) has restricted direct code access to SharePoint somehow, but I cannot tell how far the restriction has gone.

Thanks,
Suzanne (desparate)

Sub UpdateListDescrption()
' Just activate first sheet for later usage of the cell values
Worksheets(1).Activate
Dim sURL As String
Dim sEnv As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim xmlDoc As New DOMDocument
' Change HERE and update your SharePoint url
sURL = "http://dev.rainerj.local/sites/ee/_vti_bin/lists.asmx"
sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"
sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""XML Schema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
sEnv = sEnv & " <soap:Body>"
sEnv = sEnv & " <UpdateList xmlns=""http://schemas.microsoft.com/sharepoint/soap/"">"
sEnv = sEnv & " <listName>" & Range("A2").Value & "</listName>"
sEnv = sEnv & " <listProperties><List Description=""" & Range("B2").Value & """/></listProperties>"
sEnv = sEnv & " </UpdateList>"
sEnv = sEnv & " </soap:Body>"
sEnv = sEnv & "</soap:Envelope>"

With xmlhtp
.Open "post", sURL, False
.setRequestHeader "Host", "dev.rainerj.local"
.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
.setRequestHeader "soapAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateList"
.send sEnv
MsgBox .responseText
'xmlDoc.Save "D:\RainerJ\EE\WebQueryResult.xml"
End With
End Sub