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

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?

 

3 answers

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

This worked perfectly.

Hi Rosario,

 

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

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

 

 

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Friday in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

274 views 11 10
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you