Setting Custom Document properties that will be used in Sharepoint

[Greatheights]

Good day, all,

I'm trying to create and set serveral custom document properties on documents. Once uploaded to a Sharepoing library, these properties are used to sort the documents and move them through a workflow. My object is to allow another user to input the values using a user form, so that the properties are already there when the document is added to Sharepoint.

All the variables I'm using a Public in the module that call this DocProps Sub, and are set through a user form that's in a different Sub.

When my bigger macro calls DocProps, I'm getting an "object required" run-time error on the first line of code.

<code>
Sub DocProps()
'Adds the Line of Business Property and defines it as the pLine variable
Wb.CustomDocumentProperties.Add _
Name:="Line of Business", _
LinkToContent:=False, _
Type:=DocType, _
Value:=pLine

'adds the Company name property and defines it as the pCompany variable
Wb.CustomDocumentProperties.Add _
Name:="Company Name", _
LinkToContent:=False, _
Type:=DocType, _
Value:=pCompany

'adds the Year property and defines it as the pYear variable
Wb.CustomDocumentProperties.Add _
Name:="Year", _
LinkToContent:=False, _
Type:=DocType, _
Value:=pYear

'adds the Time Period property and defines it as the pPeriod variable
Wb.CustomDocumentProperties.Add _
Name:="Time Period", _
LinkToContent:=False, _
Type:=DocType, _
Value:=pPeriod

'adds the Date Loaded property and defines it as the pLoaded variable
Wb.CustomDocumentProperties.Add _
Name:="Line of Business", _
LinkToContent:=False, _
Type:=DocType, _
Value:=pLoaded

'adds Number of Policies property and defines it as the value from cell B6
Wb.CustomDocumentProperties.Add _
Name:="Number of Policies", _
LinkToContent:=False, _
Type:=DocType, _
Value:=Range("B6").Value

End Sub

</code>

This is my first time trying to pass variables through to custom properties like this, so I'm guessing my syntax is just wrong, but I've been unable to find any tips by searching. The only possibility I've found is that I should be using "ContentTypeProperty" instead of "CustomDocumentProperties" by I tried swapping that out and got the same result.

This is all being done in Excel 2007

Thanks for the help.

 

[Greatheights]

Ok, well, I eventually found out that you can not create Sharepoint properties via VBA. You can only set them if they are already present in the document. That's fine, I'll save the macro workbook in the Sharepoint library so it has those properties already.

So now I've got this code to set the values of the properties:

<code>

Sub DocProps()
ActiveWorkbook.ContentTypeProperties("Line of Business").Value = pLine
ActiveWorkbook.ContentTypeProperties("Company Name").Value = pCompany
ActiveWorkbook.ContentTypeProperties("Year").Value = pYear
ActiveWorkbook.ContentTypeProperties("Time Period").Value = pPeriod
ActiveWorkbook.ContentTypeProperties("Date Loaded").Value = pLoaded
ActiveWorkbook.ContentTypeProperties("Number of Policies").Value = Range("B5").Value
If pPriority = True Then
ActiveWorkbook.ContentTypeProperties("Priority").Value = pPriority
End If
EndSub
</code>

Now, everything is working just fine. However, when the macro gets to

<code>
ActiveWorkbook.ContentTypeProperties("Company Name").Value = pCompany
</code>

I get a Run-time error that says "An item with the following index does not exist in the collection" with a different index each time.

The really weird thing is if I opt to debug instead of end, if I just hit F5 to continue the code, it continues just fine. All the properties, including the Company Name get filled in correctly.

Any idea what is causing that run-time error and if there is anything I can do to stop it? I obviously don't want my users to have to go into debug mode in order to use the workbook.

 

[HelenR]

Thanks so much, I've been trying to do exactly the same thing!