如何通过Excel VBA连接Azure OpenAI实例？

问题描述 #

我正尝试通过Excel VBA连接我们的Azure OpenAI实例，由于没有OpenAI库，手动设置api_type、api_version、model等参数但未能成功，请问有人成功实现过吗？

以下是脱敏后的代码：

Function myAI(thePrompt As String)
    Dim MAX_TOKENS As String
    Dim TEMPERATURE As String
    Dim MODEL As String
    Dim apiEndpoint
    Dim apiKey
    Dim response As String

    apiEndpoint = <redacted>
    apiKey = <redacted>
    MAX_TOKENS = 800
    TEMPERATURE = 0
    MODEL = <redacted>
    
    Dim requestBody As String
    requestBody = "{" & _
        """model"": " & MODEL & "," & _
        """prompt"": """ & thePrompt & """," & _
        """max_tokens"": " & MAX_TOKENS & "," & _
        """temperature"": " & TEMPERATURE & _
        "}"
    Set httpObj = CreateObject("WinHttp.WinHttpRequest.5.1")
    httpObj.SetTimeouts 90000, 90000, 90000, 90000
    httpObj.Option(4) = 13056
    httpObj.Open "POST", apiEndpoint, False
    httpObj.setRequestHeader "Content-type", "application/json"
    httpObj.setRequestHeader "Authorization", "Bearer " & apiKey
    httpObj.Send requestBody
    myAI = httpObj.responseText
End Function

问题分析与修正方案 #

你的代码主要缺少Azure OpenAI专属请求头，同时存在JSON格式和变量类型的潜在问题，以下是修正后的完整实现：

1. 添加Azure OpenAI必需请求头 #

Azure OpenAI要求必须指定api-type和api-version（可附加到URL末尾或放在请求头中），部分区域还需指定api-region。

2. 修正变量类型与JSON格式 #

• 将MAX_TOKENS和TEMPERATURE改为数值类型，避免字符串拼接隐患
• 转义prompt中的双引号，防止JSON格式错误
• 确保MODEL参数被双引号包裹（部署名称为字符串类型）

修正后的代码 #

Function myAI(thePrompt As String) As String
    ' 配置参数
    Const API_ENDPOINT As String = "<redacted>" ' 示例格式：https://{你的资源名}.openai.azure.com/openai/deployments/{部署名}/completions
    Const API_KEY As String = "<redacted>"
    Const API_VERSION As String = "2023-05-15" ' 根据Azure OpenAI支持版本调整
    Const MODEL As String = "<redacted>" ' 你的部署名称
    Const MAX_TOKENS As Integer = 800
    Const TEMPERATURE As Double = 0
    
    Dim httpObj As Object
    Dim requestBody As String
    Dim escapedPrompt As String
    
    ' 转义prompt中的双引号，避免JSON格式错误
    escapedPrompt = Replace(thePrompt, """", "\""")
    
    ' 构建正确的JSON请求体
    requestBody = "{" & _
        """model"": """ & MODEL & """," & _
        """prompt"": """ & escapedPrompt & """," & _
        """max_tokens"": " & MAX_TOKENS & "," & _
        """temperature"": " & TEMPERATURE & _
        "}"
    
    ' 初始化HTTP请求对象
    Set httpObj = CreateObject("WinHttp.WinHttpRequest.5.1")
    httpObj.SetTimeouts 90000, 90000, 90000, 90000
    httpObj.Option(4) = 13056 ' 忽略SSL证书错误（仅测试用，生产环境建议移除）
    
    ' 配置请求
    httpObj.Open "POST", API_ENDPOINT & "?api-version=" & API_VERSION, False
    httpObj.setRequestHeader "Content-Type", "application/json"
    httpObj.setRequestHeader "Authorization", "Bearer " & API_KEY
    httpObj.setRequestHeader "api-type", "azure"
    
    ' 发送请求并捕获错误
    On Error Resume Next
    httpObj.Send requestBody
    If Err.Number <> 0 Then
        myAI = "请求错误: " & Err.Description
    Else
        myAI = httpObj.responseText
    End If
    On Error GoTo 0
    
    Set httpObj = Nothing
End Function

额外注意事项 #

• 确认apiEndpoint格式正确：必须包含部署路径，示例为https://{resource-name}.openai.azure.com/openai/deployments/{deployment-name}/completions
• 如果使用GPT-3.5/4的聊天接口（chat completions），请求体需调整为对话数组格式，示例：

  {"model": "your-deployment-name", "messages": [{"role": "user", "content": "你的问题"}]}
  

• 生产环境不要保留Option(4) = 13056，确保SSL证书配置正确

内容的提问来源于stack exchange，提问作者J Gerber