(Excel) Load .crl, Convert to XML, Get Revoked Serial Numbers and Dates

Load a binary .crl file (Certificate Revocation List), converts to XML, and then gets the revoked certificate serial numbers and revocation dates.

Note: This example requires Chilkat v9.5.0.77 or greater.

Download Excel Class Modules Chilkat Excel Class Modules

' This example requires the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

' Load a binary .crl file.
Dim bdCrl As Chilkat.BinData
Set bdCrl = Chilkat.NewBinData

success = bdCrl.LoadFile("qa_data/crl/ca1.crl")
If (success <> True) Then
    Debug.Print "Failed to load CRL file."
    Exit Sub
End If

Dim asn As Chilkat.Asn
Set asn = Chilkat.NewAsn
success = asn.LoadBd(bdCrl)
If (success <> True) Then
    Debug.Print asn.LastErrorText
    Exit Sub
End If

' Convert ASN.1 to XML and load into xml and re-emit for pretty printing..
Dim xml As Chilkat.Xml
Set xml = Chilkat.NewXml
success = xml.LoadXml(asn.AsnToXml())
success = xml.SaveXml("qa_output/crl.xml")

' Use this online tool to generate parsing code from CRL XML: 
' Generate Parsing Code from XML

' Here's code to parse the XML.  This code was generated by the above tool,
' and then we modified it by removing unneeded code and changing some names

Dim dt As Chilkat.CkDateTime
Set dt = Chilkat.NewCkDateTime

i = 0
count_i = xml.NumChildrenHavingTag("sequence")
Do While i < count_i
    xml.I = i

    j = 0
    count_j = xml.NumChildrenHavingTag("sequence[i]|sequence")
    Do While j < count_j
        xml.J = j

        k = 0
        count_k = xml.NumChildrenHavingTag("sequence[i]|sequence[j]|sequence")
        Do While k < count_k
            xml.K = k

            ' Get the revoked certificate's serial number in uppercase hex.
            revokedCertSerialHex = xml.GetChildContent("sequence[i]|sequence[j]|sequence[k]|int")
            Debug.Print "serial number: "; revokedCertSerialHex

            ' Get the date/time revoked.  It will be a string formatted as "YYMMDDhhmmssZ", such as "181023093028Z"
            dateRevoked = xml.GetChildContent("sequence[i]|sequence[j]|sequence[k]|utctime")

            ' Starting in Chilkat v9.5.0.77, date/time strings formatted as YYMMDDhhmmssZ can be parsed as follows:
            success = dt.SetFromTimestamp(dateRevoked)
            Debug.Print "date revoked: "; dt.GetAsRfc822(False)

            k = k + 1
        Loop
        j = j + 1
    Loop
    i = i + 1
Loop