I am using code from this post.
That code works well for small images (<35kb
). However, I have a problem when the image is larger than 35kb
. When that is the case, depending on the original image size, only half (sometimes more, sometimes less) the image gets rendered in the cell.
Is there a workaround for this? Pretty much all my images are somewhere between 100kb and 150kb. This is my code currently:
Option Explicit
Sub Test()
Dim strTempPath As String
Dim arrTest(1 To 1) As String
Dim intCounter As Integer
'base 64 image examples
arrTest(1) = GetSOLogoBase64
'use workbook path as temp path
strTempPath = Application.ActiveWorkbook.Path & "\temp.png"
For intCounter = 1 To 1
'save byte array to temp file
Open strTempPath For Binary As #1
Put #1, 1, DecodeBase64(arrTest(intCounter))
Close #1
'insert image from temp file
Sheets("Sheet1").Cells(intCounter * 4, 1).Select
Sheets("Sheet1").Pictures.Insert strTempPath
'kill temp file
Kill strTempPath
Next intCounter
End Sub
Private Function DecodeBase64(ByVal strData As String) As Byte()
Dim objXML As Object 'MSXML2.DOMDocument
Dim objNode As Object 'MSXML2.IXMLDOMElement
'get dom document
Set objXML = CreateObject("MSXML2.DOMDocument")
'create node with type of base 64 and decode
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.Text = strData
DecodeBase64 = objNode.nodeTypedValue
'clean up
Set objNode = Nothing
Set objXML = Nothing
End Function
Function GetSOLogoBase64() As String
'https://stackoverflow.com/questions/39126617/inserting-an-image-into-a-sheet-using-base64-in-vba
GetSOLogoBase64 = ActiveWorkbook.Worksheets("Sheet1").Range("G3").Value
End Function