For modern VBA development, especially in environments like Office for Mac 2016 and later, where traditional Windows API calls (such as Declare Function) are not supported, you would typically avoid using external libraries for tasks like HTTP requests. Instead, you can use built-in VBA capabilities or leverage external tools through shell commands in a way that is compatible with both Windows and Mac environments.
Given the limitations on Mac, one common approach is to use Shell to execute command-line tools like curl for making HTTP requests. This method works on both Windows and Mac, but on Windows, you typically have access to more sophisticated methods like using WinHttp.WinHttpRequest or MSXML2.XMLHTTP for HTTP requests.
Here’s an updated version of the code that should be more compatible with modern VBA environments, including Mac:
openTime = Timer ' Simulate workbook open time
closeTime = Timer + 60 ' Simulate workbook close time after 1 minute
duration = closeTime - openTime ' Calculate duration in seconds
username = Environ$("USERNAME") ' Get the username of the current user
' Prepare JSON data for Notion API
jsonData = "{""parent"": {""database_id"": ""Your_Database_ID""}, " & _
"""Username"": {""title"": [{""text"": {""content"": """ & username & """}}]}, " & _
"""Duration"": {""number"": " & duration & "}}}"
notionUrl = "https://api.notion.com/v1/pages"
authToken = "Bearer Your_Secret_API_Token"
Call SendDataToNotion(notionUrl, authToken, jsonData)
Sub SendDataToNotion(notionUrl As String, authToken As String, jsonData As String)
Dim tempFilePath As String
' Create a temporary file to store JSON data
tempFilePath = Environ$("TEMP") & "\tempData.json"
Open tempFilePath For Output As #fileNo
' Construct the curl command
script = "curl -X POST """ & notionUrl & """ -H ""Authorization: " & authToken & _
""" -H ""Notion-Version: 2021-05-13"" -H ""Content-Type: application/json"" --data @" & _
Replace(tempFilePath, "\", "/")
' Execute the curl command using Shell
' Clean up: Delete the temporary file
This code defines a TestNotionAPI subroutine that prepares the data and calls SendDataToNotion to send the data to the Notion API using a curl command executed via Shell. The JSON data is temporarily stored in a file because curl might have issues with complex inline JSON data on some systems.
Important Notes:
- Replace
"Your_Database_ID" and "Your_Secret_API_Token" with your actual Notion database ID and API token.
- This approach uses a temporary file to store JSON data, which is then passed to
curl using the --data @filename syntax. Ensure that the path to the temporary file is accessible and writable.
- Error handling and response processing from
curl are not covered here and should be implemented as needed.
- The
Shell function’s behavior might vary between Windows and Mac, and the path to curl might need to be adjusted based on the system’s configuration.
- Directly executing shell commands from VBA involves security risks, especially when incorporating user-generated content or external data. Always validate and sanitize inputs to avoid injection attacks.
This approach should offer a more modern and cross-platform way to interact with external APIs from VBA, albeit with some limitations and considerations, especially regarding security and error handling.