Convert image jpg png jpeg to base64

0 votes

I am working with the VBA language to import images from a folder and convert these images to BASE64 format.

I imported the images folder in an excel "E1" row. This worked. Here is my result:

Option Explicit

Sub Insert()

Dim strFolder As String
Dim strFileName As String
Dim objPic As Picture
Dim rngCell As Range

strFolder = "C:\Users\Lenovo\Pictures\RonHivkd" 'change the path accordingly
If Right(strFolder, 1) <> "\" Then
    strFolder = strFolder & "\"
End If

Set rngCell = Range("E1") 'starting cell

strFileName = Dir(strFolder & "*.jpg", vbNormal) 'filter for .png files

Do While Len(strFileName) > 0
    Set objPic = ActiveSheet.Pictures.Insert(strFolder & strFileName)
    With objPic
        .Left = rngCell.Left
        .Top = rngCell.Top
        .Height = rngCell.RowHeight
        .Placement = xlMoveAndSize
    End With
    Set rngCell = rngCell.Offset(1, 0)
    strFileName = Dir
Loop

End Sub

Now I would like to convert my images to BASE64. I came across this answer:

Convert image (jpg) to base64 in Excel VBA.

But when I apply the function to a cell that contains the image, I get the result: "#VALUE!" , I do not understand why!

Public Function EncodeFile(strPicPath As String) As String
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
EncodeFile = objDocElem.Text

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

End Function

enter image description here

Sep 23 in Others by Kithuzzz
• 20,660 points
102 views

1 answer to this question.

0 votes

Try this - it will perform the Base64 conversion at the same time it's importing the images.

Sub InsertAndEncode()

    Dim strFolder As String, strFileName As String, B64 As String
    Dim objPic As Picture, rngCell As Range, ws As Worksheet

    strFolder = "C:\Users\Lenovo\Pictures\RonHivkd"
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"

    Set ws = ActiveSheet
    Set rngCell = ws.Range("E1") 'starting cell

    strFileName = Dir(strFolder & "*.jpg", vbNormal) 'filter for .png files

    Do While Len(strFileName) > 0

        rngCell.Value = strFileName

        Set objPic = ws.Pictures.Insert(strFolder & strFileName)
        With objPic
            .Left = rngCell.Offset(0, 1).Left
            .Top = rngCell.Offset(0, 1).Top
            .Height = rngCell.RowHeight
            .Placement = xlMove 'not size or adding the B64 will cause problems...
        End With

        'use your existing function to get the Base64 version
        B64 = EncodeFile(strFolder & strFileName)
        If Len(B64) < 32000 Then
            rngCell.Offset(0, 2).Value = B64
        Else
            rngCell.Offset(0, 2).Value = "Too large" 'won't fit in a cell
        End If

        Set rngCell = rngCell.Offset(1, 0)
        strFileName = Dir
    Loop

End Sub
Public Function EncodeFile(strPicPath As String) As String
    Const adTypeBinary = 1          ' Binary file is encoded
    Dim objXML, objDocElem, objStream

    ' Open data stream from picture
    Set objStream = CreateObject("ADODB.Stream")
    objStream.Type = adTypeBinary
    objStream.Open
    objStream.LoadFromFile (strPicPath)
    Set objXML = CreateObject("MSXml2.DOMDocument")
    Set objDocElem = objXML.createElement("Base64Data")
    objDocElem.DataType = "bin.base64"
    objDocElem.nodeTypedValue = objStream.Read()
    EncodeFile = objDocElem.Text

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

End Function
answered Sep 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

Convert a single png file to jpg in vba

Try this code: Sub ConveretPNGToJpg() ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
95 views
0 votes
1 answer
0 votes
4 answers

How to Convert EML to PDF?

Hi, I think you need to check out ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
2,027 views
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
166 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
44 views
0 votes
1 answer

How to return a result from a VBA function

You must associate the value with the ...READ MORE

answered Nov 4 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
454 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered Sep 27 in Others by narikkadan
• 37,660 points
271 views
0 votes
1 answer

Convert table in a jpg image to excel using python

I believe you must execute OCR (optical ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
270 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP