Sub Format_Log2()
'
' This Macro makes Excel a viewer for the PCRE Report Server
' Log files.  Hopefully the logs are easy to read within Excel.
'
' Excel 2000 Macro recorded & manually edited on Jan. 28, 2002 by RZ
' Modified on July 11, 2002 by RZ to move the cleaning out of unprintable
' characters earlier in the script.  Also renamed with .bas suffix.
' Control Structures (Do Loop, If Then) were manually edited.
' Any criticism for their non-OOP quality should be directed
' to Dynalivery marketing, rather than Dynalivery engineering;)
'
' Sorts by Job #, Bold for line with *.rpt,
' Yellow Background for line with PCRE Job Started,
' Red & Bold for line with Errors/Signficant Warnings
' Cleans out Unprintable characters; All lines included;
' Prompts for Report Server Log File Name
'
' Quantity = "As Is", No Warranty, Have Fun with it.
'
' Remember Excel's limitations of 64,000 lines per worksheet
' which may limit the size of log file that you can use with this
' macro (without some extra work).
'
' Please Be Patient with the speed and redisplay, VBA is interpreted
'
'******************************************
'
' Import Text File (.log) into Excel
'
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\PCRE.log" _
        , Destination:=Range("A1"))
 '      .Name = "report_server"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
'
'       Causes prompt for log file
'
        .TextFilePromptOnRefresh = True
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(3, 1, 1, 2)
        .TextFileFixedColumnWidths = Array(8, 9, 14)
        .Refresh BackgroundQuery:=False
    End With
'
'   Resize the whole worksheet for 8 Point Arial Fonts
'
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
'
'   Center Justify the Date, Time, and Job #
'
    Columns("A:C").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
'
'   Display the Job # as an integer (zero decimal places) and
'   add margins in this column for greater readability
'
    Columns("C:C").Select
    Selection.NumberFormat = "0"
    Columns("C:C").Select
    Selection.ColumnWidth = 14.14
'
'   Clean out unprintable characters with Clean function and copy values
'
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=CLEAN(RC[-1])"
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Columns("E:E").Select
    Selection.Copy
    Columns("F:F").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Columns("D:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.ColumnWidth = 108.14
'
'   Sort by Job #
'
    Cells.Select
    Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'
' Highlight Rows with Errors, .rpt, & Start of Job
'
    Range("D1").Select
    Do While Len(ActiveCell) > 1
       If ActiveCell.Value Like "*Unable to get*" Or _
          ActiveCell.Value Like "*Exception*" Or _
          ActiveCell.Value Like "*timeout*" Or _
          ActiveCell.Value Like "*Error*" _
       Then
          With Selection.Font
            .FontStyle = "Bold"
            .ColorIndex = 3 'Red
          End With
       End If
'
       If ActiveCell.Value Like "*.rpt*" _
       Then
          With Selection.Font
             .FontStyle = "Bold"
          End With
       End If
'
       If ActiveCell.Value Like "PCRE*) started*" _
       Then
          With Selection.Interior
             .ColorIndex = 6
             .Pattern = xlSolid
          End With
       End If
'
       If ActiveCell.Value Like "*Couldn't obtain*" _
       Then
          With Selection.Interior
             .ColorIndex = 45 'Light Orange
             .Pattern = xlSolid
          End With
          With Selection.Font
             .FontStyle = "Bold"
          End With
       End If
'
'   Get the next cell in the range (single column)
'
       ActiveCell.Offset(1, 0).Select
    Loop
End Sub