Wednesday, February 17, 2010

Recursive search on the filesystem for the content of a log-file

I had the following problem lately:

On a test server I was looking for a log-file on the installation path and/or one of its sub-folders with some information about a "session timeout".

I didn't know the the log-files name, but new, that it had the extension ".log". I also didn't know which exception was was creating the entry. All I had, was the string "session timeout".

With the following Groovy script I could find all the log-files containing the string "session timeout" and from there it was easy to identify the log-file.


def dir = 'C:/some_installation_folder'
def fileExtension = '.log'
def searchString = 'session timeout'

new File(dir).eachFileRecurse() { f ->
if (f.directory) {
def p = ~/.*\${fileExtension}/
f.eachFileMatch(p) { log ->
def exceptions = []

log.eachLine { ln ->
if (ln =~ '.*' + searchString + '.*') {
exceptions << "${ln}"
}
}

if (!exceptions.empty) {
println log
exceptions.each{println " $it"}
}
}
}
}


Enjoy! :)

Tuesday, January 19, 2010

Date Format Fix in Excel 2003

Due to an issue in Excel 2003, sometime the date formats are not updated in the cells, which leads to the problem, that a cell shows a date but is not read as a date by macros and formulas.

You could go through each cell, press 'F2' and 'Enter' and Excel would finally identify the content of the cell as a date. But this you would do manually and not by script.

With the following VBA script for Excel, the format can be updated:


Sub UpdateColumnFormat()

Dim usedRangeOfRows As Integer
usedRangeOfRows = ActiveSheet.UsedRange.Rows.Count

'Date
Columns("A:A").Select
Dim e As Range
Dim currentValueE As Date
For Each e In Range("A2:A" & usedRangeOfRows).Cells
e.Select
currentValueE = ActiveCell.Value
ActiveCell.FormulaR1C1 = currentValueE
Next

End Sub


This problem occurs regularly, when I copy data from one sheet to another by VBA scripts.

With the above script, I could fix (at least work around it) this issue.

Enjoy :)

Monday, January 18, 2010

"Create List..." in Excel 2003 with VBA

While working on data within Excel with VBA, I like to use dynamic lists to address the data and eventually to give it a name.

To create a list, the same one as you would do manually by calling "Create List..." from the context menu, you can execute the VBA script below.


Private Sub CreateList()

Range("A1").Select

Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Dim NumberOfColumns As Integer
Dim NumberOfRows As Integer
Dim StartCell As Range
Dim EndCell As Range

NumberOfColumns = ActiveSheet.UsedRange.Columns.Count
NumberOfRows = ActiveSheet.UsedRange.Rows.Count

Set StartCell = Cells(1, 1)
Set EndCell = Cells(NumberOfRows, NumberOfColumns)

Dim DataRange As Range
Set DataRange = Range(StartCell, EndCell)

ActiveSheet.ListObjects.Add(xlSrcRange, DataRange, , xlYes).Name = "List1"
Range(StartCell, EndCell).Select
ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:=DataRange
Range("Data").Select

End Sub


I also give the list the name "Data", which I do to access the data by name afterwords, but this is not required to create the list.

Enjoy :)