vba - 在 VBA 中将图片上传到 file.io (HTTP Post)

Question

我正在尝试使用 https://file.io 上传文件在 Excel 中使用 VBA，使用其 Api( https://www.file.io/#one ，见下文)。

我找到了这个帖子how to upload file to file.io and get link但是，我不知道如何准确地将其从C#转移到VBA。

File.io 上的语法是:

$ curl -F "file=@test.txt" https://file.io
{"success":true,"key":"2ojE41","link":"https://file.io/2ojE41","expiry":"14 days"}
$ curl https://file.io/2ojE41 
This is a test
$ curl https://file.io/2ojE41
{"success":false,"error":404,"message":"Not Found"}

我当前的代码如下所示:

Set objhttp = CreateObject("MSXML2.ServerXMLHTTP")
URL = "https://file.io"
objhttp.Open "post", URL, False
objhttp.setRequestHeader "Content-type", "application/json" 
objhttp.Send ("file=@C:/Users/me/Downloads/image.jpg")
Debug.Print objhttp.responsetext

我的回复文本说:

{"success":false,"error":400,"message":"Trouble uploading file"}

我什至不确定路径中的“@”，或者通常是否有要使用的标准文件夹等。提前非常感谢!感谢所有帮助。

Answer 1

使用 XmlHttp VBA 发布多部分/表单数据的步骤

1. 使用 Chrome/Firefox/Fiddler 来观看 HTTP 请求。
2. 首先手动上传文件并查看浏览器执行的所有请求和响应
   (尤其是xhr，状态代码为200的文档请求)
3. 在post请求中传递cookie、参数

在本例中我使用了 Chrome 浏览器，下图显示了浏览器在请求中发送的参数。

<小时/>

 Sub UploadFilesUsingVBA()
     'this proc will upload below files to https://file.io/
          '  png, jpg, txt

        Dim fileFullPath As String
        fileFullPath = "C:\Users\santosh\Desktop\abcd.txt"

        POST_multipart_form_data fileFullPath
    End Sub

<小时/>

文件上传成功时的确认消息

<小时/>

Private Function GetGUID() As String
    ' Generate uuid version 4 using VBA
    GetGUID = WorksheetFunction.Concat(WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 4294967295#), 8), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 65535), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(16384, 20479), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(32768, 49151), 4), "-", WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 65535), 4), WorksheetFunction.Dec2Hex(WorksheetFunction.RandBetween(0, 4294967295#), 8))

End Function

Private Function GetFileSize(fileFullPath As String) As Long

    Dim lngFSize As Long, lngDSize As Long
    Dim oFO As Object, OFS As Object

    lngFSize = 0
    Set OFS = CreateObject("Scripting.FileSystemObject")

    If OFS.FileExists(fileFullPath) Then
        Set oFO = OFS.getFile(fileFullPath)
        GetFileSize = oFO.Size
    Else
        GetFileSize = 0
    End If

    Set oFO = Nothing
    Set OFS = Nothing
End Function



Private Function ReadBinary(strFilePath As String)
    Dim ado As Object, bytFile
    Set ado = CreateObject("ADODB.Stream")
    ado.Type = 1
    ado.Open
    ado.LoadFromFile strFilePath
    bytFile = ado.Read
    ado.Close

    ReadBinary = bytFile

    Set ado = Nothing
End Function


Private Function toArray(str)
    Dim ado As Object
     Set ado = CreateObject("ADODB.Stream")
     ado.Type = 2
     ado.Charset = "_autodetect"
     ado.Open
     ado.WriteText (str)
     ado.Position = 0
     ado.Type = 1
     toArray = ado.Read()
     Set ado = Nothing
End Function


Sub POST_multipart_form_data(filePath As String)

    Dim oFields As Object, ado As Object
    Dim sBoundary As String, sPayLoad As String, GUID As String
    Dim fileType As String, fileExtn As String, fileName As String
    Dim sName As Variant

    fileName = Right(filePath, Len(filePath) - InStrRev(filePath, "\"))
    fileExtn = Right(filePath, Len(fileName) - InStrRev(fileName, "."))

    Select Case fileExtn
     Case "png"
        fileType = "image/png"
     Case "jpg"
        fileType = "image/jpeg"
     Case "txt"
        fileType = "text/plain"
    End Select

    Set oFields = CreateObject("Scripting.Dictionary")
    With oFields
        .Add "qquuid", GetGUID
        .Add "qqtotalfilesize", GetFileSize(filePath)
    End With

    sBoundary = String(27, "-") & "7e234f1f1d0654"
    sPayLoad = ""
    For Each sName In oFields
        sPayLoad = sPayLoad & "--" & sBoundary & vbCrLf
        sPayLoad = sPayLoad & "Content-Disposition: form-data; name=""" & sName & """" & vbCrLf & vbCrLf
        sPayLoad = sPayLoad & oFields(sName) & vbCrLf
    Next

    sPayLoad = sPayLoad & "--" & sBoundary & vbCrLf
    sPayLoad = sPayLoad & "Content-Disposition: form-data; name=""file""; " & "filename=""" & fileName & """" & vbCrLf
    sPayLoad = sPayLoad & "Content-Type: " & fileType & vbCrLf & vbCrLf & vbCrLf & vbCrLf & vbCrLf



     sPayLoad = sPayLoad & "--" & sBoundary & "--"


      Set ado = CreateObject("ADODB.Stream")
      ado.Type = 1
      ado.Open
      ado.Write toArray(sPayLoad)
      ado.Write ReadBinary(filePath)
      ado.Position = 0

    With CreateObject("MSXML2.ServerXMLHTTP")
        .Open "POST", "https://file.io", False
        .SetRequestHeader "Content-Type", "multipart/form-data; boundary=" & sBoundary
        .Send (ado.Read())
        MsgBox .responseText
    End With

End Sub

<小时/>

有助于回答此问题的链接
1.https://stackoverflow.com/a/43266809/2227085
2.https://wqweto.wordpress.com/