Results 1 to 7 of 7
  1. #1
    New Lounger
    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

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    9,020
    Thanks
    64
    Thanked 1,139 Times in 1,060 Posts
    This page may get you started.
    https://www.techrepublic.com/blog/mi...word-document/

    cheers, Paul

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,216
    Thanks
    2
    Thanked 474 Times in 385 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]

  4. #4
    New Lounger
    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!

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,216
    Thanks
    2
    Thanked 474 Times in 385 Posts
    Quote Originally Posted by PegDog View Post
    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
    Au contraire, the example in the tutorial returns Monday next week, not Monday this week.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    New Lounger
    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.

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,216
    Thanks
    2
    Thanked 474 Times in 385 Posts
    Quote Originally Posted by PegDog View Post
    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)
    Which is exactly what both the field code in the tutorial and the macro code I posted does... Both, however, also allow for the possibility that the input date is on a Saturday, which yours doesn't!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •