none
Cannot set Sharepoint Lookup Value using ContentTypeProperties in VBA RRS feed

  • Question

  • Hi,

    Using Word 2010 Template in SharePoint 2010 Library having a lookup column.  Everything's working fine on the sharepoint side and Doc Info Panel is working fine in Word.  I can set the value of some of the sharepoint fields using ContentTypeProperties in VBA.  However, I get an error when trying to set a lookup column.  I've tried all sorts of values like so:

    ThisDocument.ContentTypeProperties("SP_Account") = "5089"

    ThisDocument.ContentTypeProperties("SP_Account") = 5089

    ThisDocument.ContentTypeProperties("SP_Account") = "5089;#CustomerName"

    ThisDocument.ContentTypeProperties("SP_Account") = "CustomerName"

    I get non descriptive automation error in some cases, other times Word crashes.

    I know how to do it using the SPFieldLookup class in .net, but is it possible in VBA?

    TIA,

    Josh

    Sunday, November 17, 2013 1:50 PM

Answers

  • Actually, I think this is a bug.  I was able to set the property using the CustomXMLPart directly like so:

    setContentTypeProperty docThis, "Account", "5089"


    Function setContentTypeProperty(TheDocument As Word.Document, _ ContentTypeItemName As String, stValue As String) As String Const SchemaPartNamespaceURI As String = _ "http://schemas.microsoft.com/office/2006/metadata/contentType" Const DataPartNamespaceURI As String = _ "http://schemas.microsoft.com/office/2006/metadata/properties" Dim cxnDataElement As Office.CustomXMLNode Dim cxnsDataElement As Office.CustomXMLNodes Dim cxnSchemaElement As Office.CustomXMLNode Dim cxnsSchemaElement As Office.CustomXMLNodes Dim cxpData As Office.CustomXMLPart Dim cxpSchema As Office.CustomXMLPart Dim cxpsData As Office.CustomXMLParts Dim cxpsSchema As Office.CustomXMLParts Dim strDataXPath As String Dim strElementName As String Dim strElementNamespaceURI As String Dim strPrefix As String Dim strResult As String Dim strSchemaXPath As String Set cxpsSchema = TheDocument.CustomXMLParts.SelectByNamespace(SchemaPartNamespaceURI) Set cxpSchema = cxpsSchema(1) strSchemaXPath = "//xsd:element[@ma:displayName='" & ContentTypeItemName & "']" Set cxnsSchemaElement = cxpSchema.SelectNodes(strSchemaXPath) Set cxnSchemaElement = cxnsSchemaElement(1) strElementName = cxnSchemaElement.SelectSingleNode("@name").Text Debug.Print "Actual Element Name: " & strElementName strElementNamespaceURI = cxnSchemaElement.ParentNode.SelectSingleNode("@targetNamespace").Text Debug.Print "Element Namespace URI: " & strElementNamespaceURI Set cxnSchemaElement = Nothing Set cxnsSchemaElement = Nothing Set cxpSchema = Nothing Set cxpsData = TheDocument.CustomXMLParts.SelectByNamespace(DataPartNamespaceURI) Set cxpData = cxpsData(1) strPrefix = cxpData.NamespaceManager.LookupPrefix(strElementNamespaceURI) If strPrefix = "" Then strDataXPath = "//" & strElementName Else strDataXPath = "//" & strPrefix & ":" & strElementName End If Set cxnsDataElement = cxpData.SelectNodes(strDataXPath) Set cxnDataElement = cxnsDataElement(1) While cxnDataElement.HasChildNodes Set cxnDataElement = cxnDataElement.FirstChild Wend

        'oddly, we get error unless we first set it to space, baffling
        cxnDataElement.Text = " "

    'now we can set the value to our string cxnDataElement.Text = stValue Set cxnDataElement = Nothing Set cxpData = Nothing Set cxnsDataElement = Nothing Set cxpsData = Nothing Set cxpsSchema = Nothing End Function

    This is a slightly modified version of Peter Jamieson's code here:

    http://answers.microsoft.com/en-us/office/forum/office_2010-word/set-default-filename-from-dip-values/1741650d-0c7d-4ed7-95ba-b2fe212f27aa?page=3

    Hopefully this is fixed in Word 2013.

    Josh






    • Marked as answer by joshbooker Thursday, December 5, 2013 12:11 PM
    • Edited by joshbooker Friday, December 6, 2013 4:06 PM
    Thursday, December 5, 2013 12:10 PM

