Incorrect base64 encoding VBA

Question

I'm trying to encode a JPG image into Base64 format for HTML (<img src="data:image/jpg .." />) in VBA Excel.

I found a similiar question, Convert image (jpg) to base64 in Excel VBA?, and tried the code:

'https://stackoverflow.com/questions/2043393/convert-image-jpg-to-base64-in-excel-vba
Sub EncodeFile()

Dim strPicPath: strPicPath = "MyDriveHere:\SomePath\image.jpg" 
'Credit to Wikipedia for orange picture:    'https://upload.wikimedia.org/wikipedia/commons/thumb/1/10/Citrus_reticulata_April_2013_Nordbaden.JPG/220px-Citrus_reticulata_April_2013_Nordbaden.JPG
Const adTypeBinary = 1          ' Binary file is encoded

' Variables for encoding
Dim objXML
Dim objDocElem

' Variable for reading binary picture
Dim objStream

' Open data stream from picture
Set objStream = CreateObject("ADODB.Stream")
objStream.Type = adTypeBinary
objStream.Open
objStream.LoadFromFile (strPicPath)

' Create XML Document object and root node
' that will contain the data
Set objXML = CreateObject("MSXml2.DOMDocument")
Set objDocElem = objXML.createElement("Base64Data")
objDocElem.DataType = "bin.base64"

' Set binary value
objDocElem.nodeTypedValue = objStream.Read()

' Get base64 value
Debug.Print objDocElem.text

' Clean all
Set objXML = Nothing
Set objDocElem = Nothing
Set objStream = Nothing

End Sub

Using the image here, https://upload.wikimedia.org/wikipedia/commons/thumb/1/10/Citrus_reticulata_April_2013_Nordbaden.JPG/220px-Citrus_reticulata_April_2013_Nordbaden.JPG

The output started with

2ESk9uWHSmQ6ayKMjmX1FWRYG5BkERRkgK9qYbqyHKTijDQc

but it should start with

/9j/4AAQSkZJRgABAQEASABIAAD//g

The output from the code also didn't generate any image on https://codebeautify.org/base64-to-image-converter#

Why is the generated output giving me a different encoding than the standard Base64 encoding?

Tim Williams · Accepted Answer · 2017-08-21 17:31:39Z

3

The Immediate window doesn't have an unlimited capacity: if you print too much you will begin to lose the start...

 Debug.Print Left(objDocElem.Text, 200)

gives me the expected start /9j/4AAQSkZJRgABAQEASABIAAD//gBgRmlsZ

answered Aug 21, 2017 at 17:31

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

WCGPR0 Over a year ago

Thank you! I would've never guessed that the immediate window had limited capacity

Usually Confused · Accepted Answer · 2023-04-30 02:51:39Z

0

The XML object inserts a line feed after ever 72 characters returned. See: Base64 encode with Stream_StringToBinary inserts a newline, breaking the string?

Just remove that character to make this work...

' Get base64 value
Dim bad As String, good As String
bad = objDocElem.text
good = Replace(bad, Chr(10), "")
Debug.Print good

answered Apr 30, 2023 at 2:51

Usually Confused

1461 silver badge5 bronze badges

Collectives™ on Stack Overflow

Incorrect base64 encoding VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related