Help on VBA program to add attachment to a confluence server page

[Rosario De Domenico]

Hi All,

I have used the code given in this answer for JIRA as a starting point but I do not manage to get through...

What I want to do is to add (and / or update) an attachment sitting on my client laptop to a confluence page in our server instance using VBA code in an Excel file.

Below the code up to the last point where I got and the response text that I get back.

Sub rdd_sendAttachementToConfluencePagebyID()
    Dim httpRestAPIreq As New WinHttpRequest
    Dim strRequestBodyStart, strRequestBodyEnd, strPassword, strUsername, strBoundary, strBaseURL, strFullURL, strPageID, strFilePath, strFileName, strFileData As String
    
    strFilePath = "C:\folder1\fodler2\file.png"
    strPageID = "7340078"
    strBaseURL = "http://theserver:8090"
    strFullURL = strBaseURL & "/rest/api/content/" & strPageID & "/child/attachment"
    strBoundary = "---------------------------7e0139a150b4e"
    strUsername = "id"
    strPassword = "pwd"
 
    strRequestBodyStart = "--" & strBoundary & vbCrLf & _
                          "Content-Disposition: form-data; name=""file_0"";value = """ & strFilePath & """;" & vbCrLf & _
                          "Content-Type: application/octet-stream" & vbCrLf & vbCrLf
 
    strRequestBodyEnd = vbCrLf & vbCrLf & "--" & strBoundary & "--"
 
    With httpRestAPIreq
        .Open "POST", strFullURL, False
        .setRequestHeader "X-Atlassian-Token", "no-check"
        .setRequestHeader "Content-Type", "multipart/form-data; boundary=" & strBoundary
        .setRequestHeader "Authorization", "Basic " & rdd_encodeBase64(strUsername & ":" & strPassword)
        .send StrConv(strRequestBodyStart, vbFromUnicode) & rdd_getFileAsBinary(strFilePath) & StrConv(strRequestBodyEnd, vbFromUnicode) 'Converts from string to bytes
        Debug.Print .responseText
    End With
End Sub
Function rdd_encodeBase64(strStrinToEncode As String) As String
    Dim arrData() As Byte
    Dim objXML As New MSXML2.DOMDocument
    Dim objNode As MSXML2.IXMLDOMElement
    arrData = StrConv(strStrinToEncode, vbFromUnicode)
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    rdd_encodeBase64 = objNode.Text
    Set objNode = Nothing
    Set objXML = Nothing
End Function
Function rdd_getFileAsBinary(ByVal strPath As String) As Variant
    Dim buffer() As Byte
    Dim lngLogFileHdl As Long
    lngLogFileHdl = FreeFile
    Open strPath For Binary As #lngLogFileHdl
    ReDim buffer(1 To LOF(lngLogFileHdl))
    Get #lngLogFileHdl, , buffer
    Close #lngLogFileHdl
    rdd_getFileAsBinary = buffer
End Function

I get back:

{"statusCode":400,"data":{"authorized":false,"valid":true,"errors":[],"notSuccessful":true,"successful":false},"message":"At least one attachment file must be included."}

Can someone help me pointing out what I am doing wrong?

[Rosario De Domenico]

At the end I resolved to use a curl executable and invoke that one from my VBA macro.

This worked perfectly.

[Javier Medina]

Hi Rosario,

Is there any way you can share your curl solution? Thank you

[Rosario De Domenico]

Following the request in the comment I expand how I did it here, but I followed a different path therefore I did not really answer the original request.

I downloaded a compiled curl.exe for Windows (curl_7_50_3_openssl_nghttp2_x64.7z) unfortunately I cannot find the link any more, but it was github, sourceforge or something similar.

I downloaded the VBA-JSON-2.2.1 library from github.

I implemented the following logic in VBA:

• build up the REST API URL in a string based on which action you want to perform (fetch a page ID, fetch page content, add or update an attachment, etc.); refer to Confluence documentation: https://docs.atlassian.com/atlassian-confluence/REST/latest-server/
• build up the CURL command in another string based on Confluence documentation and desired output / format; refer to CURL documentation (i.e. man page, do not remember the link but easy to find)
• launch the curl.exe with proper command option and URL
• use / parse the output as required.

In VBA I separated the constants (like URL path) and split the actions in multiple functions for reuse; here a couple of such functions as examples. Please, note that a couple of extra libraries need to be referenced (not sure anymore which ones, probably Microsoft Script Control 1.0, Windows Script Host Object Model).

Private Function rdd_getConfluencePageIDbyTitle(strUsID As String, strPWD As String, strServerURL As String, strSpaceKey As String, strPageTitle As String) As String
'PRE: following global constant must exist and be defined: strCURLexecutable As String --> the full path and name of the curl executable
    Dim strFullURL As String
    Dim strCURLoptions As String
    Dim strFullJSONresponse As String
    Dim strHTTPstatus As String
    Dim strSize As String
    Dim shlCmdPrpt As New WshShell
    Dim shlOutput As WshExec
    strFullURL = strServerURL & "/rest/api/content?spaceKey=" & strSpaceKey & "&title=" & rdd_URLencode(strPageTitle)
    strCURLoptions = "-w ""\n%{http_code}"" -u " & strUsID & ":" & strPWD
    Set shlOutput = shlCmdPrpt.Exec(strCURLexecutable & " " & strCURLoptions & " """ & strFullURL & """")
    strFullJSONresponse = shlOutput.StdOut.ReadLine
    strHTTPstatus = shlOutput.StdOut.ReadAll
    If strHTTPstatus <> "200" Then
        MsgBox "Could not get page ID by title, HTTP status: " & strHTTPstatus & "."
        rdd_getConfluencePageIDbyTitle = "-1"
        Exit Function
    End If
    strSize = JsonConverter.ParseJson(strFullJSONresponse).Item("size")
    Select Case strSize
        Case "0"
            MsgBox "Could not get page ID; no page found with title: " & strPageTitle & "."
            rdd_getConfluencePageIDbyTitle = ""
        Case "1"
            rdd_getConfluencePageIDbyTitle = JsonConverter.ParseJson(strFullJSONresponse).Item("results")(1).Item("id")
        Case Else
            MsgBox "Could not get page ID; found: """ & strSize & """ pages with title: " & strPageTitle & "."
            rdd_getConfluencePageIDbyTitle = "-1"
    End Select
End Function
 
Private Sub rdd_addAttachementToConfluenceByID(strUsID As String, strPWD As String, strServerURL As String, strPgID As String, strFileURI As String)
'PRE: following global constant must exist and be defined: strCURLexecutable As String --> the full path and name of the curl executable
    Dim strFullURL As String
    Dim strCURLoptions As String
    Dim strHTTPstatus As String
    Dim shlCmdPrpt As New WshShell
    strFullURL = strServerURL & "/rest/api/content/" & strPgID & "/child/attachment"
    strCURLoptions = "-o nul -w ""%{http_code}"" -u " & strUsID & ":" & strPWD & " -X POST -H ""X-Atlassian-Token: nocheck"" -F ""file=@" & strFileURI & """ -F ""comment=Added by VBA macro through REST API."""
    strHTTPstatus = shlCmdPrpt.Exec(strCURLexecutable & " " & strCURLoptions & " " & strFullURL).StdOut.ReadAll
    If strHTTPstatus <> "200" Then
        MsgBox "Could not attach file: " & strFileURI & " to confluence page, HTTP status: " & strHTTPstatus & "."
    End If
End Sub