Writing to Word "Publish Date" Document Property

[elemenopee]

I am trying to use VBA to update the "Publish Date" built in document property in Word. I am able to write to other document properties without issue, but publish date is not working. Any help you can provide would be greatly appreciated. Below is sample code I have that is not working, but works for updating other document properties. I have tried ever variation of this I can think of. Thanks in advance for your help!


SoWContentDate = Format(Now, "mmmm dd, yyyy")
' This works fine


ActiveDocument.BuiltInDocumentProperties("Publish Date") = _ SoWCoverPageDate
' This statement does not work

[Frosty]

How do you know "Publish Date" is a built in document property? I don't see that document property in Word 2010.

[elemenopee]

We have a template that uses this property as well as "author", "keywords", and "title". All are inserted into the document from Insert > Quick Parts > Document Properties. I am able to update author, keywords, and title through VBA with no issue.

[Frosty]

Yes, I can see how that would be confusing when the native interface so clearly displays them together. However, what's really going on there is a lot more complicated.

The short answer is that the builtin document properties of Author, Keywords and Title have been around for a long time. So MS calls those "coreproperties" in the XML, and gives you access to them via the original BuiltInDocumentProperties collection.

However, it looks like some of these newer exist in a different area (CoverPageProps), and additionally MS doesn't provide easy access via the out-of-date-but-still-backwards-compatible BuiltinDocumentProperties collection.

I'm not sure how much of this technical stuff you can/want to digest... and I'm not sure I can provide you with code to do exactly what you want to do, because it gets into the XML construction of the document, and how your documents are set (compatibility mode, Word version, etc) up may or may not work with this code.

But here's a best guess...


Public Sub DemoPublishDate()
' the current value (notice the standard format is "yyyy-mm-ddT00:00:00"
' when a content control has last set the value
MsgBox fGetPublishDate_XMLNode
' update the value and display that we updated it
MsgBox fGetPublishDate_XMLNode(Format(Now, "m/d/yyyy"))
End Sub

Pass in a value to change it, otherwise returns the current value


Public Function fGetPublishDate_XMLNode(Optional sUpdateTo As String) As String
Dim sNameSpace As String
Dim oDoc As Document
Dim oCoverPagePropsNode As CustomXMLNode
Dim oPublishDate As CustomXMLNode
Dim i As Integer
Dim sRet As String
' this is the name space for the CoverPage Properties (where Publish Date is located)
sNameSpace = "http://schemas.microsoft.com/office/2006/coverPageProps"
Set oDoc = ActiveDocument
' we look for it by the name space, and return the first item found's document element node
Set oCoverPagePropsNode = oDoc.CustomXMLParts.SelectByNamespace(sNameSpace)(1).DocumentElement
' loop through all the child nodes of our CoverPageProps Node
For i = 1 To oCoverPagePropsNode.ChildNodes.Count
' find the one with the PublishDate BaseName
If oCoverPagePropsNode.ChildNodes(i).BaseName = "PublishDate" Then
Set oPublishDate = oCoverPagePropsNode.ChildNodes(i)
' if we passed in a value to update, then do it
If sUpdateTo <> "" Then
oPublishDate.Text = sUpdateTo
End If
' and return the value to our function
sRet = oPublishDate.Text
Exit For
End If
Next
' return the value as our function
fGetPublishDate_XMLNode = sRet
End Function

[Frosty]

For your own edification, you should know that those built-in content controls you are inserting via the Quick Parts menu are *CONSIDERABLY* more complicated than the custom content controls you might insert via the Developer tab. It's not enough (as I discovered while researching this) to simply insert your own content control and just name it "Publish Date" -- you actually have to connect it in the back end via something called the .XMLMapping property of the Content Content control.

This area of Word 2010 is not something I've really explored... although I know Greg Maxey has done a lot of work with content controls.

[elemenopee]

Thank you Frosty. This is tremendously helpful. I've modified your code a bit and have the publish date updating now.

At the risk of overstaying my welcome :) I'd like to ask another question. I am also trying to update the "Company Email" and "Company Phone" properties, both of which appear to be part of the XML schema. Below is my code. Publish date is working correctly, but company phone and company email are not. I'm guessing I don't have the basename correct. I've tried of of the options I can think of. What am i missing?

It should go without saying I'm a VBA newb.


Dim strNameSpace As String
Dim objCoverPagePropsNode As CustomXMLNode
Dim objPublishDate As CustomXMLNode
Dim objCompanyPhone As CustomXMLNode
Dim objCompanyEmail As CustomXMLNode
Dim i As Integer
Dim i1 As Integer
Dim i2 As Integer
strNameSpace = 'removed the schema URL so I could post
Set objCoverPagePropsNode = ActiveDocument.CustomXMLParts.SelectByNamespace(strNameSpace)(1).DocumentEl ement
For i = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i).BaseName = "PublishDate" Then
Set objPublishDate = objCoverPagePropsNode.ChildNodes(i)
objPublishDate.Text = strSoWCoverPageDate
Exit For
End If
Next
' Set AMPhone = ActiveDocument.CustomXMLParts.Add("<CompanyPhone>strAMPhone</CompanyPhone>")
For i1 = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i1).BaseName = "CompanyPhone" Then
MsgBox "Found it"
Set objCompanyPhone = objCoverPagePropsNode.ChildNodes(i1)
objCompanyPhone.Text = strAMPhone
Exit For
End If
Next
For i2 = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i2).BaseName = "CompanyEmail" Then
Set objCompanyEmail = objCoverPagePropsNode.ChildNodes(i2)
objCompanyEmail.Text = strAMEmail
Exit For
End If
Next

