Repeating template

[chris_s]

Hi,

I am new to using VBA in office apps, and have been asked at work to try to produce an automated word document based on an existing two page template. This template is manually adjusted to leave a space for day, but show the month and year before being printed monthly, and then photocopied for each day in the month it is to be used for. The copies are then ring bound, and the day and date are then manually entered each day.

Is it possible for a VBA script to create a complete months worth of pages by using this two page template, adjusting the date as it goes?

Any help you could give me would be very much appreciated.

Thanks,
Chris :help

[mdmackillop]

Hi Chris
Welcome to VBAX
If you can zip and post a sample template to demonstrate your Date layout, that would help. Use Manage Attachments in the Go Advanced section.
Regards
MD

[chris_s]

Hi mdmackillop,

Thanks for the reply, please find the template file attached.

Regards,
Chris

[mdmackillop]

Hi Chris.
This doesn't use a template but creates a new document and copies the two pages repeatedly with new dates for each weekday in the month.
Open the document and run the MakeDoc macro.
Option Explicit

Sub MakeDoc()
Dim strIn As String, Dte As Date, i As Long, Mth As String
Dim NewDoc As Document
Dim OrigDoc As Document

Application.ScreenUpdating = False
Set OrigDoc = ThisDocument
Set NewDoc = Documents.Add
OrigDoc.Activate
strIn = InputBox("Please enter Month." & vbCr & "eg. Apr or April")
Dte = DateValue("1" & "/" & strIn & "/" & Year(Date))
Mth = Month(Dte)
For i = 0 To 31
Dte = Dte + 1
If Month(Dte) <> Mth Then Exit Sub
Select Case Weekday(Dte)
Case 2, 3, 4, 5, 6
OrigDoc.Activate
FillABookmark "Daily", Format(Dte, "dddd, d mmmm yyyy")
ActiveDocument.Range.Copy
NewDoc.Activate
Selection.EndKey Unit:=wdStory
Selection.PasteAndFormat (wdPasteDefault)
End Select
Next
Application.ScreenUpdating = True
End Sub


Sub FillABookmark(strBM_Name As String, strBM_Text As String)
'Thanks to Fumei for this
On Error Resume Next
With Selection
.GoTo what:=wdGoToBookmark, Name:=strBM_Name
.Collapse Direction:=wdCollapseEnd
ActiveDocument.Bookmarks(strBM_Name).Range.Text = strBM_Text
.MoveEnd Unit:=wdCharacter, Count:=Len(strBM_Text)
ActiveDocument.Bookmarks.Add Name:=strBM_Name, Range:=Selection.Range
.Collapse Direction:=wdCollapseEnd
End With
End Sub

[fumei]

You may want to put a page break between them.

[chris_s]

Dear mdmackillop,

Thank you very much indeed. That works beautifully, and should save the guys here a lot of time.

Other than this forum can you recommend some good reading material for learning VBA? (I can see lots of applications for this type of automation in my workplace)

Thanks again,
Chris

[chris_s]

Thanks fumei, much appreciated.

Chris

[chris_s]

Hi mdmackillop,

Almost forgot, I needed to make a mod to your routine. As it stands the first day of the month wasn't shown due to the dte increment.

Here's what I did.


For i = 0 To 31
If i = 0 Then
Else
Dte = Dte + 1
End If

If Month(Dte) <> Mth Then Exit Sub
Select Case Weekday(Dte)
Case 1, 2, 3, 4, 5, 6, 7
OrigDoc.Activate
FillABookmark "Daily", Format(Dte, "dddd, d mmmm yyyy")
ActiveDocument.Range.Copy
NewDoc.Activate
Selection.EndKey Unit:=wdStory
Selection.PasteAndFormat (wdPasteDefault)
End Select
.....



I also amended your case selector to allow for weekends as we often work weekends here (we're so lucky!).

Thank you again for all of your help with this.
Chris

[mdmackillop]

If you work seven days (poor man) you can dispense with the Select Case procedure entirely.

[fumei]

Hi Chris.

This forum is a good place for information. I would also recommend joining Tek-Tips (tek-tips.com), as the Office and VBA forums there are very informative.

Regarding reading, you may be able to find VBA Developer Handbook second hand.

[chris_s]

Thanks fumei, i'll have a look at Tek-Tips.

Chris