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

This widget could not be displayed.

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

This worked perfectly.

This widget could not be displayed.

Hi Rosario,

 

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

This widget could not be displayed.

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 Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published Tuesday in Confluence

Add-on evaluation with confluence templates

Atlassian market place contains number of Apps/Addons which improves the capability of out of the box Atlassian products. It is good to follow a plugin evaluation process before install add-ons. So t...

101 views 12 6
Read article

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