• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Upload image using curl in VBA

YasserKhalil

YasserKhalil

Well-Known Member
0
I have registered in a website that supports API and I have tested the CURL command line and it works fine for me

Code: 
curl --location --request POST "https://api.imgbb.com/1/upload?key=APIKEY" --form "image=iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg=="

this is API information page
https://api.imgbb.com/

I have no great knowledge of how to use excel VBA to send an equivalent command line? I am totally new to such stuff and I have searched a lot and can't get how things go on
I have found this link that may be helpful but it is in VB.NET Help with Imgur API and VB.NET - Image POST
 
Hi !​
​
If you already have the direct URL of the file to import - whatever the file type - the easy way is to directly download it​
- or to read it directly under Excel like any local file -​
rather than use this API where you need to decode first the JSON answer.​
Use this API under VBA via Msxml2.XMLHTTP or WinHttpRequest object, see the samples on this forum …​
​
 
If you are looking for the syntax to invoke Command Prompt through VBA then your syntax would look like below.

(Untested)

Code: 
Dim strCmd As String
strCmd = Environ$("comspec") & " /c curl --location --request POST ""https://api.imgbb.com/1/upload?key=APIKEY"" --form ""image=iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg=="""
Shell strCmd, vbNormalFocus
 
YasserKhalil said:
Can you give me some links that would help me?
Click to expand...
From many you can get yourself just using the top right Search button :​

Convert my code from xmlhttp method into winhttp method

Dear all, hope you are doing fine. I have made a scraper using vba xmhttp method which is parsing yell.com very smoothly at this moment. But i noticed that the site sometimes uses redirection method, as a result i end up with nothing when i run the code. So if there somebody out here help me...
chandoo.org
 
Here's sample
And here's the code I am using to convert the image to BASE64
Code: 
Sub Test()
    Dim picPath As String
    
    picPath = ThisWorkbook.Path & "\Sample.png"
    ExportToTextFile ConvertFileToBase64(picPath), ThisWorkbook.Path & "\Output.txt"
End Sub

Sub ExportToTextFile(sText As String, sPath As String)
    Dim fStream As Object
    
    Set fStream = CreateObject("ADODB.Stream")
    With fStream
        .Charset = "UTF-8"
        .Open
        .WriteText sText
        .SaveToFile sPath, 2
        .Close
    End With
End Sub

Public Function ConvertFileToBase64(strFilePath As String) As String
    Const UseBinaryStreamType = 1

    Dim streamInput: Set streamInput = CreateObject("ADODB.Stream")
    Dim xmlDoc: Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    Dim xmlElem: Set xmlElem = xmlDoc.CreateElement("tmp")

    streamInput.Open
    streamInput.Type = UseBinaryStreamType
    streamInput.LoadFromFile strFilePath
    xmlElem.DataType = "bin.base64"
    xmlElem.NodeTypedValue = streamInput.Read
    ConvertFileToBase64 = Replace(xmlElem.Text, vbLf, "")

    Set streamInput = Nothing
    Set xmlDoc = Nothing
    Set xmlElem = Nothing
End Function
 

Attachments

  • Sample.png
    Sample.png
    507.7 KB · Views: 0
YasserKhalil said:
Thanks a lot for replies.
@
shrivallabha
Thanks a lot. This works for this small image .. but try it with larger images and you will encounter and error.
Click to expand...
@YasserKhalil
I just posted the VBA code for invoking command prompt and nothing more (untested is mentioned as well for CURL part much the same way as Siddharth Rout indicated).

I see that you have cross-posted this on stackoverflow and accepted the answer there.
https://stackoverflow.com/questions/60786992/upload-image-using-curl-in-vba

If you are cross-posting then it is good to indicate so.
 
In fact, I was searching for a way using XMLHTTP in the excel VBA not the curl way .. But it seems that this is the only way for me till now.
Hope to find a solution using POST method ...
I didn't want to post the other link so as to get a different approach ..
 
  • Question for both of you guys : which Windows version do you use ?
    As the command line like the post #4 does not work on my side on computers with different Excel & Windows versions …

  • Yasser, see what exactly contains the ResponseText from a POST web request under WinHttpRequest ?
    As if it contains the JSon expected answer then it's easy to achieve the next steps …

    What is the link with Excel (so VBA) ? What is the final purpose under Excel ? …
 
