macro to calculate a date in the future

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Where can I find a good explanation on these bookmarks and would they work on
letters that are automatically generated by our database system?
 
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
 
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.
 
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
 
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.

Greg Maxey said:
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
Click to expand...
 
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]


Sandy said:
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.
Click to expand...
 
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.

Greg Maxey said:
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
Click to expand...
 
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.
 
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.
 
Back
Top