Gegeben:

Rohdaten aus Logbuch Export im ADI-Format aus MSHV 2.41

Gesucht:

Eine Möglichkeit zur Auswertung und weiteren Verarbeitung der Logdatei in Excel 2016. Das sollte auch in einer O365-Umgebung funktionieren.

Gefunden:

Damit mir die Zeilen nicht irgendwann einmal abhanden kommen, schreibe ich sie mir hier nieder.

Sub Import_adi()
    Dim j, k, length  As Long
    Dim varName As Variant
    Dim arLines() As String
    Dim Line As Variant
    Dim column As Variant
    Dim arColumns As Variant
    
    ' suppress screen activities during import
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
            
    ' Open File
    varName = Application.GetOpenFilename("ADIF-Dateien (*.adi),*.adi,Alle Dateien, *.*")
    If varName = False Then Exit Sub
    
    ' ADIF has no CRLF, only LF,
    ' so split the file data to lines and put them into an array
    arLines = Split(ReadFile(varName), Chr(10))
    
    
    ' Exported columns from MSHV 2.41 in ADIF Version 3.1.0
    ' Non-standard fields: "APP_N1MM_EXCHANGE1", "ARRL_SECT", "APP_N1MM_RUN1RUN2"
    arColumns = Array("STATION_CALLSIGN", "MY_GRIDSQUARE", "CALL", "GRIDSQUARE", "MODE", "RST_SENT", "RST_RCVD", "QSO_DATE", "TIME_ON", "QSO_DATE_OFF", "TIME_OFF", "BAND", "FREQ", "PROP_MODE", "COMMENT", "SRX", "STX", "APP_N1MM_EXCHANGE1", "ARRL_SECT", "APP_N1MM_RUN1RUN2")
    
  
    ' define head line
    For k = 0 To UBound(arColumns)
        Cells(1, k + 1).Value = arColumns(k)
    Next
    
    ' start import at line j (1 = title line)
    j = 2
    
    ' go through each line of the file
    For Each Line In arLines
        ' only lines containing <EOR> seem to be a valid line for QSO logging, others to ignore
        If InStr(Line, "<EOR>") Then
            k = 1
            ' now check data to be imported depending on the given length
            For Each column In arColumns
                Line = Mid(Line, InStr(Line, "<" + column + ":") + Len(column) + 2)
                length = Val(Line)
                Cells(j, k).Value = Mid(Line, IIf(length > 9, 4, 3), length)
                k = k + 1
            Next
            j = j + 1
        End If
    Next
    
    ' set Autofilter
    Range("A1:U1").AutoFilter
    
    ' set Autofit
    Range("A:U").columns.AutoFit
    
    ' set FreezePane
    ActiveWindow.FreezePanes = False
    Application.Goto ActiveSheet.Cells(1, 1)
    With ActiveWindow
        .SplitColumn = 2
        .SplitRow = 1
        .FreezePanes = True
    End With

End Sub

' standard function for reading a file
' thanks to the internet
Public Function ReadFile(ByVal strFileName As String) As String
    Dim intHandle As Integer
    intHandle = FreeFile
    Open strFileName For Input As #intHandle
    ReadFile = Input(LOF(intHandle), #intHandle)
    Close #intHandle
End Function

Die Liste der Spalten kann leicht an andere Umgebungen angepasst werden. Die Beschreibung des ADI-Formats ist ausführlich erklärt und bei https://adif.org erhältlich.