Solved

Excel VBA SharePoint List

Posted on 2013-11-09
4
4,247 Views
Last Modified: 2013-11-10
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
Comment
Question by:Russellbrown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 500 total points
ID: 39636097
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
 

Author Comment

by:Russellbrown
ID: 39636478
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
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39636641
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
 

Author Comment

by:Russellbrown
ID: 39637790
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question