Assistant api with VBA possible?

API
1

Hi everyone, is it possible to use the Assistant API with VBA?

I’ve already made several attempts to communicate with “my” assistant via VBA, but unfortunately without success. :frowning:

I would really appreciate a few examples.

My approach was something like this:

’ Set the Assistant ID
assistantID = “asst_QWbXMjsgxxxxxxxxws”

’ Set the URL of the Assistant endpoint
strURL = “hxxps://api.openai.com/v1/assistants/” & assistantID & “/completions”

Thanks for your support.

2

@DrDocs - I’m not too technical with VBA. But I can surely help you get started with assistants. If your goal is to have a single completion, you might not require Assistant ID. But if your goal is to create a chat session with the Assistant. You need to create assistant → create thread → add messages to the thread → create run → submit outputs for completion. I’ve written a short how to guide using the Python SDK check it out, might help you get started. Hope this helps :slightly_smiling_face:

PS: Make sure you create Assistant once. Use that ID in your flow.

1 Like
3

Thank you for your response, I’ve already seen the Python script :wink: but unfortunately, I don’t know how to implement it in VBA. Basically, I’m still in the testing phase. However, my idea was to feed a GPT assistant with information so that it can respond accordingly when asked (e.g., providing specific responses in a certain format). I know that I could also send this information with each request… but it would be nicer to train a custom assistant according to my preferences.

1 Like
4

You can look at the CURL requests that are in the API Reference as examples that must be sent to each method.

“train a custom assistant” offers you little except not repeating the instructions again.

You must have support for https with current root certificates. Applications are a bad place to put API credentials.

You can demonstrate complete usage of the Assistants API to a flagship AI model by established proficiency in using the endpoint, and get a preliminary response telling the challenges you will have… and code that doesn’t have useful status polling to actually be functional.

Challenges in Calling the OpenAI Assistants API from VBA:

  1. HTTPS with Modern Secure Ciphers:

    • TLS Version Support: The OpenAI API requires TLS 1.2 or higher for secure HTTPS connections. By default, VBA’s WinHttp and MSXML2 libraries may not use TLS 1.2, especially on older systems like Windows 7.
    • Enabling TLS 1.2: You may need to explicitly enable TLS 1.2 in your VBA application by setting the appropriate options or updating registry settings.

  2. Lack of Native JSON Support:

    • VBA does not have built-in capabilities to parse and generate JSON.
    • Solution: Use a VBA JSON library such as VBA-JSON (available on GitHub) to handle JSON serialization and deserialization.

  3. Handling HTTP Requests:

    • VBA’s HTTP request capabilities are limited compared to modern languages. You can use the MSXML2.XMLHTTP or WinHttp.WinHttpRequest objects for making HTTP calls.
    • Headers and Methods: Ensure that you can set custom headers and handle different HTTP methods (GET, POST, DELETE).

  4. Asynchronous Operations:

    • The provided Python example uses asynchronous programming (asyncio). VBA does not support asynchronous operations natively.
    • Implication: HTTP requests in VBA will be synchronous, which may block the application during the request.

  5. Error Handling and Debugging:

    • Error handling in VBA can be less informative compared to other languages. You’ll need to implement robust error checks to capture and handle API errors effectively.

Example VBA Application to Communicate with OpenAI Assistants API:

Below is an example of a basic VBA application that:

  • Creates a new thread.
  • Sends a message to the thread.
  • Retrieves and displays the assistant’s response.
  • Does not handle image files; it only processes text messages.

Prerequisites:

  1. Add References:

    • Microsoft XML, v6.0: For HTTP requests (MSXML2.XMLHTTP60).
    • Microsoft Scripting Runtime: For dictionary objects (Scripting.Dictionary).

  2. Include a JSON Library:

    • Use the VBA-JSON library to parse JSON responses.
    • Download from VBA-JSON and import JsonConverter.bas into your VBA project.
    • Ensure to set Option Explicit and initialize the JSON parser if required.

  3. Enable TLS 1.2 Support:

    • For systems that do not use TLS 1.2 by default, you may need to add code to enable it.

VBA Code:

Option Explicit

' Requires reference to:
' - Microsoft XML, v6.0
' - Microsoft Scripting Runtime
' Also, include the VBA-JSON library (https://github.com/VBA-tools/VBA-JSON)

Private Const OPENAI_API_URL As String = "https://api.openai.com/v1"
Private Const OPENAI_BETA_HEADER As String = "assistants=v2"

