Insert field in formula with VBA

cc85 · #1 02-11-2022, 11:51 AM

Hello,

I am a trainer and in my exercises, I have dates.
Instead of changing the dates manually every year, I have automated them by :
1 - Creating an advanced property "Year" that I change every year (File/Information/Properties/Advanced properties/Customization)

2 - In the body text, at each date :
- Insert/QuickPart/Field/ok
- It indicates me "! unexpected end of formula".
- Right click to "Toggle field codes" : {.=..* MERGEFORMAT }
- I put myself after = then Insert/QuickPart/Field/PropertyDoc/Year : {.=.2021.\* MERGEFORMAT }
- I put myself after 2021 and I add +1, +2 or -1
- Right click and "update fields" recalculates the date.
This gives me { { DOCPROPERTY Year \* MERGEFORMAT } + 1 \* MERGEFORMAT }
3 - Each year I change the date in properties, I recalculate => it's ok

Instead of doing each time the manipulation by hand, I tried a VBA macro :

Code:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:="= ", PreserveFormatting:=Tru

This inserts the calculation field

Code:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:="DOCPROPERTY  Annee ", PreserveFormatting:=True

Which inserts the Year property

But how do I combine the two?

There is a field type wdFieldDocProperty but I don't know how to use it.

Thank you for your solution...

gmayor · #2 02-11-2022, 09:51 PM

Why not simply insert a DATE field (or a CREATEDATE field if it is a template)
{ DATE \@ "yyyy" } or { CREATEDATE \@ "yyyy" } ? This will always show the year (or in the case of the latter the year the document was created.

To insert a DocProperty field

Code:

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldDocProperty, Text:="Annee", PreserveFormatting:=False

cc85 · #3 02-14-2022, 01:24 AM

Thank you
But that's not what I want.
For example, I did an exercise in 2018. All the dates in that exercise are from 2016 to 2024.
I want to give this same exercise now: I need to change all the dates, from 2020 to 2028.
So, I need a formula...
I wish you a good day

gmayor · #4 02-14-2022, 05:06 AM

Frankly I wouldn't do it like that. As you are going to have to change the fields in the documents, I would suggest that you use instead content controls.

If there are nine different years then use nine content controls each with a different title e.g. - Date1 to Date9, where Date1 is the first date i.e. 2020 and Date9 is the last date i.e. 2028. You can use the same field titles wherever you wish to repeat one or more of the dates.

Select the existing date in the document and run Macro1 to create and title the content control in place of the date, This only has to be done once.

Depending on how the dates are currently inserted, it may be possible to automate this to update them all at once, but as shown you put the cursor in a date and run the Macro1

Then it is a simple matter to update all the Date content controls in the document using Macro2.

You can change the defaults in the input boxes as required, but the start value is always the same for each content control in Macro1.

Code:

Sub Macro1()
Const strList As String = "0123456789"
Dim sStart As String, sText As String
Dim iDate As Integer
Dim oRng As Range
Dim oCC As ContentControl

    sStart = InputBox("Enter lowest/first year in the document", "Start Year", "2016")

    Set oRng = Selection.Range
    oRng.MoveStartWhile strList, wdBackward
    oRng.MoveEndWhile strList
    sText = oRng.Text
    iDate = CInt(oRng.Text) - CInt(sStart)
    Set oCC = oRng.ContentControls.Add(wdContentControlRichText)
    With oCC
        .Title = "Date" & CStr(iDate + 1)
        .Tag = .Title
        .Range.Text = sText
        .LockContentControl = True
    End With
    Set oCC = Nothing
    Set oRng = Nothing
End Sub

Sub Macro2()
Dim oCC As ContentControl
Dim sStart As String
    sStart = InputBox("Enter lowest/first year in the document", "Start Year", "2020")
    For Each oCC In ActiveDocument.ContentControls
        If oCC.Title Like "Date?" Then
            oCC.Range.Text = CStr(Val(sStart) + Val(Right(oCC.Title, 1) - 1))
        End If
    Next oCC
    Set oCC = Nothing
End Sub

cc85 · #5 02-14-2022, 07:41 AM

Thanks jmayor,
That's a good solution: I keep the idea and I will adapt it.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula	innkeeper9	Excel	2	09-13-2016 08:59 PM
Insert a field	CepheiMD	Word	2	03-18-2016 01:56 AM
insert database as field	david_89_	Mail Merge	3	03-26-2014 06:02 AM
Formula Field using Field Codes	hunter2193	Word	3	04-05-2013 04:58 AM
Formula to subtract one month from due date field in reminder field	ghumdinger	Outlook	1	10-01-2011 12:09 AM