26 Temmuz 2012 Perşembe 16:33
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,
27 Temmuz 2012 Cuma 12:47Moderatör
27 Temmuz 2012 Cuma 14:31
I would really appreciate if you can help me with this one.
31 Temmuz 2012 Salı 09:08
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"
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;#
08 Ağustos 2012 Çarşamba 00:37
What's in Range("B4").Value?
For lookup field, you need to set the list item ID of the lookup list.
08 Ağustos 2012 Çarşamba 00:54
Don't know much about Excel VBA. The format below looks fine.
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.
10 Ağustos 2012 Cuma 09:51
Would it be possible for you to share your code? I think a lot of people would be grateful (myself included ;)
12 Ağustos 2012 Pazar 20:04Yes, 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
12 Ağustos 2012 Pazar 20:06It is one of the value in the list. I tried with the item id also but did not work
12 Ağustos 2012 Pazar 20:18
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
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)
WB.ContentTypeProperties(WB.Worksheets("Sheet1").Cells(iRowNum, 1).Value).Value = WB.Worksheets("Sheet1").Cells(iRowNum, 1).Offset(0, 1).Value
iRowNum = iRowNum + 1
- Yanıt Olarak İşaretleyen Balaji Murugaiyan 12 Ağustos 2012 Pazar 20:18