Function does'nt work !!

[MadameHugo]

Hi,

After many researches, I used this function to convert my images path into format BASE64.

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

But when i call this function like : =EncodeFile(G2)

NOTE : G2 that contain the PATH OF IMAGE : C:\Users\dalila\Pictures\images\naom_59dddfc7dd0b0.jpg

I have the result "#VALUE!"

Can you help me please? ...

 

[Yongle]

Call the function from VBA and it will work for you

Sub CallFunc()
    MsgBox EncodeFile("C:\Users\dalila\Pictures\images\naom_59dddfc7dd0b0.jpg")
    MsgBox EncodeFile(Range("G2"))
End Sub

 

[MadameHugo]

Hi, it work's but i need to do that for all of my range in cells not in messages boxs ( because i have many images...)

Thank you again Yongle..

 

[Yongle]

Use VBA to loop
- something like this

Dim cel As Range
For each cel in Range("G2:G20")
   cel.Offset( ,1) =EncodeFile(cel)
Next cel

 

[MadameHugo]

Always the same...

The thing that i don't understand is that when a call the function, i have "#VALUE!". Please see my file and just tell me where is my error :

test.xltm

drive.google.com

 

[Yongle]

How are you calling the function ?

 

[MadameHugo]

Like this (Please see in image)

 

[Yongle]

You have not done anything wrong
You simply cannot use it as a UDF (User Defined Function)
There are lots of things that are not permitted in UDFs (eg open datastream)

User Defined Functions (UDFs) In Excel VBA. Excel Training VBA lesson 21

User Defined Functions (UDFs) In Excel VBA. Excel Training VBA lesson 21

www.ozgrid.com

You must call it via VBA as illustrated above

 

[MadameHugo]

Good morning Yongle,

After many tries, i found that if I download an image from the web, it will be converted in BASE64. But the images who are situated in my PC can't be converted. I don't understand that.

May be if I use your funtion :

Dim cel As Range
For each cel in Range("G2:G20")
cel.Offset( ,1) =EncodeFile(cel)
Next cel

This will be the solution. But this function have error, this is :

 

[Yongle]

Try this

Dim cel As Range
For each cel in Range("G2:G20")
   cel.Offset( ,1) =EncodeFile(cel.Value)
Next cel