+ Reply to Thread
Results 1 to 2 of 2

Read and write to SharePoint ContentTypeProperties when property values come from TermSet

  1. #1
    Registered User
    Join Date
    05-02-2016
    Location
    Denver Colorado
    MS-Off Ver
    Office 365
    Posts
    2

    Read and write to SharePoint ContentTypeProperties when property values come from TermSet

    This is a bit complex and I have spent weeks cobbling together something that almost works. I have created an Excel macro that is converting Word docs from one template and format structure (current) to a new template and format structure (new).
    The current docs are on a network share and the new docs will be on a SharePoint Library.

    The point of failure is that some of the SharePoint properties won't update from the doc to the library.

    The macro opens the Word docs, one at a time, from a specified folder. For each doc I do the following:
    1. Run 8 Word macros (These are in the Startup template for Word and available in all our docs) They clean up sloppy formatting.
    2. Copy the content to the New template.
    3. Run 5 more Word macros to format new document.
    4. Rename and save the new document to SharePoint library (file version is 0.1)

    Ok now it gets fun. Once the document is added to SharePoint the SharePoint document properties are added to the document.
    There are four properties defined in the SharePoint library:
    • Client = specified by user on form
    • Business = specified by user on form
    • Process = specified by user on form
    • Workflow Status = “Published” (Constant for this macro)

    The only one I can update to SharePoint is Workflow Status. This property is not tied to TermSet values.
    So ignore that one and let's focus on the other three.
    I can see, but not update, the SharePoint defined properties in the Properties Document Panel (in the Procedure Properties - Server section). (The other property can be updated from the Doc Panel).
    I can see and update all properties from SharePoint using the Properties window and selecting the value from the attached TermSet values.
    I found some code that enabled me to write a macro that updates the three properties in the Word Doc using the TermSet GUID but the values do not transfer to SharePoint. That code looks like this. (Hard coded sample code for one property)
    Please Login or Register  to view this content.
    Note: The ActiveDocument.ContentTypeProperties("Process").value property is a collection

    **THE PROBLEM**
    When I run the code above I can see the value for the property in the Document Panel but when I check the document in the value disappears.

    If I use a for each loop in a macro I can read the value I set in SharePoint.
    Please Login or Register  to view this content.
    When I run the code above the For loop runs 4 times. The first item is the English value of the property, the second is the GUID, the next two are blank and I don't know what should be there if anything.

    What I need is a way to update the property value so that it appears in SharePoint and in the Document Panel. I am not against using PowerShell or some other method and calling that from the VBA macro. I am not as familiar with other tools so would need some guidance.

    Now for the good news. The properties for all files in a source folder are the same, which means a workable solution may be a separate property update routine run post conversion.
    Since this is a batch process running on several hundred files in each of 50 or so folders the more automation the better. A two step solution flow would be...
    1. Create new Word docs and save to SharePoint.
    2. Once all docs are done, run a routine to update the properties for each file in the SharePoint folder.

    -Thanks for reading all that and any input would be appreciated

  2. #2
    Registered User
    Join Date
    05-02-2016
    Location
    Denver Colorado
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Read and write to SharePoint ContentTypeProperties when property values come from Term

    Some new information.
    When I run the read For Each loop on a property set in SharePoint it only loops through 2 times showing the English value and the GUID. When I run the same code on the property I set using the VBA macro it loops 4 times as described above. My guess is that I am not setting the value correctly. I have tried to access the unset property value collection directly using subscripts like this .Value(1) but I get run-time error 451: Property let procedure not defined and property get procedure did not return an object. MSDN says it is because the object is not a collection.

    More info.
    When I read the value of the property set in SharePoint I can use subscripts. .Value(0) Using the .Add method to set the value returns Run-time error 424 Object required.

    When I manually add a file to SharePoint and open it in Word the properties on the Document Panel are updatable. Unlike when I add the file from VBA using SaveAs2. Unfortunately, when I try to use the macro to update the script in the manually added doc I get the same result.
    Last edited by John3PG; 05-03-2016 at 10:44 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Checking to see if a file has been opened in Read/Write or Read Only
    By brokenbiscuits in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-13-2014, 09:16 AM
  2. Trying to update a sharepoint property field that requires a date entry
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2013, 01:28 AM
  3. Set Sharepoint Document property via VB
    By Yoam69 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-14-2012, 02:24 PM
  4. How to write a macro so that all the radio button values should read from a poll ?
    By ravikumar00008 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-14-2012, 11:11 AM
  5. Insert Sharepoint Doc Property/metadata into Excel sheet
    By smokebreak in forum Excel General
    Replies: 1
    Last Post: 07-29-2010, 12:35 PM
  6. Read & Write Title Property of txt file
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2010, 04:13 PM
  7. Read/Write Values
    By edwardt4482 in forum Excel General
    Replies: 0
    Last Post: 10-24-2007, 09:43 AM
  8. How can a file be converted from Read-Only to Read/Write
    By Jim in Apopka in forum Excel General
    Replies: 2
    Last Post: 11-19-2005, 01:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1