Shared Drive Templates vs Managed Output Folders in Batch Generation

Option Explicit

Sub GenerateBatchDocuments()
    Const TEMPLATE_PATH As String = "\\SharedDrive\Templates\MasterTemplate.docx"
    Const OUTPUT_ROOT As String = "C:\BatchOutputs"
    Const PDF_EXPORT As Boolean = True 'Set to False to skip PDF creation

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Data")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim batchFolder As String
    batchFolder = CreateBatchFolder(OUTPUT_ROOT)

    Dim i As Long
    For i = 2 To lastRow 'Assume headers in row 1
        Dim fileName As String
        fileName = Trim(ws.Cells(i, "C").Value) 'Column C holds the desired base name
        If fileName = "" Then
            fileName = "Document_" & i
        End If
        Dim docPath As String
        docPath = batchFolder & "\" & fileName & ".docx"

        Dim wdApp As Object
        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = False

        Dim wdDoc As Object
        Set wdDoc = wdApp.Documents.Open(TEMPLATE_PATH, ReadOnly:=True)

        'Replace bookmarks with row data
        Call ReplaceBookmarks(wdDoc, ws, i)

        wdDoc.SaveAs2 docPath, 16 'wdFormatXMLDocument

        If PDF_EXPORT Then
            Dim pdfPath As String
            pdfPath = batchFolder & "\" & fileName & ".pdf"
            wdDoc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17 'wdExportFormatPDF
        End If

        wdDoc.Close SaveChanges:=False
        wdApp.Quit
        Set wdDoc = Nothing
        Set wdApp = Nothing
    Next i

    MsgBox "Batch generation complete. Files saved to: " & batchFolder, vbInformation
End Sub

Function CreateBatchFolder(rootPath As String) As String
    Dim ts As String
    ts = Format(Now, "yyyy_mm_dd_HHnnss")
    Dim newFolder As String
    newFolder = rootPath & "\Batch_" & ts
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(rootPath) Then fso.CreateFolder rootPath
    fso.CreateFolder newFolder
    CreateBatchFolder = newFolder
End Function

Sub ReplaceBookmarks(wdDoc As Object, ws As Worksheet, rowIdx As Long)
    Dim bm As Object
    For Each bm In wdDoc.Bookmarks
        Dim col As Long
        col = BookmarkColumn(bm.Name)
        If col > 0 Then
            Dim val As String
            val = ws.Cells(rowIdx, col).Value
            bm.Range.Text = val
        End If
    Next bm
End Sub

Function BookmarkColumn(bmName As String) As Long
    'Map bookmark names to column numbers – adjust as needed
    Select Case LCase(bmName)
        Case "customername": BookmarkColumn = 2 'Column B
        Case "address": BookmarkColumn = 3 'Column C
        Case "invoicedate": BookmarkColumn = 4 'Column D
        Case Else: BookmarkColumn = 0
    End Select
End Function