How to Control cloudplan API from Excel

First of all one needs to install a JSON converter which parses JSON response data into a format which can easily be accessed by Excel VBA.

A solid an reliable converter can be found on GitHub (https://github.com/VBA-tools/VBA-JSON). Please download the respective ZIP file. The file proides a libary which can be imported into any VBA project. It will convert a JSON string (e.g. JSON API response) into a VBA dictionary. The Github side mentioned above also provides examples how the information in the VBA dictionary can be accessed.

Import the libary into your Excel project:

• Extract ZIP
• Open new Excel Workbook
• Open VBA Editor
• Menu: <Datei / File > // <Datei importieren / Import File > // Choose >>JsonConverter.bas<< file and press ENTER

Add reference in Excel:

In Excel VBA editor navigate to Menu > Extras / Tools > Verweise / References and:

• Enable Microsoft Scripting Runtime

Example code for creating a new weblink:

Sub Create_Weblink()

'Create new PDF documet from Excel sheet "Test" and save document into cloudplan folder!

Sheets("Test").ExportAsFixedFormat Type:=xlTypePDF, Filename:="F:\cloudplan\Test.pdf"

Application.Wait (Now + TimeValue("0:00:03"))

'Create a session ID (e.g. Login to your cloudplan account via cloudplan VBA)

Dim objHTTP As Object
Dim Json As String
Dim result1 As String
Dim jp1 As Object

Json = "{""email"":""yourmail@provider.com"", ""pw"":""yourawesomepassword"", ""session_duration"": 9600}" 'JSON strong as seen on https://docs.cloudplan.net/api/user/login.html

Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
Url = "https://uni.cloudplan.biz/api/user/login/" 'API URL as seen on https://docs.cloudplan.net/api/user/login.html

objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.send (Json)

result1 = objHTTP.responseText 'Receive API response as JSON

Set jp1 = JsonConverter.ParseJson(result1) 'Convert JSON string into VBA dictionary

SID = jp1("session_id") 'Assing current session ID to SID variable

'Request file ID for file to which the new weblink shall refer

Dim result2 As String
Dim jp2 As Object

Json = "{""node_id"":""5F2596ECD04408D022EF563F8733F374"",""folder_id"":""AD76085F81581B4D734838743A2F31AD""}" 'JSON strong as seen on https://docs.cloudplan.net/api/weblink/create.html

Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
Url = "https://uni.cloudplan.biz/api/folder/ls" 'API URL as seen on https://docs.cloudplan.net/api/weblink/create.html

objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "session_id", SID 'Provide current session ID for new API request
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.send (Json)

result2 = objHTTP.responseText 'Receive API response as JSON

Set jp2 = JsonConverter.ParseJson(result2) 'Convert JSON string into VBA dictionary

NoF = jp2("folder_content").Count 'How many files are in the respective folder

    For i = 1 To NoF 'Loop through all files in folder and identify file ID for file to which the new weblink shall refer

        Filename = jp2("folder_content")(i)("name")
        FileID = jp2("folder_content")(i)("cp_id")

            If Filename = "Test.pdf" Then

            FileID_X = FileID

            End If

    Next i

'Create new Weblink

Dim result3 As String
Dim jp3 As Object

Json = "{""folder_id"":""AD76085F81581B4D734838743A2F31AD"",""file_id"":"" & FileID_X & "",""file_name"":""Test.pdf"",""pw"":true,""duration"": 4838400,""max_downloads"": 10}" 'JSON strong as seen on https://docs.cloudplan.net/api/weblink/create.html

Set objHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
Url = "https://uni.cloudplan.biz/api/weblink/create" 'API URL as seen on https://docs.cloudplan.net/api/weblink/create.html

objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "session_id", SID 'Provide current session ID for new API request
objHTTP.setRequestHeader "Content-type", "application/json"
objHTTP.send (Json)

result3 = objHTTP.responseText 'Receive API response as JSON

Set jp3 = JsonConverter.ParseJson(result3) 'Convert JSON string into VBA dictionary

WLID = jp3("link_id") 'Retrive weblink 'link_ID' from VBA dictionary
WLPW = jp3("password") 'Retrive weblink 'password' from VBA dictionary

Debug.Print "weblink: https://portal.cloudplan.net/wl/Test.pdf/" & WLID 'Create new weblink based on 'link_ID'
Debug.Print "Password: " & WLPW

End Sub

See this example script.