How to upload a file by REST API in VBA

Added by jibin lee 9 months ago


I follow the REST API document to upload a excel file in VBA

When I run the post upload.xml and then login the redmine server to see that the file has existed, but it's name is oddness.

Then I use the token to post issues.xml, It's failed.

My code is below:

Private Sub uploadButton1_Click()
    respText = pvPostFile("", "d:\128393.csv", "application/octet-stream;", False)
    ThisWorkbook.Sheets(1).Cells(10, 1) = respText
    MsgBox respText
End Sub

Private Function pvPostFile(sUrl As String, sFileName As String, sContextType As String, Optional ByVal bAsync As Boolean) As String
    Const STR_BOUNDARY  As String = "3fbd04f5-b1ed-4060-99b9-fca7ff59c113" 
    Dim nFile           As Integer
    Dim baBuffer()      As Byte
    Dim sPostData       As String

    '--- read file
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        sPostData = StrConv(baBuffer, vbUnicode)
    End If
    Close nFile

    '--- post
    With CreateObject("Microsoft.XMLHTTP")
        .Open "POST", sUrl, bAsync
        .setRequestHeader "Content-Type", sContextType 
        .send pvToByteArray(sPostData)
        If Not bAsync Then
            pvPostFile = .Status & .responseText
        End If
    End With
End Function

Private Function pvToByteArray(sText As String) As Byte()
    pvToByteArray = StrConv(sText, vbFromUnicode)
End Function

Private Sub issueButton_Click()

    respText = pvPostFile("", "d:\issues.xml", "application/xml;", False)

    ThisWorkbook.Sheets(1).Cells(11, 1) = respText

    MsgBox respText
End Sub

d:\issues.xml content like below:

<?xml version="1.0" encoding="UTF-8"?>
  <subject>Creating an issue with a uploaded file</subject>
  <uploads type="array">