Help for date re-formatting macro needed

[Udo Froehn]

Hello,
I've got this load of Word documents containing thousands of dates in
format D/M/YY (plain text, no fields involved).
All these dates needs to be re-formatted to YYYY-MM-DD.

I was hoping to find a macro to do this for me, but I couldn't find any.
I'm pretty new to VBA, so can't really just write such a macro my myself
yet. Would you have any code bits or some similar macro I could tweak
to get this done?

Any help greatly appreciated!
Thanks, Udo.

 

[Doug Robbins - Word MVP]

Assuming that all the dates are in this century, use

Dim srange As Range
Dim year As String, month As String, day As String
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}",
MatchWildcards:=True, _
MatchCase:=False, Wrap:=wdFindStop, Forward:=True) = True
Set srange = Selection.Range
year = "20" & Right(srange, 2)
month = Mid(srange, InStr(srange, "/") + 1)
month = Format(Left(month, InStr(month, "/") - 1), "0#")
day = Format(Left(srange, InStr(srange, "/") - 1), "0#")
srange = year & "-" & month & "-" & day
Loop
End With

If there are no dates in future years, but some in the last century, replace

year = "20" & Right(srange, 2)

with

If Val(Right(srange, 2)) > 7 then
year = "19" & Right(srange, 2)
Else
year = "20" & Right(srange, 2)
End If

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Udo Froehn]

Thanks for the quick reply!
Unfortunately, the macro gets stuck in the "Do While" line, the error
message saying (translated into English)
"Error 5560, the text in the field 'Search for' contains an invalid
sample comparison".
From what I get from googling, the regular expression seems to be the
problem, at least for my Word 97. If I replace the expression with some
plain text, the line is processed all right.

Is there a workaround or some other solution?

Thanks, Udo


Am 2007-01-05 20:38 schrieb Doug Robbins - Word MVP:
[...]

With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}",
MatchWildcards:=True, _
MatchCase:=False, Wrap:=wdFindStop, Forward:=True) = True
Set srange = Selection.Range
year = "20" & Right(srange, 2)
month = Mid(srange, InStr(srange, "/") + 1)
month = Format(Left(month, InStr(month, "/") - 1), "0#")
day = Format(Left(srange, InStr(srange, "/") - 1), "0#")
srange = year & "-" & month & "-" & day
Loop
End With [...]

Am 2007-01-05 20:02 schrieb Udo Froehn:

Hello,
I've got this load of Word documents containing thousands of dates in
format D/M/YY (plain text, no fields involved).
All these dates needs to be re-formatted to YYYY-MM-DD.

 

[Klaus Linke]

Hi Udo,

replace the commas in {1,2} with semicolons {1;2}.
For some stupid reason, you need to use the field delimiter for your Windows
language version (as set in the Windows control panel regional settings).

Regards,
Klaus

Thanks for the quick reply!
Unfortunately, the macro gets stuck in the "Do While" line, the error
message saying (translated into English)
"Error 5560, the text in the field 'Search for' contains an invalid sample
comparison".
From what I get from googling, the regular expression seems to be the
problem, at least for my Word 97. If I replace the expression with some
plain text, the line is processed all right.

Is there a workaround or some other solution?

Thanks, Udo


Am 2007-01-05 20:38 schrieb Doug Robbins - Word MVP:
[...]

With Selection.Find
Do While .Execute(FindText:="[0-9]{1,2}/[0-9]{1,2}/[0-9]{2}",
MatchWildcards:=True, _
MatchCase:=False, Wrap:=wdFindStop, Forward:=True) = True
Set srange = Selection.Range
year = "20" & Right(srange, 2)
month = Mid(srange, InStr(srange, "/") + 1)
month = Format(Left(month, InStr(month, "/") - 1), "0#")
day = Format(Left(srange, InStr(srange, "/") - 1), "0#")
srange = year & "-" & month & "-" & day
Loop
End With [...]

Am 2007-01-05 20:02 schrieb Udo Froehn:

Hello,
I've got this load of Word documents containing thousands of dates in
format D/M/YY (plain text, no fields involved).
All these dates needs to be re-formatted to YYYY-MM-DD.

 

[Udo Froehn]

Hi Doug and Klaus,
once I replaced the commas with semicolons, the script ran perfectly.
Thanks a lot for great help!
Udo


Am 2007-01-05 23:30 schrieb Klaus Linke:

Hi Udo,

replace the commas in {1,2} with semicolons {1;2}.
For some stupid reason, you need to use the field delimiter for your Windows
language version (as set in the Windows control panel regional settings).

Regards,
Klaus


"Udo Froehn" <[email protected]> schrieb:

[macro not working in Word 97]