VBA Code for Integrating ChatGPT API in Excel

[miltonku]

Hello Everyone,


I need to use VBA in Excel to rephrase and shorten the text content of each non-empty cell in column B in Bulk using the ChatGPT API. I've written some VBA code to accomplish this, but I'm running into an issue and could use some help.

[arnelgp]

maybe share your workbook or the code so we may see the code?

[Aussiebear]

Will this satisfy you as a starter?

Function ChatGPT(prompt As String) As String
    ' Remember to replace the placeholder with your actual OpenAI API key
    Const apiKey As String = "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"
    Const requestUrl As String = "https://api.openai.com/v1/chat/completions"
    Dim http As Object, regex As Object, matches As Object
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set regex = CreateObject("VBScript.RegExp")
    Dim requestBody As String
    requestBody = "{""model"": ""gpt-3.5-turbo"", ""messages"": [{""role"": ""user"", ""content"": """ & prompt & """}]}"
    With http
        .Open "POST", requestUrl, False
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Authorization", "Bearer " & apiKey
        .Send requestBody
    End With
    If http.Status = 200 Then
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = """content"":\s*""([^""]+)"""
        End With
        Set matches = regex.Execute(http.ResponseText)
        If matches.Count >= 1 Then
            ChatGPT = Replace(matches(0).SubMatches(0), "\n", vbLf)
        Else
            ChatGPT = "Content not found in response."
        End If
    Else
        ChatGPT = "Error: " & http.Status & " - " & http.StatusText
    End If
    Set http = Nothing
    Set regex = Nothing
End Function

[miltonku]

So great! Many many thanks.