How do ask ChatGPT with API from Excel macros (vba)?

Question

I'd like to use excel to ask ChatGPT questions and get them back in a other cell. I have an API which is given in cell "A1". The question should be taken out of "A3" - the answer should be in "A6":

  Sub SendQuestionToGPT3()
  'Declare variables
  
  Dim request As Object
  Dim response As String
  Dim API As String
  
  API = Worksheets("API").Range("A1").Value

  'Set the question in a variable
  Dim question As String
  question = Range("A3").Value

  'Create an HTTP request object
  Set request = CreateObject("MSXML2.XMLHTTP")

  'Set the API endpoint and make the request
  request.Open "POST", "https://api.openai.com/v1/engines/davinci/jobs", False
  request.setRequestHeader "Content-Type", "application/json"
  request.setRequestHeader "Authorization", "Bearer " & API
  request.send "{""prompt"":""" & question & """,""max_tokens"":1000}"

  'Get the response and parse it into a string
  response = request.responseText
  response = Replace(response, ",""choices"":[]", "")
  response = Replace(response, """text"":""", "")
  response = Replace(response, """}", "")

  'Display the response in a cell
  Range("A6").Value = response

  'Clean up the object
  Set request = Nothing
End Sub

But i get this error back:

{ "error": { "message": "Unknown endpoint for this model.", "type": "invalid_request_error", "param": null, "code": null } }

Whats wrong with this code? Thanks!

That's not ChatGPT. That's Davinchi, an older model. – Cerbrus Feb 13 '23 at 08:48 — Cerbrus, Feb 13 '23 at 08:48

mike.slomczynski · Answer 1 · 2023-02-10T18:25:20.937

4

A few things that will help. Don't use the Engines endpoints because it's deprecated.

GET https://api.openai.com/v1/engines/davinci/

Also the Engines endpoint does not respond to a prompt. What it does is

Lists the currently available (non-finetuned) models, and provides basic information about each one such as the owner and availability.

Instead use the Completion endpoint.

POST https://api.openai.com/v1/completions

In order to use the API you'll have to add model to your request. Something like this.

{
  "model": "text-davinci-003",
  "prompt": "How are you?",
  "max_tokens": 256
}

Hope that helps.

edited Feb 10 '23 at 18:25

answered Feb 10 '23 at 17:07

mike.slomczynski

168
7

Unfortunately not. I've changed: request.Open "POST", "https://api.openai.com/v1/engines/davinci/jobs", False to request.Open "POST", "https://api.openai.com/v1/completions", False and get: "message": "you must provide a model parameter", – Jens W. Feb 10 '23 at 17:40
In this line `request.send "{""prompt"":""" & question & """,""max_tokens"":1000}"` add the model you want to use probably `"model": "text-davinci-003"`. It should look something like this: ``request.send "{""model"": ""text-davinci-003"",""prompt"":""" & question & """,""max_tokens"":1000}"`` – mike.slomczynski Feb 10 '23 at 17:48
The model is a required field to use the Completion endpoint. – mike.slomczynski Feb 10 '23 at 17:50
Did the change to: request.send "{""model"": ""text-davinci-003"",""prompt"":""" & question & """,""max_tokens"":1000}" But receive this: { "error": { "message": "Cannot specify both model and engine", "type": "invalid_request_error", "param": null, "code": null } } – Jens W. Feb 10 '23 at 18:40
1

The error is telling you that you are using a model and an engine, but you can only use one. Make sure that you are not using `api.openai.com/v1/engines/davinci/jobs` and `model:text-davinci-003`. Only one or the other. – mike.slomczynski Feb 10 '23 at 18:53
1

And again, don't use `api.openai.com/v1/engines/davinci/jobs` if you want to receive prompts from OpenAI because that's not what it does. – mike.slomczynski Feb 10 '23 at 18:54

score 1 · Answer 2 · answered Mar 31 '23 at 13:57

This worked for me in MS Access VBA. It uses Microsoft Scripting Runtime and the JsonConverter module which is used to extract the text from the response. The response is written directly in the the current code module window.

Paste this code into a module.

Option Compare Database
Option Explicit

Const API_ENDPOINT As String = "https://api.openai.com/v1/completions"
Const API_KEY As String = "YOUR_API_KEY_HERE"

Sub SendPostRequest()

    On Error GoTo SendPostRequest_Error

    Dim CodeModule As CodeModule
    Set CodeModule = Application.VBE.ActiveCodePane.CodeModule
    Dim lnglineCount As Long

    ' Set the API endpoint
    Dim endpoint As String
    endpoint = "https://api.openai.com/v1/completions"

    ' Set the JSON payload
    Dim payload As String
    payload = "{""model"": ""text-davinci-003"", ""prompt"": ""write a VBA function to bubble sort. please indent and comment the code and add error traping"", ""max_tokens"": 1000}"

    ' Create the HTTP request object
    Dim xhr As Object
    Set xhr = CreateObject("MSXML2.XMLHTTP")

    ' Set the request method and endpoint
    xhr.Open "POST", endpoint, False

    ' Set the request headers
    xhr.setRequestHeader "Content-Type", "application/json"
    xhr.setRequestHeader "Authorization", "Bearer " & API_KEY

    ' Send the request with the payload
    xhr.Send payload
    
    lnglineCount = CodeModule.CountOfLines
    CodeModule.InsertLines lineCount + 1, ExtractTextElement(xhr.responseText)

    Debug.Print "Finished"

    On Error GoTo 0
    Exit Sub

SendPostRequest_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendPostRequest, line " & Erl & "."

End Sub

Function ExtractTextElement(strResp As String)

    On Error GoTo ExtractTextElement_Error
    Dim responseText As String
    Dim json As Object
    
    responseText = strResp
    
    Set json = JsonConverter.ParseJson(responseText)
    
    Dim textElement As String
    textElement = json("choices")(1)("text")
    
    ExtractTextElement = textElement
    
    On Error GoTo 0
    Exit Function

ExtractTextElement_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExtractTextElement, line " & Erl & "."

End Function

How do ask ChatGPT with API from Excel macros (vba)?

2 Answers2