Option Explicit'Word objectsDim wordApp As Word.ApplicationDim DAR_TEMPLATE As Word.documentDim newDAR As Word.document
'Excel objectsDim wb As WorkbookDim dataSheet As WorksheetDim controlSheet As Worksheet
'VariablesDim darIndex As LongDim darCount As LongDim DAR_NUMBER As String
Private Const FF_COUNT As Long = 67
Sub createForms_ButtonClick()'Nolan Manteufel'13JUL2020
'Loglog.logFile ("clicked")
'Initialize objectsSet wb = ActiveWorkbookSet dataSheet = wb.Sheets("Form Data")Set controlSheet = wb.Sheets("Controls")
'Check if the datasheet is emptyIf (dataSheet.UsedRange.Rows.Count = 1) Then MsgBox ("There is no data on the 'Form Data' sheet.")Else generateFormsEnd IfEnd Sub
Sub generateForms()'Nolan Manteufel'13JUL2020
'Initialize word applicationSet wordApp = New Word.Application
'Get row index of last DAR numberdarCount = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Loop through DAR NumbersFor darIndex = 2 To darCount
'Check if DAR number existsIf IsEmpty(dataSheet.Cells(darIndex, 1)) Then 'Skip because no DAR numberElse DAR_NUMBER = dataSheet.Cells(darIndex, 1).Value populateFormEnd IfNext
'Clear word applicationSet wordApp = Nothing
End Sub
Sub populateForm()'Nolan Manteufel'13JUL2020
'VariablesDim ffIndex As LongDim DAR_FORM_PATHFILENAME As StringDAR_FORM_PATHFILENAME = controlSheet.Range("B2").Value
Dim SAVE_FOLDER As StringSAVE_FOLDER = controlSheet.Range("B3").Value
'Open DAR form "template"Set DAR_TEMPLATE = wordApp.Documents.Open(DAR_FORM_PATHFILENAME)
'Create new DAR formDAR_TEMPLATE.SaveAs2 (SAVE_FOLDER & DAR_NUMBER & ".doc")DAR_TEMPLATE.Close savechanges:=FalseSet DAR_TEMPLATE = NothingSet newDAR = wordApp.Documents.Open(SAVE_FOLDER & DAR_NUMBER & ".doc")
'Loop through fieldsFor ffIndex = 1 To FF_COUNTIf newDAR.FormFields(ffIndex).Type = wdFieldFormCheckBox Then 'Checkbox If IsEmpty(dataSheet.Cells(darIndex, ffIndex)) Then newDAR.FormFields(ffIndex).CheckBox.Value = False Else newDAR.FormFields(ffIndex).CheckBox.Value = True End IfElse 'Textbox If IsEmpty(dataSheet.Cells(darIndex, ffIndex)) Then 'Skip Else newDAR.FormFields(ffIndex).result = dataSheet.Cells(darIndex, ffIndex) End IfEnd IfNext
'Clear document variablesnewDAR.Close savechanges:=TrueSet newDAR = Nothing
End Sub