Microsoft Office Forums - View Single Post

gmaxey · 04-05-2019, 06:14 AM

Assuming all collected data means 1) Store number, 2) Store Name and 3) Month and year then I don't see why you can't just continue your process and get Month and Year from and inputbox as well.

I would create my template using three content controls. One for store number, one for store name and one for date. I would follows those three CCs with a section breack next page then select the three plus section break and save it as a BuildingBlock e.g., CoverSheet. Then using:

Code:

Sub DatedCoverSheets()
'A basic Word macro coded by Greg Maxey
Dim strName As String, strNum As String, strMY As String
Dim arrParts() As String
Dim oDate As Date
Dim lngIndex As Long
Dim oRng As Range
  strNum = InputBox("What is your store number?")
  strName = InputBox("What is your store name?")
  strMY = InputBox("Enter the month and year e.g., April 2019")
  arrParts = Split(strMY, " ")
  oDate = "1 " & arrParts(0) & " " & arrParts(1)
  For lngIndex = 1 To fcnDaysInMonth(oDate)
    With ActiveDocument.Sections(lngIndex).Range
      .ContentControls(1).Range.Text = "ABC"
      .ContentControls(2).Range.Text = "123"
      'Date the page
      .ContentControls(3).Range.Text = Format(DateAdd("d", lngIndex - 1, oDate), "MMMM dd, yyyy")
    End With
    Set oRng = ActiveDocument.Range
    oRng.Collapse wdCollapseEnd
    ActiveDocument.AttachedTemplate.BuildingBlockEntries("CoverSheet").Insert oRng, True
  Next
  'As a result of the section break in the template and in the building block, there will be two hanging sections at
  'the end of the finished document.  Delete them.
  Set oRng = ActiveDocument.Sections(lngIndex - 1).Range.Paragraphs.Last.Range
  oRng.End = ActiveDocument.Range.End
  oRng.Delete
lbl_Exit:
  Exit Sub
End Sub

Function fcnDaysInMonth(oDateSample As Date)
    fcnDaysInMonth = Day(DateSerial(Year(oDateSample), Month(oDateSample) + 1, 1) - 1)
 End Function

... you get you 28, 29, 30 or 31 pages as required.

Edit: The attached template is a variation of the code above that employs a userform.