Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-15-2020, 09:42 AM
Legal Learning Center Legal Learning Center is offline AutoOpen code to change date code to date text Windows 10 AutoOpen code to change date code to date text Office 2016
Novice
AutoOpen code to change date code to date text
 
Join Date: Feb 2020
Location: Boulder, Colorado
Posts: 3
Legal Learning Center is on a distinguished road
Default AutoOpen code to change date code to date text


Appreciate any assistance with creating a macro when a Word document is opened, it will search for and find anywhere (body of the document, headers or footers) the date code and change it to date text.


Thank you!
Legal Learning Center
Reply With Quote
  #2  
Old 02-15-2020, 10:00 AM
Charles Kenyon Charles Kenyon is offline AutoOpen code to change date code to date text Windows 10 AutoOpen code to change date code to date text Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

First, that would fix the current date in the document. It would not be the date that the document was created.
Second, use the CreateDate field in a template instead. Then, you will not need a macro. The field will update each time a document is created from the template and remain fixed. You are trying to reinvent the wheel.
Reply With Quote
  #3  
Old 02-15-2020, 10:31 AM
Legal Learning Center Legal Learning Center is offline AutoOpen code to change date code to date text Windows 10 AutoOpen code to change date code to date text Office 2016
Novice
AutoOpen code to change date code to date text
 
Join Date: Feb 2020
Location: Boulder, Colorado
Posts: 3
Legal Learning Center is on a distinguished road
Default

Thank you






This macro would be used for old documents that have the date code in them already and we don't want the date code. Instead we want to replace that code with date text. We have a lot of OLD documents with this pesky code and we want to have it change to date text when opening the document. Does this make sense?
Reply With Quote
  #4  
Old 02-15-2020, 01:41 PM
macropod's Avatar
macropod macropod is offline AutoOpen code to change date code to date text Windows 7 64bit AutoOpen code to change date code to date text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

No, that makes no sense at all, because you have failed to realise what the effect would be, which is to lock in whatever date you process those documents on - not the dates the documents were created, printed or saved (whichever is relevant).

Any macro you run against the document to unlink DATE fields will only do so after those fields have updated. In other words, you will have already lost the date you're trying to preserve. That is the fundamental flaw in relying on code that simply unlinks existing DATE fields. You may be able to correct the problem by changing the DATE fields to CREATEDATE fields. Unlike DATE fields, CREATEDATE fields only update when the document is first saved or is saved via Save As.

Other possibilities for retrieving (but not necessarily retaining) the relevant dates are PRINTDATE fields (which record when the document was last printed) or SAVEDATE fields (which record when the document was last saved). Do note that any change to the field type will require the document to be re-saved, which will inevitably change the results of a SAVEDATE field. Of course, you might use a SAVEDATE field to capture the date the document was last saved, then unlink that field before re-saving the document.

For example, to replace DATE fields with CREATEDATE fields anywhere in the document (other than textboxes), you might use code like:
Code:
Sub GetDateCreated()
Application.ScreenUpdating = False
Dim Sctn As Section, HdFt As HeaderFooter, Fld As Field
With ActiveDocument
  For Each Fld In .Range.Fields
    If Fld.Type = wdFieldDate Then
      Fld.Code.Text = Replace(Fld.Code.Text, "DATE", "CREATEDATE")
    End If
  Next Fld
  For Each Sctn In .Sections
    For Each HdFt In Sctn.Headers
      With HdFt
        If .Exists Then
          If Sctn.Index = 1 Or .LinkToPrevious = False Then
            For Each Fld In .Range.Fields
              If Fld.Type = wdFieldDate Then
                Fld.Code.Text = Replace(Fld.Code.Text, "DATE", "CREATEDATE")
              End If
            Next Fld
          End If
        End If
      End With
    Next HdFt
    For Each HdFt In Sctn.Footers
      With HdFt
        If .Exists Then
          If Sctn.Index = 1 Or .LinkToPrevious = False Then
            For Each Fld In .Range.Fields
              If Fld.Type = wdFieldDate Then
                Fld.Code.Text = Replace(Fld.Code.Text, "DATE", "CREATEDATE")
              End If
            Next Fld
          End If
        End If
      End With
    Next HdFt
  Next Sctn
