Create a page for each day of the year (minor? tweaks)

VbaNewbie · #1 01-01-2023, 05:54 AM

Dear all,

first of all, happy new year!

I would like to create a journal for 2023, creating one page (with the same content) for each day of the year - having the date on the top of the page.
I found the fantastic vba script here.

Code:

Sub CreateSigninsForMonth()
    Dim N As Integer
    Dim sCurrentMonth, sCurrentYear As String
    Dim sNewDate As String
    
    N = 1

    Count = Day(GetLastDayOfMonth)
           
    For CopyNumber = 1 To Count
    
        With Selection
            .GoTo wdGoToPage, wdGoToAbsolute, 1
            .Bookmarks("\Page").Range.Copy
            .Paste
        End With
    
        With ActiveSheet
       
         sCurrentMonth = Format(Date, "mmmm")
         sCurrentYear = Format(Date, "yyyy")
         sNewDate = (CopyNumber & " " & sCurrentMonth & " " & sCurrentYear)
        
        ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "dd. MMMM")
          
        End With
        N = N + 1
    Next CopyNumber
    
    'Delete template + blank page
    For i = 1 To 2
    With ActiveDocument
        strt = .GoTo(wdGoToPage, wdGoToLast).Start
        Set r = .Range(strt - 1, .Range.End)
        r.Delete
    End With
    Next

End Sub


Function GetFirstDayOfMonth(Optional dtmDate As Date = 0) As Date
    
    ' Return the first day in the specified month.
    If dtmDate = 0 Then
        ' Use the current date if none was specified
        dtmDate = Date
    End If
    
    GetFirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

End Function

Function GetLastDayOfMonth(Optional dtmDate As Date = 0) As Date
    
    ' Return the last day in the specified month.
    If dtmDate = 0 Then
        
         ' Use the current date if none was specified
        dtmDate = Date
    
    End If
    
    GetLastDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate) + 1, 0)
End Function

While it does work well, I encounter two problems:

1) It adds the the pages/dates in reverse order (jan. 1st is on the last page).
I tried changing the code and adding .Collapse wdCollapseEnd, but the results are the same.

Code:

        With Selection
            .GoTo wdGoToPage, wdGoToAbsolute, 1
            .Bookmarks("\Page").Range.Copy
            .Collapse wdCollapseEnd
            .Paste
        End With

How do I need to change the code to have Jan. 1st on the first page, 2nd on the second, etc.

2) Now the script only adds one month. Is it possible to to the same for the whole year? So that I have 365 days at the end?

I tried adding an additional For loop, which starts at 0 and goes to 12 and always adds +1 to the date. However, it does not work. I still only see January (and it does not recognize that not all months have 31 days...)

Here my changes:

Code:

Sub CreateSigninsForMonth()
    Dim N As Integer
    Dim nMonth As Integer
    Dim sCurrentMonth, sCurrentYear As String
    Dim sNewDate As String
    
    N = 1
    nMonth = 0

    Count = Day(GetLastDayOfMonth)
    
    month_last = 11
    
    For month_first = 0 To month_last
           
        For CopyNumber = 1 To Count
        
            With Selection
                .GoTo wdGoToPage, wdGoToAbsolute, 1
                .Bookmarks("\Page").Range.Copy
                .Paste
            End With
        
            With ActiveSheet
           
             sCurrentMonth = Format(Date + nMonth, "mmmm")
             sCurrentYear = Format(Date, "yyyy")
             sNewDate = (CopyNumber & " " & sCurrentMonth & " " & sCurrentYear)
            
            ActiveDocument.FormFields("txtDate").Result = Format(sNewDate, "dd. MMMM")
              
            End With
            N = N + 1
        Next CopyNumber
        
        'Delete template + blank page
        For i = 1 To 2
        With ActiveDocument
            strt = .GoTo(wdGoToPage, wdGoToLast).Start
            Set r = .Range(strt - 1, .Range.End)
            r.Delete
        End With
        Next
    nMonth = nMonth + 1
    Next

End Sub

Thank you for any help!

macropod · #2 01-02-2023, 12:22 AM

That's awful code and I'd be surprised if it would work at all. Amongst other things it has a quite invalid 'With ActiveSheet' reference. Word documents don't have worksheets.

In any case, what you're doing requires no code and can be done via the use of an Excel workbook with a column of dates for the year (1 January 2023 in A2, then increment the value by 1 on each successive row) and connecting your existing document to that workbook for a letter-type mailmerge and an appropriate mergefield wherever you want the date to appear on the first page. With 365 data rows in your Excel workbook, finish the merge and you'll automatically get a 365-page document, each page having its own date.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Outlook Tweaks	komobu	Outlook	1	04-30-2021 09:42 PM
How to search for a specific year from a table with year range?	Wii	Excel	0	05-05-2015 12:40 PM
How to calculate a rolling year-to-date percentage by quarter as the year progresses	sleake	Excel Programming	2	04-23-2015 11:51 AM
Networkdays per fiscal year in a 6 year range	jabnm	Excel	1	10-16-2014 11:51 AM
Creative Ways for a year-to-year comparison???	ridonkulous5	Excel	1	03-23-2011 04:49 PM