Module1.bas

Attribute VB_Name = "Module1"
Option Explicit

Dim selectedFile As String
Dim selectedFileName As String

Sub KNX0600_FullWorkflow_Log()
    Dim fd As FileDialog
    Dim wsData As Worksheet
    Dim wsNew As Worksheet
    Dim wsControl As Worksheet
    Dim searchCol As Range
    Dim searchValues As Variant
    Dim searchValue As Variant
    Dim foundCell As Range
    Dim lastRow As Long
    Dim lastBRow As Long
    Dim lastTRow As Long
    Dim lastQRow As Long
    Dim found As Boolean
    Dim cell As Range
    Dim time1 As Date
    Dim time2 As Date
    Dim diffMinutes As Long
    Dim hoursPart As Long
    Dim minutesPart As Long
    Dim totalHours As Double
    Dim timeStatus As String
    Dim chargeStatus As String
    Dim rsocStatus As String
    Dim tocSheet As Worksheet
    Dim tocRow As Long
    
    ' --------------------------
    ' Step 1: Select a .log file and import data
    ' --------------------------
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = False
    fd.Title = "Select a .log file"
    fd.Filters.Clear
    fd.Filters.Add "Log Files", "*.log"
    
    If fd.Show = -1 Then
        selectedFile = fd.SelectedItems(1)
        selectedFileName = Mid(selectedFile, InStrRev(selectedFile, "\") + 1)
        selectedFileName = Left(selectedFileName, InStrRev(selectedFileName, ".") - 1)
        
        ' Remove existing DataImport sheet if it exists
        On Error Resume Next
        Set wsData = ThisWorkbook.Sheets("DataImport")
        If Not wsData Is Nothing Then wsData.Delete
        On Error GoTo 0
        
        ' Create new sheet for imported data
        Set wsData = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
        wsData.Name = "DataImport"
        
        ' Import the .log file as text (tab-delimited)
        With wsData.QueryTables.Add(Connection:="TEXT;" & selectedFile, Destination:=wsData.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileOtherDelimiter = vbTab
            .TextFileColumnDataTypes = Array(1)
            .AdjustColumnWidth = True
            .Refresh BackgroundQuery:=False
            .Delete
        End With
        
    Else
        MsgBox "No file selected."
        Exit Sub
    End If
    
    ' --------------------------
    ' Step 2: Create new sheet named after file and copy raw data
    ' --------------------------
    On Error Resume Next
    Set wsNew = ThisWorkbook.Sheets(selectedFileName)
    On Error GoTo 0
    
    If wsNew Is Nothing Then
        Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsNew.Name = selectedFileName
    End If
    
    wsData.UsedRange.Copy wsNew.Range("A1")
    
    ' --------------------------
    ' Step 3: Perform KNX0600 functional tests on wsNew
    ' --------------------------
    ' Part 1: Search column Q for 100, 99, 98 and prune rows below
    Set searchCol = wsNew.Columns("Q")
    searchValues = Array("100", "99", "98")
    found = False
    
    For Each searchValue In searchValues
        Set foundCell = searchCol.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            found = True
            Exit For
        End If
    Next searchValue
    
    If found Then
        lastRow = wsNew.Cells(wsNew.Rows.Count, foundCell.Column).End(xlUp).Row
        If foundCell.Row < lastRow Then
            wsNew.Rows(foundCell.Row + 1 & ":" & lastRow).Delete
        End If
    End If
    
    ' Part 2: Copy last remaining cell in column B to F4 (24-hour hh:mm)
    lastBRow = wsNew.Cells(wsNew.Rows.Count, "B").End(xlUp).Row
    wsNew.Range("F4").Value = wsNew.Cells(lastBRow, "B").Value
    wsNew.Range("F4").NumberFormat = "hh:mm"
    
    ' Part 3: Find first time-formatted cell in column B and copy to F5 (24-hour hh:mm)
    For Each cell In wsNew.Range("B1:B" & wsNew.Cells(wsNew.Rows.Count, "B").End(xlUp).Row)
        If InStr(1, cell.NumberFormat, "h", vbTextCompare) > 0 Or _
           InStr(1, cell.NumberFormat, "m", vbTextCompare) > 0 Then
            wsNew.Range("F5").Value = cell.Value
            wsNew.Range("F5").NumberFormat = "hh:mm"
            Exit For
        End If
    Next cell
    
    ' Part 4: Insert "Total Time Elapsed" into F3
    wsNew.Range("F3").Value = "Total Time Elapsed"
    
    ' Calculate elapsed time between F4 and F5 and display in F6 as hh:mm
    time1 = wsNew.Range("F4").Value
    time2 = wsNew.Range("F5").Value
    diffMinutes = Abs(DateDiff("n", time1, time2))
    hoursPart = diffMinutes \ 60
    minutesPart = diffMinutes Mod 60
    wsNew.Range("F6").Value = Format(hoursPart, "00") & ":" & Format(minutesPart, "00")
    
    ' Highlight F6 and determine time status
    totalHours = diffMinutes / 60
    If totalHours <= 3 Then
        wsNew.Range("F6").Interior.Color = RGB(0, 255, 0)
        timeStatus = "PASS"
    Else
        wsNew.Range("F6").Interior.Color = RGB(255, 0, 0)
        timeStatus = "FAIL"
    End If
    
    ' Part 5: Full Charge Capacity
    wsNew.Range("F8").Value = "Full Charge Capacity"
    lastTRow = wsNew.Cells(wsNew.Rows.Count, "T").End(xlUp).Row
    wsNew.Range("F9").Value = wsNew.Cells(lastTRow, "T").Value
    
    If wsNew.Range("F9").Value >= 1840 Then
        wsNew.Range("F9").Interior.Color = RGB(0, 255, 0)
        chargeStatus = "PASS"
    Else
        wsNew.Range("F9").Interior.Color = RGB(255, 0, 0)
        chargeStatus = "FAIL"
    End If
    
    ' Part 6: Relative State of Charge (RSOC)
    wsNew.Range("F11").Value = "Relative State of Charge"
    lastQRow = wsNew.Cells(wsNew.Rows.Count, "Q").End(xlUp).Row
    If lastQRow > 1 Then
        wsNew.Range("F12").Value = wsNew.Cells(lastQRow, "Q").Value
        If wsNew.Range("F12").Value >= 98 Then
            wsNew.Range("F12").Interior.Color = RGB(0, 255, 0)
            rsocStatus = "PASS"
        Else
            wsNew.Range("F12").Interior.Color = RGB(255, 0, 0)
            rsocStatus = "FAIL"
        End If
    Else
        wsNew.Range("F12").Value = "N/A"
        wsNew.Range("F12").Interior.Color = RGB(255, 0, 0)
        rsocStatus = "FAIL"
    End If
    
    ' --------------------------
    ' Step 7: Delete temporary DataImport sheet
    ' --------------------------
    Application.DisplayAlerts = False
    wsData.Delete
    Application.DisplayAlerts = True
    
    ' --------------------------
    ' Step 8: Update Table of Contents
    ' --------------------------
    Set tocSheet = ThisWorkbook.Sheets("Import and Analyze")
    tocRow = 8
    Do While Not IsEmpty(tocSheet.Cells(tocRow, 1))
        tocRow = tocRow + 1
    Loop
    tocSheet.Hyperlinks.Add Anchor:=tocSheet.Cells(tocRow, 1), _
                            Address:="", _
                            SubAddress:="'" & wsNew.Name & "'!A1", _
                            TextToDisplay:=wsNew.Name
    
    ' --------------------------
    ' Step 9: Return to "Import and Analyze" only if all tests pass
    ' --------------------------
    If timeStatus = "PASS" And chargeStatus = "PASS" And rsocStatus = "PASS" Then
        tocSheet.Activate
    Else
        wsNew.Activate
    End If
    
    ' --------------------------
    ' Step 10: Display final message
    ' --------------------------
    MsgBox "Workflow complete!" & vbCrLf & _
           "New sheet created: " & selectedFileName & vbCrLf & _
           "Time Test: " & timeStatus & " " & Format(wsNew.Range("F6").Value, "h""h ""mm""m""") & vbCrLf & _
           "Full Charge Capacity: " & chargeStatus & " " & wsNew.Range("F9").Value & " mAh" & vbCrLf & _
           "Relative State of Charge (RSOC): " & rsocStatus & " " & wsNew.Range("F12").Value & " %"
End Sub