Assigning values to SharePoint document library lookup fields

Question

I am using SharePoint 2010 and Excel 2010.

I am writing an Excel VBA code to upload a document to a SharePoint document library. And i am using ThisWorkbook.ContentTypeProperties to assign values to all the SharePoint document library fields as below.

        If Prop.Name = "Uploader Name" Then
        Prop.Value = Range("B4").Value

I am able to upload the file and populate all the fields except the lookup fields in SharePoint. Please let me know how I can assign values to the lookup fields.

Thanks a ton,

Balaji

Answer 1

Hi,

Thank you for your post.
I'm trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience.

Thanks & Regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 2

Thanks Emir.

I would really appreciate if you can help me with this one.

Answer 3

Adding to this query. Any idea how to assign values to the multiple choice fields in sharepoint document library from Excel? I am able to assign values to the choice field with the single option like this, but it is not working for the multiple choice fields.

If Prop.Name = "Tool Used" Then
   Prop.Value = "aaa"

End If

For multiple choice fields i tried the below, but it is not working.

Prop.Value = "aaa","bbb","ccc"

Prop.Value = "aaa";"bbb";"ccc"

Prop.Value = ;#aaa;#bbb;#ccc;#

Answer 4

Hi Balaji1986,

What's in Range("B4").Value?
For lookup field, you need to set the list item ID of the lookup list.

Answer 5

Don't know much about Excel VBA. The format below looks fine.

;#aaa;#bbb;#ccc;#

Are the values aaa, bbb and ccc on the  multiple choice field's choice list? Can you set the multiple values to the multiple choice field with UI? Hope the multiple choice field is created correctly.

Answer 6

@Balaji1986

Would it be possible for you to share your code? I think a lot of people would be grateful (myself included ;)

Answer 7

Yes, the values are in the present in the list. Yes, i am able to set multiple values from UI. Yes, the multiple choice field is created correctly

Answer 8

It is one of the value in the list. I tried with the item id also but did not work

Answer 9

I found the answer by myself for these.

1. Problem - Unable to assign values to lookup field from Exce VBA

Solution - For reading and assigning values to the lookup field. First we need to publish the document information panel form to the document library using InfoPath. Then if we pass the value as a string it will accept.

2. Problem - Unable to assign values to multiple choice field from Excel VBA

Solution - For assigning values to a multiple choice field (passing more than one value) we need to pass the values as an array using split function

This is my code

Sub SetServerProperties()

Dim WB As Workbook
Set WB = ThisWorkbook

iRowNum = 2
iColNum = 1

'To assign values to the excel content type property
    Do While Sheet1.Range("Property_Col").Cells(iRowNum, 1).Value <> ""
        If iRowNum = 5 Then
          WB.ContentTypeProperties(WB.Worksheets("Sheet1").Cells(iRowNum, 1).Value).Value = Split("aaa,bbb", ",", -1, vbBinaryCompare)
          Else
          WB.ContentTypeProperties(WB.Worksheets("Sheet1").Cells(iRowNum, 1).Value).Value = WB.Worksheets("Sheet1").Cells(iRowNum, 1).Offset(0, 1).Value
        End If
        iRowNum = iRowNum + 1
    Loop

End Sub