Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    <pre>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
    </pre>


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

    <pre>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
    </pre>


    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •