building an error log file in excel (MS Office XP Pro)

Topic

New Reply

We have an excel workbook with 12 worksheets in it that gets sent to our department each month. We import the data from each of the 12 worksheets into 12 corresponding tables in MS Access and do a whole bunch of processing of the data. Because of data entry errors in the worksheets, we frequently get import errors. I wrote a data validation module in the Excel workbook that checks for nulls, empty strings, invalid data types, etc. Each time it finds an error, it concatonates 3 pieces of information (sheet name, row, column) to a global string variable as follows.

gstrError = gstrError & “Error: Sheet ” & strSheet & ” Row ” & intRow & ” Col ” & intCol & vbCrLf

When all 12 worksheets have been checked, we end up with a string who’s value looks like this:

Error: Sheet Client Management Row 1 Col 2
Error: Sheet Client Management Row 7 Col 3
Error: Sheet Client Services Row 7 Col 2
Error: Sheet Commissions Row 7 Col 2
Error: Sheet OnSite Part3 Row 1 Col 7

I want to read the value of this string variable line by line into the last worksheet called “Error Log” so that each line is a new row.
What is the best way to do this?
Thanks,

Reply | Quote

Replies

Why not write each error to the Error Log sheet immediately, instead of first concatenating the strings together and then extracting the individual parts again?

Worksheets(“Error Log”).Range(“A65536”).End(xlUp).Offset(1, 0) = _
“Error: Sheet ” & strSheet & ” Row ” & intRow & ” Col ” & intCol

Reply | Quote

Why build that as a single string? Why not put the errors in an array like this:

Dim lUB As Long
    lUB = 0
    On Error Resume Next
    lUB = UBound(gstrError)
    On Error GoTo 0
    ReDim Preserve gstrError(1 To lUB + 1) As String
    gstrError(lUB + 1) = "Error: Sheet " & strSheet & " Row " & intRow & " Col " & intCol

Then you could copy the array to the error log like this:

Dim I As Long
    With Worksheets("Errorlog")
        .Range("A:A").ClearContents
        For I = 1 To UBound(gstrError)
            .Range("A1").Offset(I - 1).Value = gstrError(I)
        Next I
    End With

Of course, you could also put the errors directly into the log sheet as you find them.

However, what I would really recommend is to build the error checking into the worksheet that collects the data and get the person that is entering the data to correct the errors when the data is being entered. This can be done using a combination of data validation (the Validation command on the Data menu), and various event routines.

If you really must build that single string of error messages, then you can parse it back into its individual errors using the InStr and Left functions.

Reply | Quote