macro to calculate a date in the future

[v&sr4JC]

I would like to have a macro that would calculate a date in the future. I
need to include in my letters for work that a response is due within 8 days.
So 8 days from the date of my letter, I would like a toolbar macro that will
input the date, 8 days in the future but if that date falls on a weekend, it
would select Monday as the due date. Is this possible?

 

[Greg Maxey]

Sally,

Not very elegant and assumes the date of the letter is in a bookmark
named "DOL" and the location of the due date is bookmarked "DD"

Sub Test()
Dim oRng As Range
Dim dateDOL As Date
Dim dateDD As Date
dateDOL = CDate(ActiveDocument.Bookmarks("DOL").Range.Text)
dateDD = DateAdd("d", 8, dateDOL)
Select Case Format(dateDD, "DDDD")
Case "Saturday"
dateDD = DateAdd("d", 2, dateDD)
Case "Sunday"
dateDD = DateAdd("d", 1, dateDD)
Case Else
'Do Nothing
End Select
Set oRng = ActiveDocument.Bookmarks("DD").Range
oRng.Text = dateDD
ActiveDocument.Bookmarks.Add "DD", oRng
End Sub

 

[v&sr4JC]

Where can I find a good explanation on these bookmarks and would they work on
letters that are automatically generated by our database system?

 

[Suzanne S. Barnhill]

See http://www.gmayor.com/insert_a_date_other_than_today.htm

 

[Greg Maxey]

Sally,

A bookmark is just a spot marked in your document. It can be a single
spot (i.e., the insertion point) or span a range of text.

For my example code I gave you, I opened a new blank document;

Typed in today's date;
Selected the date and used Insert>Bookmark to bookmark that text as
"DOL." DOL is just an arbitrary name I used to identify the bookmark.
Next I put the cursor down a few lines and inserted another bookmark
"DD." The bookmark DD identifies the place in the document to insert
the due date. Again DD is just an arbitrary name I used to stand in
for due date.

Whether it works with your letters generated from your data base is up
to you. The concept is:

Get the date from somewhere (e.g., a field, bookmark, input box, the
selection)
Send it through the mill to convert that date to a due date that meets
your requiremetns
Write that revised date to somewhere in the document.

HTH

 

[Greg Maxey]

Suzanne,

Unless I missed it, I don't think Graham's method would bump the due
date to Monday if it normally would fall on the weekend.

 

[v&sr4JC]

Super! I got it to work. Is there a way I can make the date automatically
appear in bold?

 

[Greg Maxey]

Yes,

Sub Test()
Dim oRng As Range
Dim dateDOL As Date
Dim dateDD As Date
dateDOL = CDate(ActiveDocument.Bookmarks("DOL").Range.Text)
dateDD = DateAdd("d", 8, dateDOL)
Select Case Format(dateDD, "DDDD")
Case "Saturday"
dateDD = DateAdd("d", 2, dateDD)
Case "Sunday"
dateDD = DateAdd("d", 1, dateDD)
Case Else
'Do Nothing
End Select
Set oRng = ActiveDocument.Bookmarks("DD").Range
With oRng
.Text = dateDD
.Font.Bold = True
End With
ActiveDocument.Bookmarks.Add "DD", oRng
End Sub

 

[Sandy]

This looks like something that will help me too. But how or where to I
reference this macro in my document? Do I tell the DOL properties to run
this macro on exit or ???

Thanks in advance.

Sally,

Not very elegant and assumes the date of the letter is in a bookmark
named "DOL" and the location of the due date is bookmarked "DD"

Sub Test()
Dim oRng As Range
Dim dateDOL As Date
Dim dateDD As Date
dateDOL = CDate(ActiveDocument.Bookmarks("DOL").Range.Text)
dateDD = DateAdd("d", 8, dateDOL)
Select Case Format(dateDD, "DDDD")
Case "Saturday"
dateDD = DateAdd("d", 2, dateDD)
Case "Sunday"
dateDD = DateAdd("d", 1, dateDD)
Case Else
'Do Nothing
End Select
Set oRng = ActiveDocument.Bookmarks("DD").Range
oRng.Text = dateDD
ActiveDocument.Bookmarks.Add "DD", oRng
End Sub

 

[macropod]

Hi Sandy,

For a solution to this and just about everything else you might want to do with dates in Word (all done without macros), check out
my Date Calc 'tutorial', at:
http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Number=249902
or
http://www.gmayor.com/downloads.htm#Third_party
In particular, look at the item titled 'Handling Weekends and Holidays in Calculated Dates'. Do read the document's introductory
material.

--
Cheers
macropod
[MVP - Microsoft Word]

This looks like something that will help me too. But how or where to I
reference this macro in my document? Do I tell the DOL properties to run
this macro on exit or ???

Thanks in advance.

 

[qldlifestyle]

Greg This is brilliant. However I'm still haveing trouble with it.
When I try to run it I get a "Type Mismatch" message box.
I copied and pasted the macro.
Could you tell me what I have done wrong.

Sally,

Not very elegant and assumes the date of the letter is in a bookmark
named "DOL" and the location of the due date is bookmarked "DD"

Sub Test()
Dim oRng As Range
Dim dateDOL As Date
Dim dateDD As Date
dateDOL = CDate(ActiveDocument.Bookmarks("DOL").Range.Text)
dateDD = DateAdd("d", 8, dateDOL)
Select Case Format(dateDD, "DDDD")
Case "Saturday"
dateDD = DateAdd("d", 2, dateDD)
Case "Sunday"
dateDD = DateAdd("d", 1, dateDD)
Case Else
'Do Nothing
End Select
Set oRng = ActiveDocument.Bookmarks("DD").Range
oRng.Text = dateDD
ActiveDocument.Bookmarks.Add "DD", oRng
End Sub

 

[Greg Maxey]

I suspect that you don't have the date of the letter bookmarked correctly or
you don't have a valid date in the bookmark.

Type 05 February 2009 in the document. Select it and then bookmark the
selection as DOL. Create another bookmark at the IP where the future date
should appear. Name it DD.

Run the code.

 

[qldlifestyle]

Thank you for your quick reply.
My error seems to have been with the bookmark, DOL and the formatting of the
date in the bookmark - "dddd, d MMMM yyyy". I removed the dddd and it worked.