Track[keep]

Code Breakdown: Document Automation Script

Document Automation Code

As part of the document automation project, I developed the following VBScript code to handle the automatic generation and saving of agenda documents. Below is the sanitized version of the script with explanatory comments:

Sub SaveReportAsDocx()
    Dim mondayDate As Date
    Dim formattedDate As String
    Dim cc As ContentControl
    Dim docName As String, savePath As String
    Dim currentYear As String
    Dim basePath As String
    
    ' Get the Monday of the current week
    mondayDate = Date - Weekday(Date, vbMonday) + 1
    formattedDate = Format(mondayDate, "MM_DD_YYYY")
 
    ' Extract the current year from the Monday date
    currentYear = Year(mondayDate)
 
    ' Set base directory for agendas, adjusting for year
    basePath = "C:\Users\Username\Documents\Reports\" & currentYear & "\"
    
    ' Ensure the directory exists (prevents errors if folder isn't made yet)
    If Dir(basePath, vbDirectory) = "" Then MkDir basePath
 
    ' Update the content control for the date inside the document
    For Each cc In ActiveDocument.ContentControls
        If cc.Tag = "ReportDate" Then
            cc.Range.Text = formattedDate
        End If
    Next cc
 
    ' Define the filename dynamically
    docName = "USER " & formattedDate & " Report - Agenda - Department.docx"
    
    ' Set full save path
    savePath = basePath & docName
    
    ' Save the document as .docx (without macros)
    ActiveDocument.SaveAs2 savePath, wdFormatXMLDocument
    
    ' Notify user of save completion
    MsgBox "Report saved as: " & savePath, vbInformation, "Save Confirmation"
End Sub
 
Private Sub CommandButton1_Click()
    SaveReportAsDocx
End Sub

Key Code Elements

Date Calculation

The script automatically calculates the Monday of the current week using:

mondayDate = Date - Weekday(Date, vbMonday) + 1

This ensures the report is always dated for the start of the week, regardless of when it’s generated.

Dynamic Directory Structure

The code creates a year-based folder structure if it doesn’t already exist:

basePath = "C:\Users\Username\Documents\Reports\" & currentYear & "\"
If Dir(basePath, vbDirectory) = "" Then MkDir basePath

Content Control Management

The script locates and updates the “ReportDate” content control in the document:

For Each cc In ActiveDocument.ContentControls
    If cc.Tag = "ReportDate" Then
        cc.Range.Text = formattedDate
    End If
Next cc

Dynamic Filename Creation

Files are named consistently according to our naming convention:

docName = "USER " & formattedDate & " Report - Agenda - Department.docx"

Proper File Format

The script uses the correct Word XML Document format to ensure compatibility:

ActiveDocument.SaveAs2 savePath, wdFormatXMLDocument

Next Development Steps

Based on this foundation, my next tasks include:

  1. Expanding the script to integrate with our ticketing system API
  2. Adding functionality to extract key metrics from data sources
  3. Implementing email notification upon successful report generation

This script has already eliminated several manual steps in our weekly reporting process and forms the backbone of our continued document automation efforts.

Tags: