Please Help. Future date macro that updates automatically

[MartyC]

Hi All

New user here and complete noob to macros, so please forgive me if I don't have the right terminology.

I work for a homeless services organisation and we have a lot of men who stay with us each night. One of our partner organisations have to receive paperwork of us each day. ( why this has not went digital, i don't know )

Anyway, each day, the staff have to print out about 30 copes of this 7 page form, that has various fields, and the homeless guys details have to be hand written in etc and its very time consuming. There are also many different fields for todays date, and tomorrows date (indicating an overnight stay).

Unfortunately, we are told to use these forms by a parent organisation so we can't really simplify things as it is all templates.

However, I have figured out a way to make things faster to process and save a lot of writing, but I have an issue with just one thing, and this is where you lovely folk come in.

There is a section that asks for date of stay and date of departure. I can use the insert date and update automatically function, and that works fine. As for tomorrows date , I can find the macro that lets me insert todays date plus 1. Now when I opened one of the documents today to check, todays date was showing where it should, indicating the insert date and time function is working as it should, however, the macro didn't update automatically, to show tomorrows date, where its needed, and I'm wondering, can there be something added to the one I have, to make it update automatically also/ in other words, any time I open the document, it will always show tomorrows date where it should? The code I have is as follows...

Sub InsertFutureOrPastDate()
Dim strNumberOfDays As String

' Input the number of days you want to insert from today to the future or past date.
strNumberOfDays = InputBox("Please input the number of days you want to insert", "future or past date", "Input here.For exemple,input 1 to insert the date of tomorrow")
' Insert the future or past date according to the inputed number of days
If strNumberOfDays <> "" Then
Selection.TypeText Text:=Format(Date + strNumberOfDays, "dd/mm/yyyy")
End If
End Sub

Any help or advice would be great.

Thank you so much in advance...


Marty

[gmayor]

If this is a document that you are opening and printing then a Date field will update when the document is opened as it reads the system date from the computer. For tomorrow's date, you need a calculated date field and that is covered by Paul Edstein's excellent work on dates linked from Insert a past or future date

The example shows a Delay of 14 days and refers to the CREATEDATE field (which would be appropriate if the document was a template and you were creating new documents each time) Change the instances of CREATEDATE to DATE and the Delay to 1.

[Guessed]

It looks like you are using macros anyway so I would do this with Content Controls and macros in the ThisDocument module. See attached demo document.
The code used is shown below to go along with a bunch of Content Controls I put into the demo document.

Private Sub Document_ContentControlOnEnter(ByVal thisCC As ContentControl)
  If thisCC.Title = "Today" And thisCC.ShowingPlaceholderText Then
    If thisCC.XMLMapping.IsMapped Then
      thisCC.XMLMapping.CustomXMLNode.Text = Format(Now, thisCC.DateDisplayFormat)
    End If
  ElseIf thisCC.Title = "Stay Days" And thisCC.ShowingPlaceholderText Then
    thisCC.Range.Text = InputBox("Please input the number of days for the stay duration", "Stay Duration (in days)", "1")
  End If
End Sub

Private Sub Document_ContentControlOnExit(ByVal thisCC As ContentControl, Cancel As Boolean)
  Dim aCC As ContentControl, iDays As Integer, sDateFormat As String, dtEnd As Variant, sFormat As String
  If thisCC.Title = "Today" Then
    If Not thisCC.ShowingPlaceholderText Then
      iDays = InputBox("Please input the number of days for the stay duration", "Stay Duration (in days)", "1")
      For Each aCC In ActiveDocument.SelectContentControlsByTitle("Stay Days")
        aCC.Range.Text = iDays
      Next aCC
      sFormat = thisCC.DateDisplayFormat
      thisCC.DateDisplayFormat = "d MMM yyyy"
      dtEnd = DateAdd("d", iDays, thisCC.Range.Text)
      For Each aCC In ActiveDocument.SelectContentControlsByTitle("Exit Date")
        sDateFormat = aCC.DateDisplayFormat
        aCC.Range.Text = Format(dtEnd, sDateFormat)
      Next aCC
      thisCC.DateDisplayFormat = sFormat
    End If
  End If
End Sub

[MartyC]

Ok folks, thanks for the replies. Just to clarify, i have a template, with both dates in relevant places multiple times. We have 21 rooms, but very often, most of the rooms will be occupied by the same people.

I have two folders, one for current service users, and another folder for to keep records for service users who have used the services in the past.

When a new resident comes in, I will open the template, fill their details in, national insurance etc and reason for homelessness, then drop it into the folder with the current users.

All the files can be selected and sent to the printer at once and if i get the dates going, it will save having opening up each file every day.

we work with a lot of drug and alcohol users and any time I can save at a computer helps us focus on more important things so that's why I' looking to steam line the process.

Here is a section where i have to do both dates, there are 4 other sections where I have to do this, multiply this by 21 and you can begin to see why I'm trying to solve this.

The current date is simple enough, but tomorrows date in red is where I need to get help.

Anyway, ill look into your kind replies and get back to let you know how I get on.

[MartyC]

This perfect, if i could get this to work. I really don't know what I'm doing.

[gmayor]

Did you even read the link I posted? Download the document I referred to and on page 7 you will see a heading 'Calculate a day, date, month and year, using n days delay'. Select the date in that section. Right click and 'toggle field codes'. Copy the code and paste to where you want the calculated date. Change the delay to 1 and the switch to "dd/MM/yyyy"

{QUOTE
{SET Delay 14}
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

[MartyC]

Sorry, I Did read and then downloaded the next document. Ill try this today.

Originally Posted by gmayor

Did you even read the link I posted? Download the document I referred to and on page 7 you will see a heading 'Calculate a day, date, month and year, using n days delay'. Select the date in that section. Right click and 'toggle field codes'. Copy the code and paste to where you want the calculated date. Change the delay to 1 and the switch to "dd/MM/yyyy"

{QUOTE
{SET Delay 14}
{SET a{=INT((14-{DATE \@ M})/12)}}
{SET b{={DATE \@ yyyy}+4800-a}}
{SET c{={DATE \@ M}+12*a-3}}
{SET d{DATE \@ d}}
{SET jd{=d+INT((153*c+2)/5)+365*b+INT(b/4)-INT(b/100)+INT(b/400)-32045+Delay}}
{SET e{=INT((4*(jd+32044)+3)/146097)}}
{SET f{=jd+32044-INT(146097*e/4)}}
{SET g{=INT((4*f+3)/1461)}}
{SET h{=f-INT(1461*g/4)}}
{SET i{=INT((5*h+2)/153)}}
{SET dd{=h-INT((153*i+2)/5)+1}}
{SET mm{=i+3-12*INT(i/10)}}
{SET yy{=100*e+g-4800+INT(i/10)}}
"{dd}-{mm}-{yy}" \@ "dddd, d MMMM yyyy"}

[MartyC]

Okay, I can only get the following message. I'm sure its something simple, but I really have no idea what I'm doing.

Error! Too many picture switches defined.

Here is what the code looks like to me. I'm sure you guys can spot the errors.

I do appreciate your time

[gmayor]

It looks like you have pasted text and not the code from the document, and you have pasted within your date field and not after it. Post the document or a link to it.

[MartyC]

Originally Posted by gmayor

It looks like you have pasted text and not the code from the document, and you have pasted within your date field and not after it. Post the document or a link to it.

Okay, I think I have done it right.

Thanks

[gmayor]

Try the attached. If you are using Word 2010 or later, I would suggest using content controls for the check boxes and text inputs - see
https://www.gmayor.com/insert_content_control_addin.htm

[MartyC]

Originally Posted by gmayor

Try the attached. If you are using Word 2010 or later, I would suggest using content controls for the check boxes and text inputs - see
https://www.gmayor.com/insert_content_control_addin.htm

Okay ill try. A little about me, so you can see why I appreciate your patience, I do maintenance for these guys, and i can strip an engine down blindfolded and put it back together, but when it comes to this type of thing, its all new, so ill go and try what you said but i might need a step by step.

Thanks again

[MartyC]

That seems to be perfect. If I print that off tomorrow, will the dates your helped me with be updated automatically?

[gmayor]

Don't worry about the content controls. Work with what you are comfortable with.

As for the dates, see what happens tomorrow

[MartyC]

Thank you so much