How to use ChatGPT.(Microsoft Excel)

06/16/2024

Japanese version.

Introduction to using ChatGPT from Excel.

This procedure is only for applications (Desktop) because it uses VBA and macros.

If you want to use it in the cloud (Online), use Power Automate.

Steps

Get the ChatGPT API key (secret key) in beforehand.

Then paste the code below into a VBA project in an Excel workbook.

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub ChatGPT()
  ' Setting.
  ' API key (secret key).
  Dim APIkey As String: APIkey = "sk-******"
  
  ' Specifies the number of times to wait. If this number is exceeded, the program times out and waits 10 seconds per wait.
  Dim tryMaxCount As Integer: tryMaxCount = 5
  
  ' Specify the cell of the message to be queried.
  Dim CellAsk As String: CellAsk = "Sheet1!A1"
  
  ' Specify the cell that will receive the results.
  Dim CellAnswer As String: CellAnswer = "Sheet1!A2"
  
  ' End setting.
  
  Dim httpReq As Object: Set httpReq = CreateObject("MSXML2.XMLHTTP")
  Dim httpReqBody As String: httpReqBody = ""
  
  With httpReq

        .Open "POST", "https://api.openai.com/v1/completions", False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Authorization", "Bearer " & APIkey

        httpReqBody = "{""model"":""text-davinci-003"", ""prompt"":""" & Range(CellAsk).Value & """, ""max_tokens"":2024, ""temperature"":0, ""top_p"":1}"
        
        .send httpReqBody
        
        Dim count As Integer: count = 0
        Do While .readyState <> 4
          If count < tryMaxCount Then
            Call Sleep(10000)
          Else
            Exit Do
          End If
          
          count = count + 1
        Loop
        
        If .readyState = 4 Then
          Dim textStartIndex As Integer: textStartIndex = InStr(httpReq.responseText, "\n\n") + 4
          Dim textEndIndex As Integer: textEndIndex = InStr(textStartIndex, httpReq.responseText, """")
  
          Range(CellAnswer).Value = Replace(Mid(httpReq.responseText, textStartIndex, textEndIndex - textStartIndex), "\n", Chr(10))
        Else
          Range(CellAnswer).Value = "Could not retrieve (timeout)"
        End If
        
  End With
  

  Set httpReq = Nothing
  MsgBox ("End")
End Sub

Rewrite lines 6-15.

Change the red part of line 6 to the API key (secret key) you obtained.

Dim APIkey As String: APIkey = "API key (secret key)"

The larger the number in line 9, the longer to wait. When the server load is heavy, if this number is not large, the process may be terminated halfway through.

Dim tryMaxCount As Integer: tryMaxCount = 5

Line 12 specifies the cell that sets the contents of the query to ChatGPT.

Dim CellAsk As String: CellAsk = "Sheet1!A1"

Line 15 specifies the cell to set the response from ChatGPT.

Dim CellAnswer As String: CellAnswer = "Sheet1!A2"

After setting up, place a button or something on the worksheet to call ChatGPT.

---

Links

ChatGPT Articles

Tips. Others.(Microsoft Excel)