All replies

  • Judging by the spam in this forum that's been here for days, I guess this forum is dead.  I'll try to find another place to post.
    Monday, November 18, 2013 2:07 PM
  • Hi,

    Welcome to post your issue in MSDN forum.

    According to your description, you want to know how to set a lookup column of SharePoint with VBA.

    >> I know how to do it using the SPFieldLookup class in .net, but is it possible in VBA<<

    No.

    You could “set the value of some of the SharePoint fields using ContentTypeProperties in VBA”, since the ContentTypeProperties is a property of Document object in Word Object Model. ContentTypeProperties Property returns the metadata stored in a document, such as author name, subject, and company. The Word Object Model is exposed to VBA and make it interact with the objects of Word. So you could do that.

    However, lookup column is a feature of SharePoint instead of Office Word. Since the API of SharePoint haven’t been exposed to VBA, I’m afraid we cannot use VBA code to interact with the lookup column of SharePoint.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 19, 2013 9:27 AM
    Moderator
  • Actually, I think this is a bug.  I was able to set the property using the CustomXMLPart directly like so:

    setContentTypeProperty docThis, "Account", "5089"


    Function setContentTypeProperty(TheDocument As Word.Document, _ ContentTypeItemName As String, stValue As String) As String Const SchemaPartNamespaceURI As String = _ "http://schemas.microsoft.com/office/2006/metadata/contentType" Const DataPartNamespaceURI As String = _ "http://schemas.microsoft.com/office/2006/metadata/properties" Dim cxnDataElement As Office.CustomXMLNode Dim cxnsDataElement As Office.CustomXMLNodes Dim cxnSchemaElement As Office.CustomXMLNode Dim cxnsSchemaElement As Office.CustomXMLNodes Dim cxpData As Office.CustomXMLPart Dim cxpSchema As Office.CustomXMLPart Dim cxpsData As Office.CustomXMLParts Dim cxpsSchema As Office.CustomXMLParts Dim strDataXPath As String Dim strElementName As String Dim strElementNamespaceURI As String Dim strPrefix As String Dim strResult As String Dim strSchemaXPath As String Set cxpsSchema = TheDocument.CustomXMLParts.SelectByNamespace(SchemaPartNamespaceURI) Set cxpSchema = cxpsSchema(1) strSchemaXPath = "//xsd:element[@ma:displayName='" & ContentTypeItemName & "']" Set cxnsSchemaElement = cxpSchema.SelectNodes(strSchemaXPath) Set cxnSchemaElement = cxnsSchemaElement(1) strElementName = cxnSchemaElement.SelectSingleNode("@name").Text Debug.Print "Actual Element Name: " & strElementName strElementNamespaceURI = cxnSchemaElement.ParentNode.SelectSingleNode("@targetNamespace").Text Debug.Print "Element Namespace URI: " & strElementNamespaceURI Set cxnSchemaElement = Nothing Set cxnsSchemaElement = Nothing Set cxpSchema = Nothing Set cxpsData = TheDocument.CustomXMLParts.SelectByNamespace(DataPartNamespaceURI) Set cxpData = cxpsData(1) strPrefix = cxpData.NamespaceManager.LookupPrefix(strElementNamespaceURI) If strPrefix = "" Then strDataXPath = "//" & strElementName Else strDataXPath = "//" & strPrefix & ":" & strElementName End If Set cxnsDataElement = cxpData.SelectNodes(strDataXPath) Set cxnDataElement = cxnsDataElement(1) While cxnDataElement.HasChildNodes Set cxnDataElement = cxnDataElement.FirstChild Wend

        'oddly, we get error unless we first set it to space, baffling
        cxnDataElement.Text = " "

    'now we can set the value to our string cxnDataElement.Text = stValue Set cxnDataElement = Nothing Set cxpData = Nothing Set cxnsDataElement = Nothing Set cxpsData = Nothing Set cxpsSchema = Nothing End Function

    This is a slightly modified version of Peter Jamieson's code here:

    http://answers.microsoft.com/en-us/office/forum/office_2010-word/set-default-filename-from-dip-values/1741650d-0c7d-4ed7-95ba-b2fe212f27aa?page=3

    Hopefully this is fixed in Word 2013.

    Josh






    • Marked as answer by joshbooker Thursday, December 5, 2013 12:11 PM
    • Edited by joshbooker Friday, December 6, 2013 4:06 PM
    Thursday, December 5, 2013 12:10 PM
  • Judging by the spam in this forum that's been here for days, I guess this forum is dead.  I'll try to find another place to post.

    Hi Josh

    Are you still seeing spam? That's been here "for days"? We've been hit by spammers quite a bit, but have usually managed to get it cleaned up relatively promptly... Until they change their log-in and inundate us again. If a moderator isn't active in the forum it can take a few minutes until the central administration is alerted. That can be triggered by marking a couple such messages as "Abusive".


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, December 6, 2013 3:40 PM
    Moderator
  • Hi Cindy,

    Thanks for checking.  There were pages of spam in this forum at times on Nov 16th, 17th & 18th.  Being the first time I've posted here, I figured it was a dead forum since I didn't do through several pages to find the most recent valid post.

    One time on the 18th I was actively on this forum and I marked some as abuse, could see the mod was actively removing spam and new spam would come shortly after.   

    Anyway, glad to know this is the right forum for word dev.  I haven't seen spam since.

    PS...I don't have to time to blog, but the above solution is something you may consider writing about.  I cannot find anything on bing that tells how to set SharePoint lookup columns in VBA.  I figured it was impossible until I ran across Peter's post which used CustomXMLPart to get the values from similar complex ContentType columns.  I modified his code to set the values for lookup columns and it works!  Inability to set them using .ContentTypeProperties seems like a bug to me.

    Have a great day!

    Josh

    Friday, December 6, 2013 3:58 PM
  • <<the above solution is something you may consider writing about. >>

    If I understood it, I'd try to find time to blog about it - but I don't, so I don't dare :-) But I'm very glad you took a moment to post what you discovered in this thread as I'm sure it's going to help others.


    Cindy Meister, VSTO/Word MVP, my blog

    Saturday, December 7, 2013 1:56 PM
    Moderator