IMPORT *.LOG file into textbox + autosize

[xluser2007]

Hi All,

I have attached a sample LOG file, an output from a SAS program.

I want to see if I can use VBA as a front end to analyse such files programatically, rather than open them up and search visually.

As a first step, my query is:

If I specify a location for this file e.g. "C:\testingSASLOG\errorsampleSAS.log", Could this LOG file be imported into a specified textbox in Excel, and then have the textbox autosized?

The LOG file could potentially be 5000 lines (as a maximum amount for my purposes, but likely to be around 500 lines so that viewing in anExcel front end is reasonable).

I've attached sample LOG file below and a sample workbook front end example, but need to understand how to import it. I've seen the READALL method to scan text files quickly, not sure how to apply it here though.

Any help appreciated with this.

[Paul_Hossler]

Why a text box?

I'd consider loading each line into a seperate row, and do my clean up etc.

A macro could then scan each line for key words, and put a note into another cell

I've always found textboxes difficult to manipulate

Paul

[xluser2007]

Why a text box?

I'd consider loading each line into a seperate row, and do my clean up etc.

A macro could then scan each line for key words, and put a note into another cell

I've always found textboxes difficult to manipulate

Paul

Hi paul,

Great Question.

The textbox is for viewing purposes only.

My question for parsing will come in another thread, depending on how possible the above is. I was hoping the the parsing would be done in VBA when it read the file and not require it to be placed in cells to scan etc i.e. use VBA directly on the LOG file rather than VBA on cells/ ranges for this later purpose.

I've found that there are text display limitations in a worksheet, and I would not want anyone deleting any part of the displayed log in anyway (I;m sure cells could be protected), so a textbox is just a clean interface for displaying, I thought.

Based on your suggestion though, I'm game to experiment.

If you could advise on clean viewing methods or give me any example, I would be eally grateful.

Thanks again for your interest.

[xluser2007]

