Http request to Azure translator from vba in MS Access

Question

I'd like to send http request to api.cognitive.microsofttranslator.com/translate and send some text to translate, but it does not work.

I try with this request:

Public Sub translate()
     Dim strUrl As String
     Dim params As String
     Dim strResponseHeaders As String
     Dim allResponseHeader As String
     Dim strResponse As String
     Dim body As String
     Dim phrase As String: phrase = "some text to translate"
     Dim target As String: target = "mk"
     Dim some_key as string : some_key = "somekey" 
     dim location as string: location = "west..."

     body = "[{""text"":""" & phrase & """}]"
     strUrl = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0"
     params = "&from=en&to=" & target

     Dim hReq As Object
     Set hReq = CreateObject("MSXML2.XMLHTTP")
     With hReq
        .Open "POST", strUrl & params, False
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "Content-Type", "charset=UTF-8"
        .setRequestHeader "Ocp-Apim-Subscription-Key", some_key
        .setRequestHeader "Ocp-Apim-Subscription-Region", location
        .setRequestHeader "Content-Length", Len(body)
        
        .send body
        strResponseHeaders = .StatusText
        strResponse = .responseText
        allResponseHeader = .getAllResponseHeaders

       'translate = hReq.ResponseText
    End With
End Sub

I get an error at .send body

== the parameter is not correct

I tried with curl request from batch file. If I run from cmd it works fine :

set "traceId=%random%%random%%random%"
set "endpoint=https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=en&to=mk"
set "requestData=[{""text"":""the price is""}]"

curl --location "%endpoint%" ^
    --header "Ocp-Apim-Subscription-Key: %key%" ^
    --header "Ocp-Apim-Subscription-Region: %region%" ^
    --header "X-ClientTraceId: %traceId%" ^
    --header "Content-Type: application/json" ^
    --header "Content-Type: charset=UTF-8" ^
    --data "%requestData%"

Answer 1

HTTP request to Azure translator from VBA in MS Access

You can use the below code to use the HTTP request to Azure translator from vba in MS Access.

Code:

Imports System.IO
Imports System.Net
Imports System.Text
Imports Newtonsoft.Json

Module Program
    Sub Main(args As String())
        Console.OutputEncoding = Encoding.UTF8
        Dim sourceLanguage As String = "en"
        Dim targetLanguage As String = "mk"
        Dim translatedText As String = Translator.TranslateText("Welcome to my world!", sourceLanguage, targetLanguage)
        Console.WriteLine(translatedText)
    End Sub
End Module

Public Class Translator
    Private Shared subscriptionKey As String = "<Your api key>"
    Private Shared location As String = "<Your location>"

    Public Shared Function TranslateText(ByVal text As String, ByVal sourceLanguage As String, ByVal targetLanguage As String) As String
        Dim endpoint As String = $"https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from={sourceLanguage}&to={targetLanguage}"
        Dim requestBody As Object = New Object() {New With {Key .Text = text}}
        Dim requestBodyJson As String = JsonConvert.SerializeObject(requestBody)


        Dim request As HttpWebRequest = CType(WebRequest.Create(EndPoint), HttpWebRequest)
        request.Headers.Add("Ocp-Apim-Subscription-Key", subscriptionKey)
        request.Headers.Add("Ocp-Apim-Subscription-Region", location)
        request.ContentType = "application/json charset=UTF-8"
        request.Method = "POST"

        Using streamWriter As StreamWriter = New StreamWriter(request.GetRequestStream())
            streamWriter.Write(requestBodyJson)
        End Using

        Dim response As HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

        Using streamReader As StreamReader = New StreamReader(response.GetResponseStream(), Encoding.UTF8)
            Dim responseJson As String = streamReader.ReadToEnd()
            Dim result As Object() = JsonConvert.DeserializeObject(Of Object())(responseJson)
            Dim translation As String = result(0)("translations")(0)("text").ToString()
            Return translation
        End Using
    End Function
End Class

The above code is used to translate text from one language to another using Azure cognitive service. It constructs an HTTP request to the Translator API endpoint with the appropriate headers and request body and parses the response to extract the translated text.

Also, you need to use the Newtonsoft.Json library to serialize and deserialize JSON data.

Output:

Добредојдовте во мојот свет!

Reference:

Translator Translate Method - Azure AI services | Microsoft Learn