Option Explicit
Option Base 1
Private Sub Document_New()
Dim InputFilePathIn As String 'Input file from ICIS
Dim ErrorFilePathOut As String 'Output error log containing all
fields not processed
Dim fileinputchar As String 'Holds full input line before
field split
Dim InputFieldName As String 'After Split - ICIS field name
Dim InputValue As String 'After Split - ICIS field value
Dim DocBookmark As String 'Word Bookmark - will contain
InputValue
Dim DocLabel As String 'Word Field Label - will contain
labels retrieved from DB
Dim x As Integer 'Character Count for
fileinputchar processing
Dim db As Database 'Database
Dim rs As Recordset
Dim errorlogopen As String 'Used to check if the error log
is open
Dim ErrorLogtext As String 'Error log text
Dim CompareCollection As New Collection 'Holds InputFieldName and
InputValue for comparison
Dim ProcessedData() As Variant
Dim CollectionValue As String
Dim CollectionFieldName As String
Dim Count As Integer
InputFilePathIn = "C:\Documents and Settings\ABC\Desktop\summarytest.txt"
ErrorFilePathOut = "C:\Documents and Settings\ABC\Desktop\summaryerror.txt"
errorlogopen = "no"
Set db = OpenDatabase("C:\Documents and
Settings\lid0rb8\Desktop\benefitsummaryfieldcodes")
Set rs = db.OpenRecordset("Fieldcodes", dbOpenTable)
With rs
.Index = "FieldName"
'Clear Values from database
rs.MoveFirst
Do Until rs.EOF
.Edit
.Fields("DisplayValue").Value = " "
.Update
rs.MoveNext
Loop
Open InputFilePathIn For Input As #1
Do While Not EOF(1)
Line Input #1, fileinputchar
fileinputchar = LTrim(fileinputchar)
'Cycle thru the line to search for terminating character and then split
the line into
'the field name and the value to update. This calls a function
RemoveLeadingYes because
'the summaries only contain the Yes if no other text is present.
If Mid(fileinputchar, 1, 1) = "s" Then
Else
If Mid(fileinputchar, 2, 4) = "ICIS" Then
ActiveDocument.FormFields("ICISPRODID").Result = fileinputchar
Else
For x = 1 To Len(fileinputchar)
If Mid(fileinputchar, x, 1) = "~" Then
InputFieldName = Left(fileinputchar, x)
InputValue = RemoveLeadingYes(Right(fileinputchar,
Len(fileinputchar) - (x + 1)))
End If
Next x
If Mid(InputFieldName, 1, 1) = "X" Then
Else
rs.MoveFirst
rs.Seek "=", InputFieldName
If rs.NoMatch Then
If errorlogopen = "no" Then
errorlogopen = "yes"
Open ErrorFilePathOut For Output As #2
ErrorLogtext = "File - " & InputFilePathIn
Write #2, ErrorLogtext
End If
ErrorLogtext = "Field - " & InputFieldName & ", " &
InputValue
Write #2, ErrorLogtext
Else
If rs.Fields("Bookmark") = "NA" Then
Else
InputValue = Left(InputValue, 255)
.Edit
.Fields("DisplayValue").Value = InputValue
.Update
Count = Count + 1
ReDim Preserve ProcessedData(4, Count)
ProcessedData(1, Count) = rs.Fields("Bookmark")
ProcessedData(2, Count) = InputValue
ProcessedData(3, Count) =
rs.Fields("CompareFieldName")
ProcessedData(4, Count) = rs.Fields("Label")
'MsgBox ProcessedData(1, Count) & ProcessedData(2,
Count) & ProcessedData(3, Count) & ProcessedData(4, Count)
End If
End If
End If
End If
End If
Loop
Close #1
For x = 1 To Count
DocBookmark = ProcessedData(1, x)
DocLabel = ProcessedData(4, x) & " - "
ActiveDocument.FormFields(DocBookmark).Result = DocLabel
ActiveDocument.Bookmarks(DocBookmark).Select
Selection.InsertAfter ProcessedData(2, x)
Next x
ActiveDocument.Activate
End With
End Sub
