Results 1 to 7 of 7
-
2018-07-10, 10:03 #1
- Join Date
- Jul 2018
- Posts
- 3
- Thanks
- 0
- Thanked 0 Times in 0 Posts
VBA Code for Checking for a Weekend
I have a simple Word table that contains today's date in one field, and tomorrow's date in another. That part is no problem. However, if today is a Friday, then I want the calculation to add 3, rather than 1, to the "delivery date." I can easily do this in Excel (using the WORKDAY function), but can't find a way that Word will accomplish the same conditional statement.
Any and all help is appreciated.
PegDog
-
2018-07-10, 11:39 #2
- Join Date
- Dec 2009
- Location
- Earth
- Posts
- 9,179
- Thanks
- 69
- Thanked 1,165 Times in 1,083 Posts
This page may get you started.
https://www.techrepublic.com/blog/mi...word-document/
cheers, Paul
-
2018-07-10, 23:05 #3
- Join Date
- May 2002
- Location
- Canberra, Australian Capital Territory, Australia
- Posts
- 5,256
- Thanks
- 2
- Thanked 484 Times in 393 Posts
My Microsoft Word Date Calculation Tutorial (in the Sticky thread at the top of this forum), shows how you can do this with field coding - provided the initial date is bookmarked (it can't be done via a simple table cell reference). For a macro-based solution, you might try:
Code:Sub Demo() Application.ScreenUpdating = False Dim i As Long With ActiveDocument.Tables(1) Select Case CDate(Split(.Cell(1, 1).Range.Text, vbCr)(0)) Mod 7 Case 6: i = 3 Case 0: i = 2 Case Else: i = 1 End Select .Cell(1, 2).Range.Text = Format(CDate(Split(.Cell(1, 1).Range.Text, vbCr)(0)) + i, "DD-MM-YYYY") End With Application.ScreenUpdating = True End Sub
Cheers,
Paul Edstein
[MS MVP - Word]
-
2018-07-13, 16:04 #4
- Join Date
- Jul 2018
- Posts
- 3
- Thanks
- 0
- Thanked 0 Times in 0 Posts
Paul,
Thanks a bunch for all your help. I used the first reference you sent, but since the purpose was different in that one (returning Monday of the current week, rather than Monday of the next week IF "today" is Friday), I had to tweak it some. Took some annoying failures, but finally worked. And the final solution was as you suggested in your second post - bookmarking the place in the Word document where the calculated date is to appear.
Sub AutoNew()
'
'AutoNew Macro
'Return Monday's Date if the variable is a Friday.
Dim dteFriday As Date
If (Weekday(Date)) = 6 Then dteFriday = Date + 3 Else dteFriday = Date + 1
With ActiveDocument.Bookmarks("TomDate").Range
.InsertBefore Format(dteFriday, "MMMM d, yyyy")
End With
End Sub
______
Thanks again for your help!
-
2018-07-13, 17:23 #5
- Join Date
- May 2002
- Location
- Canberra, Australian Capital Territory, Australia
- Posts
- 5,256
- Thanks
- 2
- Thanked 484 Times in 393 Posts
-
2018-07-13, 18:24 #6
- Join Date
- Jul 2018
- Posts
- 3
- Thanks
- 0
- Thanked 0 Times in 0 Posts
Au contraire-contraire. Returning Monday next week is exactly what i want it to do, as long as the macro is being executed on a Friday. i.e, If I prepare an order on a Friday, I want it to be delivered NEXT Monday, not last Monday. On the other hand, if I prepare an order on a Wednesday, I want it to be delivered tomorrow - Thursday (i.e., the +3/+1 parameters) That's why I had to tweak the code so much. It had the timing constructs, just not the same concept.
-
2018-07-13, 19:16 #7
- Join Date
- May 2002
- Location
- Canberra, Australian Capital Territory, Australia
- Posts
- 5,256
- Thanks
- 2
- Thanked 484 Times in 393 Posts