API - Allow the API to look at a specific website inside an Excel cell

API
, ,
1

Hi !
I’m currently creating VBA Functions to read information about cells and to read about a website on an Excel sheet, everything is working great, the program can read the information well, but do not want to read information about a specific website I have on a cell. Do you have a solution ?

Here is the code:

Function GetGPT4Response(question As String, labelsRange As Range, dataRange As Range) As String
Dim http As Object
Set http = CreateObject(“MSXML2.ServerXMLHTTP.6.0”)

Dim apiKey As String
apiKey = "API-KEY" ' Ensure to replace this with your actual API key

' API URL parameter
Dim apiUrl As String
apiUrl = "https://api.openai.com/v1/chat/completions"

' Request parameter
Dim postData As String
postData = GenerateDataContent(question, labelsRange, dataRange)

Debug.Print "API URL: " & apiUrl
Debug.Print "Post Data: " & postData

' Setting up the HTTP request
With http
    .Open "POST", apiUrl, False
    .SetRequestHeader "Content-Type", "application/json"
    .SetRequestHeader "Authorization", "Bearer " & apiKey
    .Send postData
    
    Debug.Print "Request Sent"
    
    ' Handle the response
    If .Status = 200 Then
        Debug.Print "Response: " & .ResponseText
        GetGPT4Response = ParseGPTResponse(.ResponseText)
    Else
        Debug.Print "Error: " & .Status & " " & .StatusText
        Debug.Print "Error Details: " & .ResponseText
        GetGPT4Response = "Error: " & .Status & " " & .StatusText
    End If
End With

End Function

Function GenerateDataContent(question As String, labelsRange As Range, dataRange As Range) As String
Dim i As Integer
Dim userDetails As String
userDetails = “”

' Concatenate labels and data into a single string
For i = 1 To labelsRange.Cells.Count
    userDetails = userDetails & labelsRange.Cells(1, i).Value & ": " & dataRange.Cells(1, i).Value & ", "
Next i

' Create a combined message with the question and user details
Dim userMessage As String
userMessage = question & " Here are some details: " & userDetails

' Construct the JSON data for the API call
Dim data As String
data = "{""model"": ""gpt-4"", ""messages"": [{""role"": ""system"", ""content"": ""You are a helpful assistant.""}, {""role"": ""user"", ""content"": """ & userMessage & """}]}"

GenerateDataContent = data

End Function

Function ParseGPTResponse(jsonResponse As String) As String
’ Parse and extract the assistant’s message from API response
Dim json As Object
Set json = JsonConverter.ParseJson(jsonResponse)
ParseGPTResponse = json(“choices”)(1)(“message”)(“content”)
End Function

On a cell I’m currently writing:
=GetGPT4Response(“Write a compliment about the person in the company according to the information and to the website. Maximum 20 words”;B1:E1; B4:E4)

In B1 to E1 I have the labels (Website, Job_Title, Person_First_Name, Person_Last_Name)

In B4 to E4 I have the real information.

If you need more information just tell me I’ll explain in more details.

Thanks in advance !

Zoltan