Hi All, I started writing code as follows (adapted from here (http://www.mrexcel.com/forum/showthread.php?p=1921869)):

http://pubs.logicalexpressions.com/pub0009/images/dot_transp.gif
Option Explicit

Sub test()
Dim fn As String, temp As String, i As Long, r As Range
fn = ThisWorkbook.Worksheets("IMPORTLOG").Range("IMPORTLogName").Value '<- file path for LOG
Debug.Print fn

temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll

ThisWorkbook.Worksheets("IMPORTLOG").Range("A1").Value = temp

End Sub
But this is causing a "Runtime error 76: path not found" error in the line:


temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll

Any ideas why this is not working.

I have reattached my workbook with the above code and the LOG file (which should be saved in the folder as specified in the worksheet for the purposes of the above code).

Any help is apprecaited.

[xluser2007]

Ok, I had the filepath for the LOG incorrect.

the following code works to import the single LOG file as a string.

Sub test_IMPORTSAS_LOG()

Dim strLogName As String, strEntireLOG As String

' Define Full LOG Path as defined in the range "IMPORTLOG"
strLogName = ThisWorkbook.Worksheets("IMPORTLOG").Range("IMPORTLogName").Value '<- file path for LOG
Debug.Print strLogName

' Read in Entire LOG as a single string
strEntireLOG = CreateObject("Scripting.FileSystemObject").OpenTextFile(strLogName).ReadAll
Debug.Print strEntireLOG

' OUTPUT the LOG in the range OutputPasteLOG
With ThisWorkbook.Worksheets("IMPORTLOG").Range("OutputPasteLOG")
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.Value = strEntireLOG
.WrapText = False
End With

End Sub

When it pastes theough it does so as a single string. I would like it to go to a new line at the end of every line. How can I get this to occur?

[xluser2007]

Ok, I have figured it out and started to make good headway into this open problem.

I went with Paul's suggestion of importing the LOG into a cells rather than a textbox.

I thinks it works well so far as IMPORTING the LOG into the cells is concerned.

The current workbook and sample LOG to test are attached in this post below.

A few queries:
1. How do I put a border around the IMPORTED LOG?
2. How do I highlight specific words in the LOG as imported into the various e.g. highlight all "ERROR:" terms yellow at the click of a button, is this possible?

Any help is apreciated

P.S. the current importing code is as follows:

Sub IMPORTSAS_LOG()

Dim strLogName As String, strEntireLOG As String
Dim arrLOG As Variant
Dim lngCounter As Long

' Define Full LOG Path as defined in the range "IMPORTLOG"
strLogName = ThisWorkbook.Worksheets("IMPORTLOG").Range("IMPORTLogName").Value '<- file path for LOG
Debug.Print strLogName

' Check if teh LOG file Exists, if NOT then let USer know to correct before re-running
If FileFolderExists(strLogName) Then

' Read in Entire LOG as a single string
strEntireLOG = CreateObject("Scripting.FileSystemObject").OpenTextFile(strLogName).ReadAll
Debug.Print strEntireLOG

' Define an array of separate line strings of the Entire LOG string
' This delimiter is essentially a newline in the LOG
arrLOG = Split(strEntireLOG, vbNewLine)
Debug.Print LBound(arrLOG)


With ThisWorkbook.Worksheets("IMPORTLOG").Range("OutputPasteLOG")

' Clear ALL contents and formats of the entire column before IMPORTING the LOG
.EntireColumn.Clear

'DISPLAY time at which the LOG was IMPORTED
.Offset(-1, 0).Value = "SAS LOG Imported below at " & Format(Now, "dd/mm/yyyy hh:mm:ss")

.Offset(-1, 0).WrapText = False
.Offset(-1, 0).HorizontalAlignment = xlCenterAcrossSelection
.Offset(-1, 0).VerticalAlignment = xlCenter
.Offset(-1, 0).Interior.ColorIndex = xlCIPaleBlue

With .Offset(-1, 0).Font

.Bold = True
.Italic = True

End With

End With

' OUTPUT the LOG in the range OutputPasteLOG
For lngCounter = LBound(arrLOG) To UBound(arrLOG)

With ThisWorkbook.Worksheets("IMPORTLOG").Range("OutputPasteLOG").Offset(lngCounter, 0)

' Start IMPORTING te LOG line by line
.Value = arrLOG(lngCounter)

' Define attributes of the IMPORT Range i.e.
' NO word wrapping, Left Alignment, Bottom postion of text
.WrapText = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter

End With

Next lngCounter

Call MsgBox("The Specified SAS LOG was imported successfully!" _
& vbCrLf & "" _
, vbInformation Or vbDefaultButton1, "Successful!")


Else

' Display error message in teh case the LOG file doesn't exists
Call MsgBox("The Specified SAS LOG does not exist, please check and CORRECT the LOG path name and try Again." _
& vbCrLf & "" _
& vbCrLf & "" _
& vbCrLf & "" _
, vbCritical Or vbDefaultButton1, "SAS LOG Does not Exist!")

End If

End Sub

[xluser2007]

Is anybody keen to help me out??

I have tried and made good headway. Some expert assistance is always welcome.:)

[p45cal]

re: 1. How do I put a border around the IMPORTED LOG? With ThisWorkbook.Worksheets("IMPORTLOG").Range("OutputPasteLOG").Resize(lngCounter - 1)
ActiveWorkbook.Names.Add Name:="WholeLogRng", RefersTo:="=IMPORTLOG!" & .Address
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End WithYou will note the Names.Add line above; used in the answer to the next question.
This has been added to the attached.

2. How do I highlight specific words in the LOG as imported into the various e.g. highlight all "ERROR:" terms yellow at the click of a button, is this possible?
Add a button and have it call something like this:Sub blah()
Dim WordsArray, cll, wrd
WordsArray = Array("Error", "Errors", "Note", "warning")
For Each cll In Range("WholeLogRng").Cells
For Each wrd In WordsArray
If InStr(UCase(cll.Value), UCase(wrd)) > 0 Then
cll.Characters(Start:=InStr(UCase(cll.Value), UCase(wrd)), Length:=Len(wrd)).Font.ColorIndex = 46 '3
End If
Next wrd
Next cll
End Sub
also added to the attached.

[xluser2007]

pascal,

As usual, you are a great help :thumb. thank you, your suggestions work really well.

I have a few additional enhancements that I would like to make e.g. Having the log change based on a selection event (based on file selected from a dropdown list), but I will need to play with it a bit more.

A small query though. Is it possible to have the Imported LOG Range and the Header be protected via VBA i.e. disallow anyone to paste over, delete etc and flash a warning saying "This is the Imported LOG Range, you may copy from it, but may not modify it any way, Thank you".
(The purpose of this being to avoid having people changing the LOG output :).)

As for pasrsing the LOG for the various error types, I really like your method. I have searched around and found RegExp patterns for all major error types. For searching and displaying these errors, I would like to learn how to extend your highlighting method using Regexp, I will start another thread for this.

thanks and kind regards,

[xluser2007]

I've only slightly amended your code to remove highlighting.

As attached below: