API calls from MS Access (via VBA)

Tips, Tricks & How to's
1

Hi all,

Just a little bit of knowledge sharing for those of us who are pretty new to API’s, excel, access, etc. I am not formally trained as a dev nor have I ever taken any courses on how to build stuff in excel or access, so I just wanted to share for those like me, just want something to copy and hack apart to learn from and manipulate to suit their needs.

I have been trying to import data from Clockify via the API into MS Access. I will then be taking data from our accounting software and then aggregating all that data in a separate excel sheet, but to do all that the first step was making the API and there is very little info out there for those who are not well versed in this stuff like me to reference. Hopefully this helps:

Firstly build a form in access. Make a button (mine was called POSTTimesheets). Right click and create a build event > code builder. This will get the VBA window open and sets you up to start doing API stuff. You can then copy and paste this in there, change the names are needed, I have tried to leave informative comments to help make life easier. Also dont forget to change the workspace id and api key.

Private Sub POSTTimesheets_Click()
Dim Request As Object
Dim authKey As String
Dim stUrl As String
Dim jsonBody As String
Dim response As String

' API Endpoint URL for getting all projects
stUrl = "https://reports.api.clockify.me/v1/workspaces/{Your workspace id lives here}/reports/detailed"

' Authentication Key
authKey = "{Super secret API Key}"

' Create the JSON body - note I am exporting as a csv, you can change this to json and then parse that data into a table as well. I use the csv because the stored query it triggers on the Clockify DB gives me the most relevant data for what I need.
jsonBody = "{""dateRangeStart"":""2024-01-01T00:00:00.000Z""," & _
           """dateRangeEnd"":""2024-12-31T23:59:59.999Z""," & _
           """detailedFilter"":{""page"":1,""pageSize"":50}," & _
           """exportType"":""csv""}"

' Create XMLHTTP object
Set Request = CreateObject("MSXML2.XMLHTTP.6.0")

With Request
    .Open "POST", stUrl, False  ' Use POST method to retrieve all projects
    .SetRequestHeader "Content-Type", "application/json"
    .SetRequestHeader "Accept", "application/json"  ' Expect JSON response
    .SetRequestHeader "X-Api-Key", authKey  ' Set the API key header
    ' Send the request (no body for GET request)
    .Send jsonBody
End With

' Get the response, this then puts the response into the textbox I made on the form called "Timesheets". Using the textbox is a great way to test that it works. From there you just need to parse it and get it into a table.
response = Request.ResponseText
Timesheets = response
MsgBox "Done"

' Clean up
Set Request = Nothing

End Sub

Hopefully this might help someone in the future! Please remember, share your knowledge, keeping things secret doesn’t help anyone…especially your app support teams :joy:

2

Hey Ian,

Thank you for contributing to Clockify’s Forum and sharing knowledge about useful API queries!

We are glad you’re utilizing the possibilities of our API calls and that the Clockify app helps with MS Access use cases. We also greatly appreciate your feedback regarding the documentation.
Moving forward, I took this possibility to forward your opinion to our product team, so they can consider expanding this and making it more concise for the future.

Feel free to contact our support should you require any assistance, and we’ll gladly help!

All the best.

1 Like