Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4884
  • Last Modified:

Excel VBA SharePoint List

Hi,

I would like to programmatically ( using Excel VBA )  populate the "Description" e.g. "This list contains contact details"  of a SharePoint list which has a name, say, "ListABC".
      Lists               Description
     ListABC         This list contains contact details

Would appreciate it very much if you can help out with the VBA code.

Environment:  Excel 2010 and SharePoint 2010

Thank you
0
Russellbrown
Asked:
Russellbrown
  • 2
  • 2
1 Solution
 
Rainer JeschorCommented:
Hi,

this sub can do the job (using SharePoint web service):
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=""http://www.w3.org/2001/XMLSchema"" 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

Open in new window


In your Excel VBA add a reference to "Microsoft XML 6".
Then just update the SharePoint url as well as the request header to fit your environment and simply adjust the two cell values:
- The first one is the NAME of the list
- The second one the new description

HTH
Rainer
0
 
RussellbrownAuthor Commented:
Hi Rainer,

Thank you for the prompt response. It works great once again. Just a bit of fine-tuning. In the description the text maybe have "reserve character" e.g. "This list contains contact details
other matters". The code is not able to parse "&". Is there a work around please?

Otherwise it is all perfect.

Kind regards,
Russ
0
 
Rainer JeschorCommented:
Hi,
ah, sorry.
You will have to HTML/XML encode values like ampersands, quotes, lower/greater than ...

Therefore I just wrote a small helper function:
Function EncodeForXML(sToEncode) As String
   Dim sEncoded As String
   If IsNull(strXmlValue) Then
     EncodeForXML = ""
   Else
     sEncoded = CStr(sToEncode)
     ' 1. Replace the ampersand (as all other replacements will create ampersands as well
     sEncoded = Replace(sEncoded, "&", "&amp;")
     ' 2. Replace single quote
     sEncoded = Replace(sEncoded, "'", "&apos;")
     ' 3. Replace double quote
     sEncoded = Replace(sEncoded, """", "&quot;")
     ' 4. Replace lower
     sEncoded = Replace(sEncoded, "<", "&lt;")
     ' 5. Replace greater
     sEncoded = Replace(sEncoded, ">", "&gt;")
     ' Return the encoded value
     EncodeForXML = sValue
   End If
End Function

Open in new window


Now just call this function at the place where you set the description like
sEnv = sEnv & "      <listProperties><List Description=""" & EncodeForXML(Range("B2").Value) & """/></listProperties>"

Open in new window


HTH
Rainer
0
 
RussellbrownAuthor Commented:
Perfecto! Thank you very much, Rainer.

I  did minor adjustments ( lines 4 and 19 ) to align the variables and get function to run:
Function EncodeForXML(sToEncode) As String
   Dim sEncoded As String
   
   If IsNull(sToEncode) Then ' strXmlValue align variable #1
     EncodeForXML = ""
   Else
     sEncoded = CStr(sToEncode)
     ' 1. Replace the ampersand (as all other replacements will create ampersands as well
     sEncoded = Replace(sEncoded, "&", "&amp;")
     ' 2. Replace single quote
     sEncoded = Replace(sEncoded, "'", "&apos;")
     ' 3. Replace double quote
     sEncoded = Replace(sEncoded, """", "&quot;")
     ' 4. Replace lower
     sEncoded = Replace(sEncoded, "<", "&lt;")
     ' 5. Replace greater
     sEncoded = Replace(sEncoded, ">", "&gt;")
     ' Return the encoded value
     EncodeForXML =sEncoded  ' sValue   align variable # 2
   End If
End Function

Open in new window


Best regards,
Russ
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now