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
• 12,240 points
16 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
• 20,880 points

Related Questions In Others

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
1,877 views
0 votes
1 answer

How to add one image in Flutter App?

Hi@akhtar, You can use Image.network() method to add ...READ MORE

answered Jul 24, 2020 in Others by MD
• 95,360 points
224 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
402 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,095 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 18,960 points
41 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
134 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

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

answered 6 days ago in Others by narikkadan
• 20,880 points
26 views
0 votes
1 answer

PHP Convert String to SEO Friendly Url For Bengali Language Type

$string = preg_replace("/[^a-z0-9]/u", "$separator", $string);  change the ...READ MORE

answered Feb 14 in Others by narikkadan
• 20,880 points
174 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