Automatically update dates using a macro

matthew544 · #1 07-05-2011, 07:51 AM

I am not too familiar with macros, but I am trying to create one that can automatically update dates in a particular document. The document is used for training purposes and contains a large number of different dates. As time passes, all of the dates need to be updated. In other words, I need to change 06/21/2010 to 06/21/2011 and 12/02/1999 to 12/02/2000, and so on. Some dates are also in the MM/DD/YY format. I tried to use a basic find/replace macro (shown below), but ran into problems. Because the macro is replacing all instances of each number, the month and day (as well as other non-date numbers contained in the document) are also being changed. 06/01/08 is turning into 07/02/09, and I don't want that.

If I put the years inside form fields and add a bookmark to each field, is there a way to modify my code so that the find/replace function only works for those particular fields?

Thanks for any help that anyone can provide.

Code:

Sub test1()

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
   .Text = "2011"
   .Replacement.Text = "2012"
   .Forward = True
   .Wrap = wdFindContinue
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = False
   .MatchSoundsLike = False
   .MatchAllWordForms = False

End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
   .Text = "2010"
   .Replacement.Text = "2011"
   .Forward = True
   .Wrap = wdFindContinue
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = False
   .MatchSoundsLike = False
   .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
   .Text = "10"
   .Replacement.Text = "11"
   .Forward = True
   .Wrap = wdFindContinue
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = False
   .MatchSoundsLike = False
   .MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

...etc

End Sub

matthew544 · #2 07-07-2011, 08:39 AM

FYI - I also posted this question at another forum. Please check for responses there before attempting to solve this issue. Thanks.

macropod · #3 07-08-2011, 02:37 AM

Hi Matthew,

Try:

Code:

Sub NextYear()
With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Text = "[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = True
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute
  End With
  Do While .Find.Found
    If Split(.Text, "/")(2) = Format(.Text, "yyyy") Then
      .Text = DateAdd("yyyy", 1, .Text)
    Else
      .Text = Format(DateAdd("yyyy", 1, .Text), "mm/dd/yy")
    End If
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
End Sub

matthew544 · #4 07-08-2011, 06:18 AM

Fantastic. Worked perfectly. Thanks so much.

matthew544 · #5 08-26-2011, 08:26 AM

I apologize for reopening an old thread. Is it possible to modify this macro so that it also updates dates that are in the mm/yyyy or mm/yy formats? I tried adjusting the code that macropod provided, but I couldn't get it to work?

Thanks.

macropod · #6 12-06-2011, 03:19 AM

Hi Matthew,

I hadn't seen your follow-up post until today. You could insert the following code between the existing 'Loop' and 'End With' lines:

Code:

  With .Find
    .Text = "[!/][0-9]{1,2}/[0-9]{2,4}"
    .Execute
  End With
  Do While .Find.Found
    If Split(.Text, "/")(1) = Format(.Text, "yyyy") Then
      .Text = Left(.Text, 1) & Format(DateAdd("yyyy", 1, .Text), "m/yyyy")
    Else
      .Text = Left(.Text, 1) & Format(DateAdd("yyyy", 1, .Text), "m/yy")
    End If
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatically update web images on Powerpoint	thetodd14	PowerPoint	0	05-04-2011 10:37 AM
How to update automatically the “file name” as we do for the “Date”?	Jamal NUMAN	Word	2	01-06-2011 02:43 PM
Letter Head Template - Update all other templates automatically	nirok	Word	1	11-25-2010 03:14 PM
How to set links that automatically update	tkelly5446	Project	1	11-17-2010 04:26 AM
Automatically update text changes in Word - how do I do it?	jonrm	Word	2	05-21-2009 08:49 AM