CODEWITHSUNDEEP
excelvbabase64hex
10101
10101

Reputation: 2402

Convert a hex to base64 in Excel

I know there is already existing topic with the same name Convert a hex string to base64 in an excel function However I am not receiving the same result as I am expecting by VBA provided in the thread. I am using this website and it provides the correct answer https://cryptii.com/pipes/base64-to-hex

It looks like there are 12 unnecessary characters in result provided by Hex2Base64. How to make it work like a website?

I know there is simple solution to input to another cell something like: =LEFT(cell;20) but how it is possible to make with current VBA?

So I have:

HEX: 01 00 00 00 05 00 00 00 00 E3 07 04 00 0F 00

On the website I receive BASE64: AQAAAAUAAAAA4wcEAA8A

enter image description here

By solution provided, in already existing thread, I receive BASE64: AQAAAAUAAAAA4wcEAA8AAAAAAAAAAA==

Function Hex2Base64(byVal strHex)
    Dim arrBytes
    If Len(strHex) Mod 2 <> 0 Then
        strHex = Left(strHex, Len(strHex) - 1) & "0" & Right(strHex, 1)
    End If
    With CreateObject("Microsoft.XMLDOM").createElement("objNode")
        .DataType = "bin.hex"
        .Text = strHex
        arrBytes = .nodeTypedValue
    End With
    With CreateObject("Microsoft.XMLDOM").createElement("objNode")
        .DataType = "bin.base64"
        .nodeTypedValue = arrBytes
        Hex2Base64 = .Text
    End With
End Function

Upvotes: 1

Views: 1371

Answers (1)

BrakNicku
BrakNicku

Reputation: 5991

The problem is with input parameter - hex string with spaces. Removing spaces gives expected results:

Debug.Print Hex2Base64(Replace("01 00 00 00 05 00 00 00 00 E3 07 04 00 0F 00", " ",""))

Upvotes: 2

Related Questions