[Frosty]

Couple of pointers, since I don't have a lot of time.

1. You don't need to use different integer variables solely for the purpose of being a counter. It will reset when you get to the next For loop when you say For i = 1 to yada yada
So you can get rid of i1 and i2 variables, and simply use i throughout.

2. This is a great time to explore using the Locals Window within VBA. This is primarily how I found the information needed to give you the original code. Set a breakpoint in your routine (F9 key) right after the line where you set the objCoverPagePropsNode. Then view the locals window, and explore that object. You'll quickly see the ChildNodes, which you can expand and find the base names available (I think there are 6 child nodes).

You may be a vba newb, but you've clearly got some programming experience somewhere, if you feel confident enough to start changing variable prefixes ;)


Incidentally, use of breakpoints (putting your cursor somewhere and pressing F9) removes the need to use lines of code like msgbox "Found It".

F8 is also useful, as that lets you step through code. Combined with the locals window, it's a lot better than throwing stuff against a wall to see what sticks.

[Frosty]

Personally, I'd probably right a function which I could pass in the proper BaseName, and either return or update the value. But I don't have the time to give you a sample of that, at the moment.

You're not over-staying your welcome-- that's what this forum is for :)

[Paul_Hossler]

Is this different than using .CustomDocumentProperties?



Option Explicit

Sub Question()
ActiveDocument.CustomDocumentProperties.Add _
Name:="PublishDate", _
LinkToContent:=False, _
Value:=Format(Now, "yyyy-mm-ddT00:00:00"), _
Type:=msoPropertyTypeString
MsgBox ActiveDocument.CustomDocumentProperties("PublishDate")
End Sub



Paul

[Frosty]

Paul,

I don't believe so... since these are actually an established new area of properties, rather than a custom property of any name. The PublishDate, CompanyName etc stuff already exists.

Check out the Quick Parts area the OP describes and the content control inserted when using Publish Date.

[gmaxey]

I realize that I am Johnny Come Late to this thread, still this might help.

When you are working with the CustomXMLParts, the real "key to the kingdom" so to speak is utilizing the "Xpath." Run the following code on any active document:


Sub ScratchMacro()
Dim oCustXMLPart As CustomXMLPart
Dim oNodeXMLPartRoot As CustomXMLNode
Dim lngIndex As Long
For lngIndex = 1 To 3 'The tree built-in parts.
Set oCustXMLPart = ActiveDocument.CustomXMLParts(lngIndex)
Debug.Print oCustXMLPart.NamespaceURI & " " & oCustXMLPart.ID
Set oNodeXMLPartRoot = oCustXMLPart.DocumentElement
Debug.Print "Root base name: " & oNodeXMLPartRoot.BaseName
' Call a recursive procedure to list node data.
Iterator oNodeXMLPartRoot
Debug.Print vbLf
Next lngIndex
lbl_Exit:
Exit Sub
End Sub

Sub Iterator(ByRef Element As CustomXMLNode)
Dim oNodeChild As CustomXMLNode
Dim oNodeChild2 As CustomXMLNode
For Each oNodeChild In Element.ChildNodes
If oNodeChild.NodeType = msoCustomXMLNodeElement Then
Debug.Print "Element base name: " & oNodeChild.BaseName & " XPath: " & oNodeChild.XPath & " Text: "; oNodeChild.Text
' Recursive condition.
If oNodeChild.HasChildNodes Then
Iterator oNodeChild
End If
End If
Next
lbl_Exit:
Exit Sub
End Sub


Now you know the XPath for every node you would want to work with and your procedure can be vastly simplified:


Sub Test()
Dim oCustXMLPart As CustomXMLPart
Dim oNode As CustomXMLNode
Set oCustXMLPart = ActiveDocument.CustomXMLParts.SelectByID("{55AF091B-3C7A-41E3-B477-F2FDAA23CFDA}")
Set oNode = oCustXMLPart.SelectSingleNode("/ns0:CoverPageProperties[1]/ns0:PublishDate[1]")
oNode.Text = "8/11/2012"
Set oNode = oCustXMLPart.SelectSingleNode("/ns0:CoverPageProperties[1]/ns0:CompanyPhone[1]")
oNode.Text = "867-5309"
Set oNode = oCustXMLPart.SelectSingleNode("/ns0:CoverPageProperties[1]/ns0:CompanyEmail[1]")
oNode.Text = "acme hardward@hardward.net"
lbl_Exit:
Set oCustXMLPart = Nothing
Set oNode = Nothing
Exit Sub
End Sub

