Hi Typia
1. Create a new Excel workbook with two worksheets: 'PivotTable' and 'Datasheet'
2. Navigate to this location on the machine where you are running the Keystore service for SPARX EA: C:\Program Files (x86)\Sparx Systems\Keystore\Service\Logs
3. There should be 3 log files there named : ssksLog-?
4. Open one of the log files, copy the contents and paste them into cell A1 of the worksheet 'Datasheet'
5. Paste the code below into the VB editor of the Excel workbook and run it :
Sub HideRows()
beginrow = 1
chkcol = 1
rowcnt = beginrow
Do Until IsEmpty(Cells(rowcnt, chkcol))
If InStr(1, Cells(rowcnt, chkcol), "[INFO]: CHECKOUT SUCCESS") = 0 And InStr(1, Cells(rowcnt, chkcol), "[INFO]: CHECKIN SUCCESS") = 0 Then
Cells(rowcnt, chkcol).EntireRow.Delete
Else: rowcnt = rowcnt + 1
End If
Loop
Txt2Col
End Sub
Sub Txt2Col()
Dim rng As Range
Set rng = [A1]
Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Rows(1).Insert shift:=xlShiftDown
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Time"
Cells(1, 3).Value = "c"
Cells(1, 4).Value = "State"
Cells(1, 5).Value = "e"
Cells(1, 6).Value = "User"
Cells(1, 7).Value = "g"
Cells(1,
.Value = "h"
Cells(1, 9).Value = "i"
Cells(1, 10).Value = "j"
Cells(1, 11).Value = "k"
Cells(1, 12).Value = "l"
Cells(1, 13).Value = "Key"
Cells(1, 14).Value = "n"
Cells(1, 15).Value = "o"
Cells(1, 16).Value = "p"
Cells(1, 17).Value = "Userpc"
Cells(1, 18).Value = "ExpiryDate"
Cells(1, 19).Value = "ExpiryTime"
newPVT
End Sub
Sub newPVT()
'Declare Variables
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim LastRow As Long
Dim LastCol As Long
'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "PivotTable"
Application.DisplayAlerts = True
Set PSheet = Worksheets("PivotTable")
Set DSheet = Worksheets("DataSheet")
'Define Data Range
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _
TableName:="SalesPivotTable")
'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
'Insert Row Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("User")
.Orientation = xlRowField
.Position = 1
End With
'With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month")
' .Orientation = xlRowField
' .Position = 2
'End With
'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Date")
.Orientation = xlDataField
.Position = 1
.Function = xlCount
' .NumberFormat = "#,##0"
.Name = "Users"
'Insert Column Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("State")
.Orientation = xlPageField
.Position = 1
.CurrentPage = "CHECKIN"
End With
'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
ActiveSheet.PivotTables("SalesPivotTable").ColumnGrand = False
ActiveSheet.PivotTables("SalesPivotTable").RowGrand = False
End With
End Sub
