Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 40
  1. #16
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I see, like I said, the syntax I saw in many examples online is Selection.[something].Transparency = 0.5 (I might be a little off on that but I will repost with example(s)). So I guess it seems like I might want to scrap the syntax for something else because VBA doesn't like the Shp2.TextFrame.[the rest of it] in that way??

    I actually have been playing with ways to minimize the number of steps it would take to make it perfect (hence, my objective of just opening the file and pressing the print button). I played with using an Excel Object in it, but only made the font look weird. I also played with using graphics but didn't really solve any problem. I think the best one I came up with was that I created a Style that has 50% transparency, so I would have to 1. open the file 2. select each text box (can't Control-A to select all for text boxes) and hit the Style I had created, then 3. press print

    Like I said, I will repost with some examples tomorrow (gotta go to bed here in California time).

    Oh, I love the first link you referenced to me. I can understand what the algorithm is doing and how that person put the Select Case statement, but I don't understand all of the syntax (it is Excel VBA, not Word VBA which I believe is a little different). I am going to play around with it using my very limited knowledge of Word VBA.

    Andrew, can you work with me on that Excel VBA so we can modify it to make it work in Word please?? (I registered an account on Ozgrid.com and posted a reply to that person, but not sure if that person is going to reply (it is an old post by now, I just hope that person will reply).
    Last edited by LukeB; 2013-07-08 at 00:40.

  2. #17
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Whoa!! I am kind of getting lost here.....I understand what you are saying about applying a style in the GUI, but I am getting confused when you say "Is the style already in the document!! You would need to firstly copy this from the template where the macro resides before calling that line. There will be code to do this somewhere on this forum."

    I am not sure if MS Word applies the Style I created with 50% transparency to all existing and new MS Word documents (under my computer for anyone that logs in, under my login only, or maybe something else??)

    Can you kind of go a little slower for what I need to overcome here??

    So I tested it out with that syntax Shp.TextFrame.TextRange.Style = "DateStamp" and it totally worked!!!!
    I just don't know if it will only work on that file or if it will work on all files??
    Last edited by LukeB; 2013-07-08 at 01:15.

  3. #18
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    Luke

    The line which applies DateStamp style will work if (and only if) the style is already in the document. It sounds like you are running this macro on files you are getting from outsiders so it is highly unlikely they already have a style with that name in their documents. Even if they did, could you trust that it has the same font settings that you require? If you created a template and got those users to supply only files created with the template then you might have a chance of this happening.

    So instead you put the style as you need it into Normal.dotm (where you also store the macro) and include code to copy the style into the current document prior to applying that style to your new text box content.

    Code:
    Sub AutoOpen()
      Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
      If Format(Now(), "ddd") = "Mon" Then
        iDelay = 3
      Else
        iDelay = 1
      End If
      iDelay = InputBox("NMM RECEIVED How many day(s) ago?", "Received x days ago", iDelay)
      sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
    
      'this line will fail if the style DateStamp doesn't exist in the Normal Template
      Application.OrganizerCopy Source:=NormalTemplate.FullName, _
                Destination:=ActiveDocument.Name, Name:="DateStamp", Object:=wdOrganizerObjectStyles
      
      Set Shp = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationVertical, _
            Left:=7, Top:=252, Width:=25, Height:=170)
      
      Shp.TextFrame.TextRange.Style = "DateStamp"
      Shp.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
      Shp.Fill.ForeColor = RGB(255, 255, 255)
      Shp.Line.Visible = msoFalse
      
      Set Shp2 = ActiveDocument.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
            Left:=462, Top:=765, Width:=142, Height:=18)
      Shp2.TextFrame.TextRange.Style = "DateStamp"
      Shp2.TextFrame.TextRange.Text = "NMM RECEIVED: " & sDate
      Shp.Fill.ForeColor = RGB(255, 255, 255)
      Shp2.Line.Visible = msoFalse
      Shp2.IncrementRotation (180)
    End Sub
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #19
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    Luke

    I have had a look at your post on ozgrid and picked up a key fact that changes everything. It appears you are using this macro to create an overprint page which you then print on top of pages that have been already printed. If this is the case then you are not modifying electronic copies of the existing documents and there is no real need for a more complicated macro. A macro might save you three seconds a day but take 10 hours to create.

    The process I would use is:
    1. Create a Word document with the two text boxes and put the required date into both these text boxes.
    2. Format the content of the text boxes as grey (they don't need to be transparent unless you are overlaying them inside each file prior to the first print)
    3. Save the document because you will use it again tomorrow.
    4. Print a single page and take it to the photocopier
    5. Load the paper tray with the reports and put your single printed page on the glass.
    6. Max the count on the copies and hit go - when it runs out of paper then your job is done and you can reset the copier
    7. Throw away the piece of paper on the glass


    Tomorrow, edit the Word file you saved in step three (change the date to the date required today) and repeat steps 3 onwards.
    Last edited by Andrew Lockton; 2013-07-08 at 20:51.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  5. #20
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I guess that is one way to do it, but I actually prefer to open the Word document that runs the AutoOpen macro, inserting the correct date in the 2 transparent text boxes automatically and then just pressing print (specifying more pages than I have claim forms that I load prior to pressing print and canceling the print job once all papers have been date stamped).

    I think I know what you are saying about the Normal.dotm template in your previous post. I am not getting files from other people\employees, this is a 1 file project to make the tedious task of manually entering the date into 2 transparent text boxes in the correct format every day completely automated so I can just give it a quick visual check (to make sure the dates are correct), load the stack of preprinted claim forms into the printer tray, and press print. I really do appreciate seeing how to insert the VBA code into a Normal.dotm (macro template), but I don't think that would be necessary for this project, right?? I believe I took care of that sort of issue by using the If statement (If Application.ActiveDocument.Name = "[name of document]" Then [rest of VBA code] End If) around the whole VBA code.

    And yes, that 3 seconds a day (more like 2 minutes a day) times many years of doing this adds up. Besides, it isn't only about me, if I do, one day, move up and don't have to 'datestamp' these claim forms every day, someone else will have to do it and feel as annoyed as me if that person has to manually write the correct date in the correct format every day, you know??

    What I do need help and time with is how to merge my VBA syntax that you have so graciously helped me with, with the VBA syntax of the OzGrid post section: ' official market holidays in USD Area (I hope I will have more time to commit to this undertaking tomorrow and in the days to come, however long it takes).

  6. #21
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    I am not going to put in the effort to produce the correct date every time. The rewards for solving that problem are IMO not worthy of the time which would need to be spent on it.

    The attached document is the best I am prepared to do. I have put in a duplicated Date content control and a macro. Every time you open this document it will already have the right date in it (assuming the macro runs). The exception is the day after a holiday. On those rare occasions, you click on either of the dates and a popup will appear so you can choose the day you wish to display. This solution doesn't require anything in the Normal template. It also doesn't require you to do anything other than open and print each day. I would recommend you make the document read-only so that uncontrolled changes don't occur in the document from day to day.

    The code has been simplified to the bare minimum - there is no insertion of text boxes required because they are already in the document. There is no formatting required because that is also already in the file. All you need to do is pay enough attention to do two clicks on the day following a public holiday.
    Attached Files Attached Files
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  7. #22
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is very cool (I believe that is called a Date Picker Control).

    Can I change the Title of the Content Control from "PublishDate" to "Mail Room Receive Date"? If I do it gives me one of those VBA Run-time error '5941': The requested member of the collection does not exist.
    So I click Debug and it highlights that line where the Content Control Title exists....

    Also I see that the Macro you created says "Option Explicit" at the top (does this mean that that code makes the AutoOpen macro run exclusive to that file??)
    Last edited by LukeB; 2013-07-09 at 13:25.

  8. #23
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't know if this code is going to help at all, but I found this (would you be able to tell me how I am suppose to run the functions and produce output to go into the text boxes)??

    Code:
    Option Explicit
    Option Base 1
    
    Public Function EasterDate(yr As Integer) As Date
        Dim d As Integer
        d = (((255 - 11 * (yr Mod 19)) - 21) Mod 30) + 21
        EasterDate = DateSerial(yr, 3, 1) + d + (d > 48) + 6 - ((yr + yr \ 4 + d + (d > 48) + 1) Mod 7)
    End Function
    
    ' We can generalize this to holidays that are defined as the Nth Day of some month,
    ' such as Martin Luther King's birthday, celebrated on the 3rd Monday of January.
    ' The following function will return the Nth DayOfWeek for a given month and year:
    'SYntax NDOW:
    '   y = Year
    '   M = Month
    '   N = Nth day of M month
    '   DOW = Day of the week:  1 = Sunday, 2= Monday, etc.
    Public Function NDow(y As Integer, M As Integer, N As Integer, DOW As Integer) As Date
        NDow = DateSerial(y, M, (8 - WeekDay(DateSerial(y, M, 1), (DOW + 1) Mod 8)) + ((N - 1) * 7))
    End Function
    
    ' To compute this date, we first need a function to tell us how many Mondays there are in the month.
    Public Function DOWsInMonth(yr As Integer, M As Integer, DOW As Integer) As Integer
    On Error GoTo EndFunction
    Dim i As Integer
    Dim Lim As Integer
    Lim = Day(DateSerial(yr, M + 1, 0))
    DOWsInMonth = 0
    For i = 1 To Lim
        If WeekDay(DateSerial(yr, M, i)) = DOW Then
            DOWsInMonth = DOWsInMonth + 1
        End If
    Next i
    EndFunction:
    Err.Clear
    On Error GoTo 0
    End Function
    
    Public Function Thanksgiving(yr As Integer) As Date
       Thanksgiving = DateSerial(yr, 11, 29 - WeekDay(DateSerial(yr, 11, 1), vbFriday))
    End Function
    
    Public Function WkNr(AnyDate As Date, Optional WhichFormat As Variant) As Integer
    '
    ' WhichFormat: missing or <> 2 then returns week number,
    '              = 2 then YYWW
    '
    Dim ThisYear As Long
    Dim PreviousYearStart As Date
    Dim ThisYearStart As Date
    Dim NextYearStart As Date
    Dim YearNum As Integer
     
    ThisYear = Year(AnyDate)
    ThisYearStart = YearStart(ThisYear)
    PreviousYearStart = YearStart(ThisYear - 1)
    NextYearStart = YearStart(ThisYear + 1)
    Select Case AnyDate
        Case Is >= NextYearStart
            WkNr = (AnyDate - NextYearStart) \ 7 + 1
            YearNum = Year(AnyDate) + 1
        Case Is < ThisYearStart
            WkNr = (AnyDate - PreviousYearStart) \ 7 + 1
            YearNum = Year(AnyDate) - 1
        Case Else
            WkNr = (AnyDate - ThisYearStart) \ 7 + 1
            YearNum = Year(AnyDate)
    End Select
     
    If IsMissing(WhichFormat) Then
        Exit Function
    End If
    If WhichFormat = 2 Then
        WkNr = CInt(Format(Right(YearNum, 2), "00") & Format(WkNr, "00"))
    End If
     
    End Function
     
    'This function requires the YearStart function which returns the date of the first Monday of a given year.
    Function YearStart(WhichYear As Long) As Date
     
    Dim WeekDay As Integer
    Dim NewYear As Date
     
    NewYear = DateSerial(WhichYear, 1, 1)
    WeekDay = (NewYear - 2) Mod 7
    If WeekDay < 4 Then
        YearStart = NewYear - WeekDay
    Else
        YearStart = NewYear - WeekDay + 7
    End If
     
    End Function
    
    Public Function IsWeekend(wDate As Date) As Integer
        If WeekDay(wDate) = 7 Or WeekDay(wDate) = 1 Then IsWeekend = 1
    End Function
    
    Public Function isHoliday(wDate As Date) As Integer
        Dim sDate As Date
        Dim tDate, tYear As Integer
        tDate = EasterDate(Year(wDate))
        sDate = tDate
        Select Case wDate
            Case DateValue(Year(wDate) & "-01-01")
                isHoliday = 1   '   New Year's day
            Case DateValue(Year(wDate) & "-06-04")
                isHoliday = 1   '   Independance Day
            Case DateValue(Year(wDate) & "-05-01")
                isHoliday = 1   '   Labor Day
            Case DateSerial(Year(wDate), 11, 29 - WeekDay(DateSerial(Year(wDate), 11, 1), vbFriday))
                isHoliday = 1   '   Thanksgiving
            Case NDow(Year(wDate), 1, 3, vbMonday)
                isHoliday = 1   '   Martin Luther King Day
            Case tDate
                isHoliday = 1   '   Easter Sunday
            Case tDate + 1
                isHoliday = 1   '   Easter Monday
            Case tDate + 39
                isHoliday = 1   '   Ascension Day
            Case tDate + 49
                isHoliday = 1   '   Whit Sunday  (Penteccost)
            Case tDate + 50
                isHoliday = 1   '   Whit Munday
            Case DateValue(Year(wDate) & "-12-25")
                isHoliday = 1   '   Christmas Day
        End Select
    End Function
    
    Public Function WorkDays(dDate1 As Date, dDate2 As Date) As Integer
        Dim tDays As Integer
        Dim dTemp As Date
        tDays = dDate2 - dDate1 + 1
        dTemp = dDate1
        Do While dTemp <= dDate2
            tDays = tDays - IIf(isHoliday(dTemp) Or IsWeekend(dTemp), 1, 0)
            dTemp = dTemp + 1
        Loop
        WorkDays = tDays
    End Function
    
    Public Function DaysOff(dDate1 As Date, dDate2 As Date) As Integer
        Dim tDays As Long
        Dim dTemp As Date
        tDays = 0
        If dDate1 <= dDate2 Then
            dTemp = dDate1
            Do While dTemp <= dDate2
                tDays = tDays + IIf(isHoliday(dTemp) Or IsWeekend(dTemp), 1, 0)
                dTemp = dTemp + 1
            Loop
        End If
        DaysOff = tDays
    End Function
    
    
    Public Function lastDay(xM As Integer, Optional tYear As Long) As Integer
       tYear = IIf(tYear = 0, Year(Date), tYear)
       Select Case xM
          Case 1, 3, 5, 7, 8, 10, 12
             lastDay = 31
          Case 4, 6, 9, 11
             lastDay = 30
          Case 2
             lastDay = IIf(Int(tYear / 4) = tYear / 4, 29, 28)
       End Select
    End Function

  9. #24
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    If you change the title of the content control then you need to change the line of the code which selects by title

    Set oCC = ActiveDocument.SelectContentControlsByTitle("Mail Room Receive Date").Item(1)

    Option Explicit tells the module that all variables need to be declared before you can use them. The lines that start with 'Dim' are declaring what type each variable is. This is good programming practice as it allows the computer to allocate the minimum amount of space possible to a variable.

    In your posted code there are functions. These take an input and return an output. Looking at the code, if you wanted to find the date of easter you could try
    MsgBox("Easter in 2013 falls on " & EasterDate(2013))

    Or if you wanted to capture the date for easter in a variable you could use something like this
    sDate = Format(EasterDate(2013), "dddd, MMMM dd, yyyy")

    A more complex function in that code starts with
    Function DOWsInMonth(yr As Integer, M As Integer, DOW As Integer) As Integer
    the above line declares three variables of type integer as inputs and returns another integer variable with the name of the function itself

    In this case, you pass in three variables in the order of year, month, day of week - such as
    MsgBox("The number of Tuesdays in Jan 2013 is " & DOWsInMonth(2013,1,3))
    Last edited by Andrew Lockton; 2013-07-09 at 18:00.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  10. #25
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, so here are the days my work gets off:

    New Year's Day 1/1/13 Always
    Martin Luther King Day 1/21/13 Variable (3rd Monday of January)
    President's Day 2/18/13 Variable (3rd Monday of February)
    Memorial Day 5/27/13 Variable (Final Monday of May)
    Independence Day 7/4/13 Always
    Labor Day 9/2/13 Variable (1st Monday of September)
    Thanksgiving Day (& Friday) 11/28/13 and 11/29/13 Variable (4th Thursday and Friday of November)
    Christmas Day 12/25/13 Always

    I could go like this?: ( I don't know that whole line of math they are doing. In Other words, why does it say d = (((255 - 11 * (yr Mod 19)) - 21) Mod 30) + 21
    EasterDate = DateSerial(yr, 3, 1) + d + (d > 48) + 6 - ((yr + yr \ 4 + d + (d > 48) + 1) Mod 7)
    What are all these numbers??

    sDate = Format(ChristmasDay(2013), "dddd, MMMM dd, yyyy")
    If sDate = Wednesday, December 25, 2013 Then
    sDate = Format(Now() - 2, "dddd, MMMM dd, yyyy")
    ElseIf Format(Now(), "ddd") = "Mon" Then
    sDate = Format(Now() - 3, "dddd, MMMM dd, yyyy")
    Else sDate = Format(Now() - 1, "dddd, MMMM dd, yyyy")
    End If


    Or I could just go with that other code since it seems to be more adaptable (up to the year 2500 as part of the title was called) referencing:
    Select Case ISO_datum
    Case 102, 705, 1226
    calendar_USD = False
    Exit Function
    Case 115 To 121 ' Martin Luther King day ( it falls on 3rd Monday )
    calendar_USD = False
    Exit Function
    Case 215 To 221 ' President's day ( it falls on 3rd Monday )
    calendar_USD = False
    Exit Function
    Case 525 To 531 ' Memorial day ( it falls on last Monday )
    calendar_USD = False
    Case 901 To 907 ' Labor day ( it falls on first Monday )
    calendar_USD = False

    So from that, I can just make an If statement like this??:

    If Case = 115 To 121 Then
    sDate = Format(Now() - 4, "dddd, MMMM dd, yyyy")
    If Case = 215 To 221 Then
    sDate = Format(Now() - 4, "dddd, MMMM dd, yyyy")
    If Case = 525 To 531 Then
    sDate = Format(Now() - 4, "dddd, MMMM dd, yyyy")
    If Case = 901 To 907 Then
    sDate = Format(Now() - 4, "dddd, MMMM dd, yyyy")
    If Case = 1225 Then '(or If Case = 1225 & Format(Now(), "ddd") = "Mon" Then sDate = Format(Now() - 4, "dddd, MMMM dd, yyyy") and a different one for each day??)
    sDate = Format(Now() - ?, "dddd, MMMM dd, yyyy") ' depends what day it falls on right?

    All of those except for Christmas are Mondays and that is why I can just go Now() - 4 for all of those, but don't know how I would do Thanksgiving. But when I come to think of it, I would be putting wrong dates in for the rest of the ranges (I am getting more and more confused)??
    Last edited by LukeB; 2013-07-09 at 19:08.

  11. #26
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    Luke

    If you think the date functions are complicated you ain't seen nothing yet. The basic flow I would use to solve this is:
    1. Build an array of all the dates of public holidays for the current year
    2. Use that array to build a dictionary of all the dates that are the working days that follow a public holiday
    3. Populate the dictionary with the nearest preceding work day (iterating to check that day is not also a public holiday)
    4. Amend the date stepping code to hijack the usual 1day/3days pattern if the current date happens to be in the dictionary

    The dictionary object would be used to create a list of date pairs (today's date and last working date) which can be searched without looping an array. Excellent code for working with arrays and dictionaries is on this forum.

    Then sit back and hope that no-one ever has a day off sick because all that coding effort will be wasted unless you amend the code to be smart enough to work out that the code wasn't run yesterday so it needs to display not yesterday but the day before yesterday.

    This idea actually makes me think there is another way of doing this - what if you stored today's date in the document metadata and simply inject that date into the content control next time the document is opened? If the file was opened once every work day and never at any other time this would be a flawless solution and require no date manipulation at all.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  12. #27
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, so it sounds like a good strategy and I will look on this forum and probably google arrays and dictionaries as well. Here is an article I found that I don't know if you are talking about doing something like this: http://www.experts-exchange.com/Soft...ss-in-VBA.html

    Don't worry about sick days because if I don't do it because I am sick or something, someone else will have to do it for that particular day (regardless, it will get done every day).

    I hope I am not understanding you correctly about storing today's date in the document metadata to populate the content control every time it is opened with the current date.....This is not the objective at all, remember the mail room receives the mail 1 day before I get it on my desk and 3 days before from Friday to Monday. If it was the current day that the mail room received it and I got it to date stamp it the same day, I wouldn't have to use any VBA macro at all, it would be super easy just to put a form control for Updating the current day. But unfortunately, it is as the way I just mentioned in this paragraph.....

    I don't know VBA and you are probably going to think 'What in the world' or something but I am taking snippets of code to make this:

    Sub HolidayExclusions(ByVal Target As Range)
    Dim Arr As Variant
    Dim Dic As Scripting.Dictionary
    Set Dic = New Scripting.Dictionary

    Holidays = Array("01/01", "01/21", "02/18", "05/27", "07/04", "09/02", "11/28", "11/29", "12/25")

    For Each x In Dic
    MsgBox Dic.Item(x)
    Next
    End Sub

    ??? Is this what you are saying for steps #1 and #2??? (see what I mean, I am a VBA newbie!! Thank you for putting up with my ignorance....)
    Last edited by LukeB; 2013-07-10 at 12:34.

  13. #28
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What about doing something like this Andrew:

    Code:
    Sub AutoOpen()
      Call SkipHolidays    'All the rest of the code is the same except this line to Call Private Function SkipHolidays (see below this code)  
      Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
      Dim oCC As ContentControl
      If Format(Now(), "ddd") = "Mon" Then
        iDelay = 3
      Else
        iDelay = 1
      End If
      sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")
      
      Set oCC = ActiveDocument.SelectContentControlsByTitle("MailRoom Received").Item(1)
      oCC.Range.Text = sDate
    End Sub

    SkipHolidays function:

    Code:
    Private Function SkipHolidays(rst As Recordset, _
     strField As String, dtmTemp As Date, intIncrement As Integer) _
     As Date
        ' Skip weekend days, and holidays in the
        ' recordset referred to by rst.
        Dim strCriteria As String
        On Error GoTo HandleErr
        ' Move up to the first Monday/last Friday if the first/last
        ' of the month was a weekend date. Then skip holidays.
        ' Repeat this entire process until you get to a weekday.
        ' Unless rst contains a row for every day in the year (!)
        ' this should finally converge on a weekday.
        Do
            Do While IsWeekend(dtmTemp)
                dtmTemp = dtmTemp + intIncrement
            Loop
            If Not rst Is Nothing Then
                If Len(strField) > 0 Then
                    If Left(strField, 1) <> "[" Then
                        strField = "[" & strField & "]"
                    End If
                    Do
                        strCriteria = strField & _
                         " = #" & Format(dtmTemp, "mm/dd/yy") & "#"
                        rst.FindFirst strCriteria
                        If Not rst.NoMatch Then
                            dtmTemp = dtmTemp + intIncrement
                        End If
                    Loop Until rst.NoMatch
                End If
            End If
        Loop Until Not IsWeekend(dtmTemp)
    ExitHere:
        SkipHolidays = dtmTemp
        Exit Function
    
    HandleErr:
        ' No matter what the error, just
        ' return without complaining.
        ' The worst that could happen is that the code
        ' includes a holiday as a real day, even if
        ' it's in the table.
        Resume ExitHere
    End Function
    When I tried this, it gave me a Compile error: User-defined type not defined ??? I have been trying to read about UDFs but can't seem to figure out why I can't just Call a Function??

    I read on http://support.microsoft.com/kb/858611 to click on the latest version of Microsoft ActiveX Data Object Library (for me, the latest version was 6.1). After clicking on that one, and reopening the file, the error message changed to: Compile error: Argument not optional (with Sub AutoOpen() highlighted in yellow and yellow arrow pointing at it (and Call SkipHolidays highlighted in the normal blue highlight color).
    Last edited by LukeB; 2013-07-10 at 15:10.

  14. #29
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I was trying to be cheesy and came up with this really simple version (but of course, would have to updated continually or just be a super long series of If Statements): Option Explicit
    Sub AutoOpen()
    'Series of If Statements must be updated after the holiday in a given year passes
    Dim Shp As Shape, Shp2 As Shape, sDate As String, iDelay As Integer
    Dim oCC As ContentControl
    If Now = 1 / 2 / 14 Then
    iDelay = 2 ' post New Years Day
    ElseIf Now = 1 / 21 / 14 Then
    iDelay = 4 ' post Martin Luther King's Day
    ElseIf Now = 2 / 18 / 14 Then
    iDelay = 4 ' post President's Day
    ElseIf Now = 5 / 27 / 14 Then
    iDelay = 4 ' post Memorial Day
    ElseIf Now = 7 / 7 / 14 Then
    iDelay = 4 ' post Independence Day
    ElseIf Now = 9 / 3 / 13 Then
    iDelay = 4 ' post Labor Day
    ElseIf Now = 12 / 2 / 13 Then
    iDelay = 5 ' post Thanksgiving Day Weekend
    ElseIf Now = 12 / 26 / 13 Then
    iDelay = 2 ' post Christmas Day
    ElseIf Format(Now(), "ddd") = "Mon" Then
    iDelay = 3
    Else
    iDelay = 1
    End If
    sDate = Format(Now() - iDelay, "dddd, MMMM dd, yyyy")

    Set oCC = ActiveDocument.SelectContentControlsByTitle("MailR oom Received").Item(1)
    oCC.Range.Text = sDate
    End Sub
    I don't understand why this doesn't work?? It always does 2 days ago whenever I set my system clock on one of the days specified in the If Statement (first business day post Holiday)?? If you can help me with this one, I will get out of your hair.....
    Last edited by LukeB; 2013-07-10 at 18:50.

  15. #30
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,505
    Thanks
    3
    Thanked 141 Times in 134 Posts
    That is the wrong syntax for a date and Now has a time component in it too. Try something along the lines of...

    If Date = #01/02/2014# Then
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

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