End With
Application.ScreenUpdating = True
End Sub
Whichever approach you take, you'll still need to verify that the new dates are correct - by reference to the original documents. Assuming you have those, you'll know what the dates should be changed back to, in which case you could use code like:
Code:
Sub DateCorrector()
Application.ScreenUpdating = False
Dim StrDt As Variant, Sctn As Section, HdFt As HeaderFooter, Fld As Field, Rng As Range
StrDt = InputBox("Please Input the Correct Date", "Date Fixer")
If IsDate(StrDt) Then
  StrDt = CDate(StrDt)
Else
  MsgBox "Not a valid date! Exiting", vbCritical
End If
With ActiveDocument
  For Each Fld In .Range.Fields
    If Fld.Type = wdFieldDate Then
      Set Rng = Fld.Result
      Fld.Unlink
      Rng.Text = Format(StrDt, "MMM-DD-YYYY")
    End If
  Next Fld
  For Each Sctn In .Sections
    For Each HdFt In Sctn.Headers
      With HdFt
        If .Exists Then
          If Sctn.Index = 1 Or .LinkToPrevious = False Then
            For Each Fld In .Range.Fields
              If Fld.Type = wdFieldDate Then
                Set Rng = Fld.Result
                Fld.Unlink
                Rng.Text = Format(StrDt, "MMM-DD-YYYY")
              End If
            Next Fld
          End If
        End If
      End With
    Next HdFt
    For Each HdFt In Sctn.Footers
      With HdFt
        If .Exists Then
          If Sctn.Index = 1 Or .LinkToPrevious = False Then
            For Each Fld In .Range.Fields
              If Fld.Type = wdFieldDate Then
                Set Rng = Fld.Result
                Fld.Unlink
                Rng.Text = Format(StrDt, "MMM-DD-YYYY")
              End If
            Next Fld
          End If
        End If
      End With
    Next HdFt
  Next Sctn
End With
Application.ScreenUpdating = True
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-15-2020, 06:46 PM
Charles Kenyon Charles Kenyon is offline AutoOpen code to change date code to date text Windows 10 AutoOpen code to change date code to date text Office 2019
Moderator
 
Join Date: Mar 2012
Location: Sun Prairie, Wisconsin
Posts: 9,081
Charles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant futureCharles Kenyon has a brilliant future
Default

Quote:
Originally Posted by Legal Learning Center View Post
Thank you

This macro would be used for old documents that have the date code in them already and we don't want the date code. Instead we want to replace that code with date text. We have a lot of OLD documents with this pesky code and we want to have it change to date text when opening the document. Does this make sense?

Yes, this makes sense. However, the date will be the date that you run the macro, not the date that was originally in the document. See Paul's comments and macros.
Reply With Quote
  #6  
Old 02-22-2020, 01:35 PM
Legal Learning Center Legal Learning Center is offline AutoOpen code to change date code to date text Windows 10 AutoOpen code to change date code to date text Office 2016
Novice
AutoOpen code to change date code to date text
 
Join Date: Feb 2020
Location: Boulder, Colorado
Posts: 3
Legal Learning Center is on a distinguished road
Default

Thank you,
It makes sense. Once the document is opened that has the date code, it is going to change before any code is run on it. I can unlink it at that time so going forward it will be text.


Is there a way through a group policy or option to disable date code going forward?
Reply With Quote
  #7  
Old 02-22-2020, 02:24 PM
macropod's Avatar
macropod macropod is offline AutoOpen code to change date code to date text Windows 7 64bit AutoOpen code to change date code to date text Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Legal Learning Center View Post
Is there a way through a group policy or option to disable date code going forward?
Not really. The best you can do is use a CREATEDATE field so the date remains tied to whatever date the document is first saved, or saved via Save As. That way, whoever creates the document should at least notice that the date on a document that takes some days to complete becomes out of date.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change font color if date in column F is prior to today's date. kevinbradley57 Excel Programming 1 12-05-2018 07:35 PM
AutoOpen code to change date code to date text VBA Code to search for field codes with certain text before the Field code and to change style welcometocandyland Word VBA 4 02-08-2017 06:53 PM
AutoOpen code to change date code to date text VBA code for inserting a future date cosmopolitan Word VBA 1 08-14-2013 01:58 PM
Change format of date when using Now function in VB code Bondai Excel Programming 2 03-02-2012 05:09 PM
AutoOpen code to change date code to date text Imported message date change to today's date promark Outlook 1 12-23-2005 07:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft