Convert image (jpg, png, jpeg) to base64

Question

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

The function works fine for me. How are you using it? Are you calling it as a UDF in a cell? If so, remember that depending on the size of the image, the resulting string can be very large. — PeterT, Feb 23 '20 at 22:45
That function expects a file path to your image file, and will return the base-64 encoded representation of the file. Likely it will not fit in a worksheet cell, so you will need to do something else with it. What do you need the B64 version for? - are you posting them to some API or ? — Tim Williams, Feb 23 '20 at 23:36
@PeterT : This is what I do, I click on a cell, and insert the function "= PERSONAL.XLSB! EncodeFile ()", and then, I select the cell that contains the image. The images are very small. I tried with larger images but it's the same ... I really don't understand. Can I send you please the file in your mail adress ? — Dalila Hannouche, Feb 24 '20 at 17:10
@Tim Williams Thank you for your response, I finally understand... I did not used the FILE PATH. No, I used the image directly from a cell. Because I have imported the images from a folder. How can I import my images in my range excel as a PATH please ? — Dalila Hannouche, Feb 24 '20 at 17:23
You can't easily do that - you'd need to save the images to disk and then use the code you have to convert to base64 — Tim Williams, Feb 24 '20 at 17:24
I need to import the images as a PATH(url) in my cells. What I did is to import images from a folder and that's all... I will search again...Thank you. — Dalila Hannouche, Feb 24 '20 at 17:36
What are you going to *do* with the Base64 strings? You just want to store them in a worksheet cell? You can do that, as long as your images aren't too large. — Tim Williams, Feb 24 '20 at 23:09
"I select the cell that contains the image" - cells never "contain" an image: it's just positioned over the top of the cell. You cannot use a UDF to "read" the image this way. You already have code to translate your images to Base64 - just point that at your folder full of images. — Tim Williams, Feb 24 '20 at 23:33
@TimWillias I will explain you because i tried again... I finally imported all my images as file path.For exemple, in my cell "A1" i have the link : "C:\Users\dalila\images\naom_59dde02a18e7.jpg", but when i execute the function like "=EncodeFile(A1)" , i still have "#Value!" — Dalila Hannouche, Feb 25 '20 at 09:18
@TimWilliams I updated my post and added an image to show you my error... Please, help :c — Dalila Hannouche, Feb 25 '20 at 10:20
How large are your images? A cell can only hold about 32k characters, which means you're limited to images smaller than whatever that is in file size(Base-64 encoding adds about 40% to the file size) Your function works fine for smaller images <25k or so. — Tim Williams, Feb 25 '20 at 17:32
Hi. I don't think it's because of the size of the images. For example, when I upload an image from the internet, the (Encode File) function works very well. But when I take a picture from the PC, the function doesn't work anymore. Do I need to change something in the (Encode File) function ? Thanks again @Tim Williams — Dalila Hannouche, Feb 28 '20 at 15:46
Can you please answer my questions about the size of your images and what you need the BASE64 versions for? I'm trying to help you get to your ultimate goal, but you're not giving me what I need to do that. — Tim Williams, Feb 28 '20 at 15:49
Sorry... I think I'm a little panicked about that. Thank you for your patience...I will answer, my images are small, one of them have this dimension (303x302) with weight : 5,45 Ko. I need to convert this images to BASE64 to import them in a website like file xls or csv. — Dalila Hannouche, Feb 28 '20 at 16:33

score 0 · Answer 1 · answered Feb 28 '20 at 17:29

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

Thank you thank youuu Tim !!! But the result of some images are too large :'( How can we convert them ! :'( Also, i did researches in google and found that code to import images with path : https://www.extendoffice.com/documents/excel/4701-excel-get-picture-name.html Thank you again for you help... — Dalila Hannouche, Feb 28 '20 at 18:03
For the larger images, it depends what you are going to do with the Base64 strings once they're in excel. You could split the "Too large" B64 up into 32k "chunks" and put each chunk in a separate cell, but then you'd need to re-combine them at some point. — Tim Williams, Feb 28 '20 at 18:07
I will import this images into a website. This images are for products. There is another solution please?... — Dalila Hannouche, Feb 28 '20 at 20:37
Iam working with Odoo, it's an EPR. I just click into the button "import" the xml file or csv. — Dalila Hannouche, Feb 28 '20 at 23:34

Convert image (jpg, png, jpeg) to base64

1 Answers1