BTW, you all might find this useful: http://gregmaxey.mvps.org/word_tip_pages/cc_var_bm_doc_prop_tools_addin.html and there is a vastly improved (IMHO) version coming shortly!!

And oh!!, don't feel bad about being a newbie. I'm just a bumbling novice myself, but it sure feels good when I can school a Titan like Jason. :funnyashe


Thank you Frosty. This is tremendously helpful. I've modified your code a bit and have the publish date updating now.

At the risk of overstaying my welcome :) I'd like to ask another question. I am also trying to update the "Company Email" and "Company Phone" properties, both of which appear to be part of the XML schema. Below is my code. Publish date is working correctly, but company phone and company email are not. I'm guessing I don't have the basename correct. I've tried of of the options I can think of. What am i missing?

It should go without saying I'm a VBA newb.


Dim strNameSpace As String
Dim objCoverPagePropsNode As CustomXMLNode
Dim objPublishDate As CustomXMLNode
Dim objCompanyPhone As CustomXMLNode
Dim objCompanyEmail As CustomXMLNode
Dim i As Integer
Dim i1 As Integer
Dim i2 As Integer
strNameSpace = 'removed the schema URL so I could post
Set objCoverPagePropsNode = ActiveDocument.CustomXMLParts.SelectByNamespace(strNameSpace)(1).DocumentEl ement
For i = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i).BaseName = "PublishDate" Then
Set objPublishDate = objCoverPagePropsNode.ChildNodes(i)
objPublishDate.Text = strSoWCoverPageDate
Exit For
End If
Next
' Set AMPhone = ActiveDocument.CustomXMLParts.Add("<CompanyPhone>strAMPhone</CompanyPhone>")
For i1 = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i1).BaseName = "CompanyPhone" Then
MsgBox "Found it"
Set objCompanyPhone = objCoverPagePropsNode.ChildNodes(i1)
objCompanyPhone.Text = strAMPhone
Exit For
End If
Next
For i2 = 1 To objCoverPagePropsNode.ChildNodes.Count
If objCoverPagePropsNode.ChildNodes(i2).BaseName = "CompanyEmail" Then
Set objCompanyEmail = objCoverPagePropsNode.ChildNodes(i2)
objCompanyEmail.Text = strAMEmail
Exit For
End If
Next

[Frosty]

Oh that's good stuff, Greg. I figured you had something like this already, so thanks for weighing in. Now we all get to learn!

Some of that stuff makes me nervous (the .SelectById method which is clearly using some kind of hash key might mean the code only works for a document created by a specific version of Word), but at least with your iterator code, you can clearly troubleshoot it.

Thanks again!

[gmaxey]

Jason,

Good point! Actually in this case you could simply Set oCustXMLPart = ActiveDocument.CustomXMPart(1) '2, or 3 as applicable as the built-ins are always the same.

I only used ID because it was on my mind from the work on the new add-in I'm working on.

[gmaxey]

I had to dig this out of the add-in that I am working. In it, the cover page properties are listed with spaces (e.g, Company Phone instead of CompanyPhone). So:


Option Explicit

Sub Demo()
WriteValueToCoverPagePropertiesCustXMLPart "Company Phone", "867-5309"
End Sub

Sub WriteValueToCoverPagePropertiesCustXMLPart(ByRef strProp As String, strText As String)
Dim oCustPart As CustomXMLPart
Dim oNode As CustomXMLNode
Dim strXPath As String
strProp = Replace(strPart, " ", "")
Select Case strProp
Case "Abstract"
strXPath = "/ns0:CoverPageProperties[1]/ns0:Abstract[1]"
Case "PublishDate"
strXPath = "/ns0:CoverPageProperties[1]/ns0:PublishDate[1]"
Case "CompanyAddress"
strXPath = "/ns0:CoverPageProperties[1]/ns0:CompanyAddress[1]"
Case "CompanyPhone"
strXPath = "/ns0:CoverPageProperties[1]/ns0:CompanyPhone[1]"
Case "CompanyFax"
strXPath = "/ns0:CoverPageProperties[1]/ns0:CompanyFax[1]"
Case "CompanyEmail"
strXPath = "/ns0:CoverPageProperties[1]/ns0:CompanyEmail[1]"
Case Else
Exit Sub
End Select
Set oCustPart = ActiveDocument.CustomXMLParts(3)
Set oNode = oCustPart.SelectSingleNode(strXPath)
oNode.Text = strText
Set oCustPart = Nothing
Set oNode = Nothing
End Sub

[Frosty]

Just note that there's a slight mistake in your code, in that you pass in "Company Phone" but your case statement contains "CompanyPhone" .. so nothing would happen.

This is one of those times when I might prefer to use an enumerator in something more than a demo version, since I don't think the xpath would change, right?

[gmaxey]

Jason,

No:

strProp = Replace(strPart, " ", "")

The mistake was not changing

(strPart, " ", "") to (strProp, " ", "")
:-(

[elemenopee]

Many thanks for the replies. I've got my macro working exactly as I wanted.