Sub OpenAIChat()
    Dim apiKey As String
    apiKey = Environ("OPENAI_API_KEY")
    If apiKey = "" Then
        MsgBox "OPENAI_API_KEY environment variable not set", vbCritical
        Exit Sub
    End If
    
    Dim threadId As String
    Dim userMessage As String
    Dim assistantResponse As String
    
    ' Enable TLS 1.2
    EnableTLS12
    
    ' Create a new thread
    threadId = CreateThread(apiKey)
    If threadId = "" Then Exit Sub
    
    ' Send a message to the thread
    userMessage = "Hello, how are you today?"
    If Not SendMessage(apiKey, threadId, "user", userMessage) Then Exit Sub
    
    ' Wait for a moment to allow the assistant to process (if necessary)
    Application.Wait Now + TimeSerial(0, 0, 1)
    
    ' Retrieve the assistant's reply
    assistantResponse = GetAssistantReply(apiKey, threadId)
    If assistantResponse = "" Then Exit Sub
    
    ' Display the assistant's reply
    MsgBox "Assistant: " & assistantResponse, vbInformation, "OpenAI Assistant"
    
    ' Optionally delete the thread
    'DeleteThread apiKey, threadId
End Sub

' Function to enable TLS 1.2
Private Sub EnableTLS12()
    Dim winHttpReq As Object
    Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
    winHttpReq.Option(9) = 2048
End Sub

' Function to create a new thread
Private Function CreateThread(apiKey As String) As String
    Dim url As String
    url = OPENAI_API_URL & "/threads"
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Prepare the request body
    Dim reqBody As String
    reqBody = "{}" ' Empty JSON object
    
    ' Open the request
    httpReq.Open "POST", url, False
    httpReq.setRequestHeader "Content-Type", "application/json"
    httpReq.setRequestHeader "OpenAI-Beta", OPENAI_BETA_HEADER
    httpReq.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send the request
    httpReq.send reqBody
    
    If httpReq.Status = 200 Or httpReq.Status = 201 Then
        Dim jsonResponse As Object
        Set jsonResponse = JsonConverter.ParseJson(httpReq.responseText)
        CreateThread = jsonResponse("id")
    Else
        MsgBox "Error creating thread: " & httpReq.Status & vbCrLf & httpReq.responseText, vbCritical, "Error"
        CreateThread = ""
    End If
End Function

' Function to send a message
Private Function SendMessage(apiKey As String, threadId As String, role As String, content As String) As Boolean
    Dim url As String
    url = OPENAI_API_URL & "/threads/" & threadId & "/messages"
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Prepare the request body
    Dim messageData As Object
    Set messageData = CreateObject("Scripting.Dictionary")
    messageData.Add "role", role
    messageData.Add "content", content
    
    Dim reqBody As String
    reqBody = JsonConverter.ConvertToJson(messageData)
    
    ' Open the request
    httpReq.Open "POST", url, False
    httpReq.setRequestHeader "Content-Type", "application/json"
    httpReq.setRequestHeader "OpenAI-Beta", OPENAI_BETA_HEADER
    httpReq.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send the request
    httpReq.send reqBody
    
    If httpReq.Status = 200 Or httpReq.Status = 201 Then
        SendMessage = True
    Else
        MsgBox "Error sending message: " & httpReq.Status & vbCrLf & httpReq.responseText, vbCritical, "Error"
        SendMessage = False
    End If
End Function

' Function to get the assistant's latest reply
Private Function GetAssistantReply(apiKey As String, threadId As String) As String
    Dim url As String
    url = OPENAI_API_URL & "/threads/" & threadId & "/messages?limit=10&order=desc"
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Open the request
    httpReq.Open "GET", url, False
    httpReq.setRequestHeader "OpenAI-Beta", OPENAI_BETA_HEADER
    httpReq.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send the request
    httpReq.send
    
    If httpReq.Status = 200 Then
        Dim jsonResponse As Object
        Set jsonResponse = JsonConverter.ParseJson(httpReq.responseText)
        
        Dim messages As Object
        Set messages = jsonResponse("data")
        
        Dim message As Variant
        For Each message In messages
            If message("role") = "assistant" Then
                GetAssistantReply = message("content")
                Exit Function
            End If
        Next message
    Else
        MsgBox "Error retrieving messages: " & httpReq.Status & vbCrLf & httpReq.responseText, vbCritical, "Error"
        GetAssistantReply = ""
    End If
End Function

' Function to delete a thread (optional)
Private Sub DeleteThread(apiKey As String, threadId As String)
    Dim url As String
    url = OPENAI_API_URL & "/threads/" & threadId
    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' Open the request
    httpReq.Open "DELETE", url, False
    httpReq.setRequestHeader "OpenAI-Beta", OPENAI_BETA_HEADER
    httpReq.setRequestHeader "Authorization", "Bearer " & apiKey
    
    ' Send the request
    httpReq.send
    
    If httpReq.Status = 200 Then
        MsgBox "Thread deleted successfully.", vbInformation, "Success"
    Else
        MsgBox "Error deleting thread: " & httpReq.Status & vbCrLf & httpReq.responseText, vbCritical, "Error"
    End If
End Sub

