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, 2022 in Others by Kithuzzz
• 38,010 points
750 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, 2022 by narikkadan
• 63,700 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, 2022 in Others by narikkadan
• 63,700 points
1,256 views
0 votes
1 answer
0 votes
1 answer

How to Convert Excel Cell Values Into Individual PNG Files?

This will: create a chart add an image to ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,700 points
302 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, 2022 in Others by narikkadan
• 63,700 points
2,094 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, 2022 in Others by narikkadan
• 63,700 points
637 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, 2022 in Others by narikkadan
• 63,700 points
1,438 views
0 votes
1 answer

VBA Excel Adding pictures to a worksheet

It should work as: ActiveSheet.Pictures.Insert("C:\Work\test_Project\myjpgfile.jpg").Select As that's all the ...READ MORE

answered Jan 3, 2023 in Others by narikkadan
• 63,700 points
289 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, 2022 in Others by narikkadan
• 63,700 points
2,241 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, 2022 in Others by narikkadan
• 63,700 points
1,918 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