I am using Windows 10 64 Bit and Office 365 32 Bit
In fact, I am lost at using such stuff WinHttpRequest ..need the first steps and I will try on my own
 
Thanks a lot for your patience Mr. Marc L
I am still confused as the target is to upload image not download .. that's a point
And another point how to implement the API Key in the code
Code: 
Sub Demo()
    src$ = "Confused about the source url"
    'src$ = "http://fr.louisvuitton.com/images/is/image/lv/1/PP_VP_AS/louis-vuitton--M41746_PM2_Front view.jpg"
    LFN$ = ThisWorkbook.Path & "\MyJSON.json"
    If RequestDownload(src, LFN) Then
        'Cells(5).Select
        'ActiveSheet.Pictures.Insert LFN
    End If
End Sub

Function RequestDownload(URL$, FILE$) As Boolean
    Dim B() As Byte, F%
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "POST", URL, False
        .setRequestHeader "DNT", "1"
        On Error GoTo Fin
        .send
        If .Status = 200 Then
            B = .responseText
            F = FreeFile(1)
            Open FILE For Binary As #F
            Put #F, , B
            Close #F
            RequestDownload = True
        End If
Fin:
    End With
End Function
 
The link is just to see how to use a web request under VBA​
but yes my bad since post #2 where I wrote « the easy way is to directly download it »​
so without more information I doubt VBA is the more appropriate tool …​
​
Anyway another sample where you can see how to pass parameters to the request via the send statement :​

VBA Internet Explorer Automation - Insert value from cell to input and retrieve result

I have a list of mobile numbers in column A:A and I wanted to find out what phone operator they belong to. So, if you open the website link: http://www.qualoperadora.net/ and type inside the box "11976773331", the result will be the following: Número:(11)97677-3331 Operadora: Claro - Celular...
chandoo.org
 