Explanation of the Code:

  1. EnableTLS12 Subroutine:

    • Ensures that the HTTP requests use TLS 1.2.
    • Sets the Option(9) of WinHttp.WinHttpRequest to 2048, which corresponds to enabling TLS 1.2.

  2. CreateThread Function:

    • Sends a POST request to /v1/threads to create a new thread.
    • Uses an empty JSON object {} as the request body, similar to initializing a thread without messages.
    • Parses the response to extract the id of the new thread.

  3. SendMessage Function:

    • Sends a POST request to /v1/threads/{thread_id}/messages to add a message.
    • Constructs a JSON body with the role and content of the message.
    • Sets the appropriate headers, including Content-Type, OpenAI-Beta, and Authorization.

  4. GetAssistantReply Function:

    • Sends a GET request to /v1/threads/{thread_id}/messages to retrieve messages from the thread.
    • Uses query parameters limit=10 and order=desc to get the latest messages.
    • Iterates through the messages to find the first one with role equal to assistant.
    • Returns the content of the assistant’s message.

  5. DeleteThread Subroutine (Optional):

    • Sends a DELETE request to /v1/threads/{thread_id} to delete the thread.
    • Useful for cleanup after testing.

  6. OpenAIChat Subroutine:

    • Coordinates the process: creates a thread, sends a message, retrieves the assistant’s reply, and displays it.
    • Demonstrates how to use the functions together.

Usage Instructions:

  • API Key:

    • Ensure that your OpenAI API key is set in the environment variable OPENAI_API_KEY.
    • Alternatively, you can assign your API key directly to the apiKey variable (not recommended for security reasons).

  • VBA References:

    • In the VBA editor, go to Tools > References and check:
      • Microsoft XML, v6.0
      • Microsoft Scripting Runtime

  • Include JSON Library:

    • Download JsonConverter.bas from VBA-JSON.
    • Import the file into your VBA project.
    • If necessary, set JsonConverter.JsonLibCompatibility = JsonConverter.JsonLibJsonBag

  • Run the Code:

    • Execute the OpenAIChat subroutine.
    • The assistant’s reply will be displayed in a message box.

Notes on API Methods and Parameters:

  • Headers:

    • "OpenAI-Beta": "assistants=v2" is included as per the API requirements.
    • "Authorization": "Bearer {API_KEY}" is used for authentication.
    • "Content-Type": "application/json" is necessary for POST requests with a JSON body.

  • Endpoints:

    • POST /v1/threads to create a new thread.
    • POST /v1/threads/{thread_id}/messages to send a message.
    • GET /v1/threads/{thread_id}/messages to retrieve messages.
    • DELETE /v1/threads/{thread_id} to delete a thread.

  • Parameters:

    • CreateThread: Empty JSON object {} since we’re not initializing with messages.
    • SendMessage: JSON body with role (“user”) and content (the message text).
    • GetAssistantReply: Query parameters limit and order to retrieve the latest messages.

Handling Errors:

  • The code includes basic error handling using MsgBox to display error messages.
  • Checks the Status property of the HTTP request to determine if the request was successful.
  • Parses the response text and displays any error messages returned by the API.

Potential Issues and Solutions:

  • TLS Errors:

    • If you encounter errors related to TLS or secure connections, ensure that TLS 1.2 is enabled using the EnableTLS12 subroutine.
    • On older operating systems, you may need to update your system or configure registry settings.

  • JSON Parsing Errors:

    • Ensure that the JSON library is correctly included and initialized.
    • Use Option Explicit and declare all variables to prevent typographical errors.

  • API Key Issues:

    • Double-check that the API key is correctly set and has the necessary permissions.
    • Be cautious not to expose your API key in code shared publicly.

Conclusion:

By addressing the challenges of HTTPS support and JSON parsing, we can successfully interact with the OpenAI Assistants API from a VBA application. This example demonstrates how to create a new thread, send a user message, and retrieve the assistant’s response using the exact API methods and parameters provided.

Disclaimer:

  • The OpenAI API and its endpoints may change, especially since some features might be in beta.
  • Always refer to the latest OpenAI API documentation for up-to-date information.
  • Handle API keys securely and follow best practices for storing and using sensitive information.
2 Likes
5

Thank you very much for your detailed response! ““train a custom assistant” offers you little except not repeating the instructions again.” It’s a real shame to read this :\ I was hoping that I could create a new assistant under “Assistants”, preconfigure it with my own information, and then send it information directly via the API interface. I’m currently in the process of integrating your script, but I’m not getting a response yet :wink: But I think the problem lies between the chair and the screen :slight_smile:

6

If you have created an Assistant and have its ID, then that is an API call you don’t have to make in different code, since the assistant is set up with instructions about its behavior, the model, internal tools.

You still have to create a new thread to start a chat, place a new user message into the thread, initiate a run with the assistant ID and the thread ID, poll the run status to see when it completes or is still in progress or errors, then get the most recent message.

Chat Completions is easier: send messages including the system prompt behavior, receive response in the same network request.

1 Like
7

Has anyone had any luck on this? This did not work for me.