Build a UI / input form for a text file

Option Explicit


Sub ValidateMyFile()

Dim FileName As String
Dim iFileNo As Integer
Dim sCity As String
Dim sDate As String
Dim lLineCounter As Long
Dim lErrorCount As Long
Dim iLinesToSkip As Integer  ' to skip any header lines
iLinesToSkip = 1

On Error GoTo ErrorHandler

'this next line assumes that you have a named range in your workbook
'that contains the full path of the file to process
'FileName = Range("FileNameToProcess")

'or you could prompt the user for a file path\name:
'FileName = InputBox("Enter file path and name: ", "File to Validate")

'for testing I created a test file and hard coded the path\name
FileName = "C:\temp\TestFile.txt"

'check to see if the file exists
If Dir(FileName) = "" Then
    MsgBox "File not found... exiting", vbCritical, "File Validatorizer"
    Exit Sub
End If

iFileNo = FreeFile

Open FileName For Input As iFileNo
lLineCounter = 0

For lLineCounter = 1 To iLinesToSkip
    Input #iFileNo, sCity, sDate
Next

Do While Not EOF(iFileNo)
    lLineCounter = lLineCounter + 1
    
    Input #iFileNo, sCity, sDate
    If Not IsDate(sDate) Then
        MsgBox "Bad date at line: " & lLineCounter & "  for city: " & sCity & " Bad Date: " & sDate
        lErrorCount = lErrorCount + 1
    End If
Loop
Close

MsgBox "Done!" & vbCrLf & vbCrLf & "Processed: " & lLineCounter & " lines" & vbCrLf & "Found: " & lErrorCount & " errors", vbExclamation

Exit Sub

ErrorHandler:

MsgBox Err.Description & vbCrLf & vbCrLf & "Exiting ..."


End Sub