Multi-Form Generator

An Excel Workbook that rapidly generates numerous Word forms.

Sheet1 Source Code

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

ThisWorkbook Source Code

Option ExplicitDim result As Boolean
Private Const workbook_password = "password!"Private Const workbook_owner = "nmanteufel"
Private Sub Workbook_Open()'Nolan Manteufel'13JUL2020
'Logresult = log.logFile("open")
'Get usernameDim userName As StringuserName = Environ$("username")userName = LCase(userName)
'Enable ONLY if official fileDim filePathName As StringfilePathName = Application.ActiveWorkbook.FullName
If filePathName = ActiveWorkbook.Sheets("Controls").Range("B1").Value Then ' Official file ActiveWorkbook.Sheets("Controls").Visible = xlSheetVisible ActiveWorkbook.Sheets("Form Data").Visible = xlSheetVisible ActiveWorkbook.Sheets("File Disabled").Visible = xlSheetVeryHidden MsgBox ("Welcome, " & userName & ". Enjoy the document! :)")Else ' NOT official file ActiveWorkbook.Sheets("File Disabled").Visible = xlSheetVisible ActiveWorkbook.Sheets("Controls").Visible = xlSheetVeryHidden ActiveWorkbook.Sheets("Form Data").Visible = xlSheetVeryHiddenEnd If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)'Nolan Manteufel'13JUL2020
'Logresult = log.logFile("close")
'Adjust sheet visibilityActiveWorkbook.Sheets("File Disabled").Visible = xlSheetVisibleActiveWorkbook.Sheets("Controls").Visible = xlSheetVeryHiddenActiveWorkbook.Sheets("Form Data").Visible = xlSheetVeryHidden
'Save changesActiveWorkbook.Close savechanges:=True
End Sub

log Module

'TODO: Customize these constantsPrivate Const DAR_REVISION As String = "05"Private Const LOG_FILEPATHNAME As String = "S:\Draft Procedures (QA)\Instruments\data\log.xlsx"Private Const LOG_PASSWORD As String = "dxwf&kq&n!"
Function logFile(logMsg As String) As Boolean' Nolan Manteufel' 13JUL2020
' Opens a workbook and logs a message.
'Save application settingsDim applicationState(2) As BooleanapplicationState(0) = Application.ScreenUpdatingapplicationState(1) = Application.EnableEventsapplicationState(2) = Application.DisplayAlerts
'Set application settingsApplication.ScreenUpdating = FalseApplication.EnableEvents = FalseApplication.DisplayAlerts = False
'Open logDim logBook As WorkbookDim logSheet As Worksheet
Set logBook = Workbooks.Open(LOG_FILEPATHNAME)Set logSheet = logBook.Sheets("log")
'Prepare dataDim dataForLog As StringDim timeOfSave As String
dataForLog = Format(Date, "yyyymmdd")timeOfSave = Format(Time, "hhmmss")dataForLog = dataForLog + "_" + _ timeOfSave + "_" + _ Environ$("username") + "_" + _ Environ$("computername") + "_DARGENERATOR_DAR REV: " + _ DAR_REVISION + "_" + _ logMsg
dataForLog = LCase(dataForLog)
'Log dataDim rowIndex As LonglogSheet.Unprotect (LOG_PASSWORD)rowIndex = logSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1logSheet.Range("A" & rowIndex).Value = dataForLoglogSheet.Protect (LOG_PASSWORD)
'Close loglogBook.Close savechanges:=True
Set logSheet = NothingSet logBook = Nothing
'Reset application settingsApplication.ScreenUpdating = applicationState(0)Application.EnableEvents = applicationState(1)Application.DisplayAlerts = applicationState(2)End Function

Page updated

Google Sites

Report abuse