Observation Form

Enters form into an Excel log and saves the form as a PDF.

Content Controls

This example uses Content Controls. There is no functional difference than using Form Fields, but the VBA code is slightly different and the visual appearance is more modern.

Source Code

VERSION 1.0 CLASSBEGIN MultiUse = -1 'TrueENDAttribute VB_Name = "ThisDocument"Attribute VB_GlobalNameSpace = FalseAttribute VB_Creatable = FalseAttribute VB_PredeclaredId = TrueAttribute VB_Exposed = True'Used to move documentsPrivate Const PDF_FILEPATH As String = "Y:\ENG\Observation Form\Observation Log\PDFs\"Private Const OBSERVATION_LOG_FILEPATHNAME As String = "Y:\ENG\Observation Form\Observation Log\Observation Log.xlsx"Private Const OBSERVATION_LOG_PASSWORD As String = "password!"
'Used to access Content ControlsPrivate Const oTitle = "observationTitle"Private Const oType = "observationType"Private Const oObservation = "observation"Private Const oCaption1 = "caption1"Private Const oCaption2 = "caption2"
'Used to access data arrayEnum formInputEnums iUser iComputer iDatetime iTitle iType iObservation iCaption1 iCaption2 iPDFEnd Enum
Private Sub submitButton_Click()
'Logs form in OBSERVATION_LOG_FILEPATHNAME excel file.'Protects the OBSERVATION_LOG_FILEPATHNAME excel file with OBSERVATION_LOG_PASSWORD.'Saves pdf of form in PDF_FILEPATH folder.
'VariablesDim formDoc As DocumentSet formDoc = ActiveDocumentDim cc As ContentControls
Dim excelApp As Excel.ApplicationDim logWB As Excel.WorkbookDim logSheet As Excel.WOrksheet
'UserDim formUser As StringformUser = Environ$("username")formUser = LCase(formUser)
'ComputerDim formComputer As StringformComputer = Environ$("computername")formComputer = LCase(formComputer)
'DatetimeDim dateTime As StringDim timeOnly As StringtimeOnly = Format(Time, "hhmmss")dateTime = Format(Date, "yyyymmdd")dateTime = dateTime & timeOnly
'Inputs arrayDim formInputs(8) As String
'Populate metadataformInputs(iUser) = formUserformInputs(iComputer) = formComputerformInputs(iDatetime) = dateTime
'Populate dataSet cc = formDoc.SelectContentControlsByTag(oTitle)formInputs(iTitle) = cc(1).Range.Text
Set cc = formDoc.SelectContentControlsByTag(oType)formInputs(iType) = cc(1).Range.Text
Set cc = formDoc.SelectContentControlsByTag(oObservation)formInputs(iObservation) = cc(1).Range.Text
Set cc = formDoc.SelectContentControlsByTag(oCaption1)formInputs(iCaption1) = cc(1).Range.Text
Set cc = formDoc.SelectContentControlsByTag(oCaption2)formInputs(iCaption2) = cc(1).Range.Text
formInputs(iPDF) = PDF_FILEPATH & dateTime & "_" & formUser & ".pdf"
'Launch excelSet excelApp = New Excel.Application
'The workbookSet logWB = excelApp.Workbooks.Open(FileName:=OBSERVATION_LOG_FILEPATHNAME)
'The worksheetSet logSheet = logWB.Worksheets("Observations")
'Unprotect the loglogSheet.Unprotect (OBSERVATION_LOG_PASSWORD)
'Log the entryDim observationPKey As LongobservationPKey = logSheet.UsedRange.Rows.Count + 1logSheet.Cells(observationPKey, 1).Value = observationPKeylogSheet.Cells(observationPKey, 2).Value = formInputs(iDatetime)logSheet.Cells(observationPKey, 3).Value = formInputs(iUser)logSheet.Cells(observationPKey, 4).Value = formInputs(iComputer)logSheet.Cells(observationPKey, 5).Value = formInputs(iType)logSheet.Cells(observationPKey, 6).Value = formInputs(iTitle)logSheet.Cells(observationPKey, 7).Value = formInputs(iObservation)logSheet.Cells(observationPKey, 8).Value = formInputs(iCaption1)logSheet.Cells(observationPKey, 9).Value = formInputs(iCaption2)logSheet.Cells(observationPKey, 10).Value = formInputs(iPDF)
'Protect the loglogSheet.Protect (OBSERVATION_LOG_PASSWORD)
'Close loglogWB.Close savechanges:=True
'Release object referencesSet cc = NothingSet logSheet = NothingSet logWB = NothingSet excelApp = Nothing
'Save as PDFformDoc.ExportAsFixedFormat _ OutputFileName:=formInputs(iPDF), _ ExportFormat:=wdExportFormatPDF, _ OpenAfterExport:=False
End Sub

Tools > References

In the VBA editor, navigate to Tools > References, and select the Microsoft Excel XX.X Object Library.

Page updated

Google Sites

Report abuse