I found this code but I don't know how to implement it properly to suit my needs
Code: 
Sub Upload(strUploadUrl, strFilePath, strFileField, strDataPairs)
'Uses POST to upload a file and miscellaneous form data
'strUploadUrl is the URL (http://127.0.0.1/cgi-bin/upload.exe)
'strFilePath is the file to upload (C:\My Documents\test.zip)
'strFileField is the web page equivalent form field name for the file (File1)
'strDataPairs are pipe-delimited form data pairs (foo=bar|snap=crackle)
Const MULTIPART_BOUNDARY = "---------------------------0123456789012"
Dim ado, rs
Dim lngCount
Dim bytFormData, bytFormStart, bytFormEnd, bytFile
Dim strFormStart, strFormEnd, strDataPair
Dim web
Const adLongVarBinary = 205
    'Read the file into a byte array
    Set ado = CreateObject("ADODB.Stream")
    ado.Type = 1
    ado.Open
    ado.LoadFromFile strFilePath
    bytFile = ado.Read
    ado.Close
    'Create the multipart form data.
    'Define the end of form
    strFormEnd = vbCrLf & "--" & MULTIPART_BOUNDARY & "--" & vbCrLf
    'First add any ordinary form data pairs
    strFormStart = ""
    For Each strDataPair In Split(strDataPairs, "|")
        strFormStart = strFormStart & "--" & MULTIPART_BOUNDARY & vbCrLf
        strFormStart = strFormStart & "Content-Disposition: form-data; "
        strFormStart = strFormStart & "name=""" & Split(strDataPair, "=")(0) & """"
        strFormStart = strFormStart & vbCrLf & vbCrLf
        strFormStart = strFormStart & Split(strDataPair, "=")(1)
        strFormStart = strFormStart & vbCrLf
    Next
    'Now add the header for the uploaded file
    strFormStart = strFormStart & "--" & MULTIPART_BOUNDARY & vbCrLf
    strFormStart = strFormStart & "Content-Disposition: form-data; "
    strFormStart = strFormStart & "name=""" & strFileField & """; "
    strFormStart = strFormStart & "filename=""" & Mid(strFilePath, InStrRev(strFilePath, "\") + 1) & """"
    strFormStart = strFormStart & vbCrLf
    strFormStart = strFormStart & "Content-Type: application/upload" 'bogus, but it works
    strFormStart = strFormStart & vbCrLf & vbCrLf
    'Create a recordset large enough to hold everything
    Set rs = CreateObject("ADODB.Recordset")
    rs.Fields.Append "FormData", adLongVarBinary, Len(strFormStart) + LenB(bytFile) + Len(strFormEnd)
    rs.Open
    rs.AddNew
    'Convert form data so far to zero-terminated byte array
    For lngCount = 1 To Len(strFormStart)
        bytFormStart = bytFormStart & ChrB(Asc(Mid(strFormStart, lngCount, 1)))
    Next
    rs("FormData").AppendChunk bytFormStart & ChrB(0)
    bytFormStart = rs("formData").GetChunk(Len(strFormStart))
    rs("FormData") = ""
    'Get the end boundary as a zero-terminated byte array
    For lngCount = 1 To Len(strFormEnd)
        bytFormEnd = bytFormEnd & ChrB(Asc(Mid(strFormEnd, lngCount, 1)))
    Next
    rs("FormData").AppendChunk bytFormEnd & ChrB(0)
    bytFormEnd = rs("formData").GetChunk(Len(strFormEnd))
    rs("FormData") = ""
    'Now merge it all
    rs("FormData").AppendChunk bytFormStart
    rs("FormData").AppendChunk bytFile
    rs("FormData").AppendChunk bytFormEnd
    bytFormData = rs("FormData")
    rs.Close
    'Upload it
    Set web = CreateObject("WinHttp.WinHttpRequest.5.1")
    web.Open "POST", strUploadUrl, False
    web.setRequestHeader "Content-Type", "multipart/form-data; boundary=" & MULTIPART_BOUNDARY
    web.send bytFormData
End Sub
 
I think this is a good start. I got a response but error in JSON response appears
Code: 
Sub Test()
Dim objHTTP     As Object
Dim URL         As String
Dim Data        As String
Dim replyTXT

Data = ThisWorkbook.Path & "\Logo.png"

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "https://api.imgbb.com/1/upload?key=ea40e4aec91ead65f6cdb3f148ebf2b8"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "Content-type", "application/json" 'if you have/need headers
objHTTP.send Data
replyTXT = objHTTP.responseText
MsgBox (replyTXT)


End Sub
Code:

Is there a specific way to send the Data in this line Data = ThisWorkbook.Path & "\Logo.png"?
What should I do to inspect the page after pressing the F12 key.. which url should I inspect to be able to get the right information?
 
Marc L said:
  • Question for both of you guys : which Windows version do you use ?
    As the command line like the post #4 does not work on my side on computers with different Excel & Windows versions …

  • Yasser, see what exactly contains the ResponseText from a POST web request under WinHttpRequest ?
    As if it contains the JSon expected answer then it's easy to achieve the next steps …

    What is the link with Excel (so VBA) ? What is the final purpose under Excel ? …
Click to expand...
@Marc L I have not tested CURL and the rest of the stuff. I posted only for VBA+CMD part so I am not sure if the rest works or not.
 
YasserKhalil said:
Is there a specific way to send the Data in this line Data = ThisWorkbook.Path & "\Logo.png"?
Click to expand...
According to your initial link it can't work like this​
as your Data variable must contain the parameters with their data accordingly …​
​
… like "key={the key}&image={binary base64 data or a URL for an image}&name={file name}"​
​
where you must replace each { } part …​
​
 
I am not sure. I thought it is possible using XMLHTTP POST method to put the required fields through it then to using .send ??*??
Thanks anyway for trying to help.
 
My problem is with how to implement each parameter. Will I inspect a specific page and what's the page in this case? or this doesn't require an inspection part. I am lost :)
 
It's just the send statement parameters where you must enter your own API key like post #19 just reading your initial link​
or all in the URL like in your post #17 so without the Data variable in the send codeline, avoid to mix both …​
​
 
You must log in or register to reply here.
Back
Top