How to create a Deepl Glossary CSV separated from a Excel selection using API with VBA

Question

I have the following code which is executed from a Excel From, which also containing the variables.

This code works for CSV format, but I cannot get it to work for CSV.

DEEPL support mention this.

In addition, the following restrictions apply:

Only, (comma) is allowed as a value separator, each field may optionally be enclosed in double quotes, fields containing double quotes or commas must be enclosed in double quotes, if double quotes are used to enclose fields, then a double quote appearing inside a field must be escaped by preceding it with another double quote. Currently it is not possible to obtain entries in the CSV format. Only the upload of a glossary supports the CSV format.

The example below shows how valid glossary entries in CSV format (using the four-column variant) could be created in a programming language with backslash escape sequences (e.g. Python, JavaScript, etc.):

"sourceEntry1,targetEntry1,en,de\n\"source\"\"Entry\",\"target,Entry\",en,de"

My current code:

Sub CreateDeepLGlossaryFromForm()
    On Error Resume Next ' Enable error handling

    ' Get the glossary name, source/target language, and range from the form
    Dim glossaryName As String
    
  
    glossaryName = frmCreateGlosserySub.TBGlosseryName.value


    Dim sourceLang As String
    sourceLang = frmCreateGlosserySub.CBSourceLang.value

    Dim targetLang As String
    targetLang = frmCreateGlosserySub.CBTargetLang.value ' Assuming ListIndex starts from 0
    
    Dim glossaryRange As Range

    ' Check if RefEditGlossaryRange is empty
    If frmCreateGlosserySub.RefEditGlosseryRange.Text = "" Then
    
        MsgBox "Please select a range using the RefEdit control before running this macro.", vbExclamation, "Range Selection Error"
        Exit Sub
    End If

    ' Retrieve the glossary range from RefEdit control
    On Error Resume Next
    Set glossaryRange = Range(frmCreateGlosserySub.RefEditGlosseryRange.Text)
    On Error GoTo 0

    If glossaryRange Is Nothing Then
        MsgBox "Please enter a valid range in the RefEdit control before running this macro.", vbExclamation, "Invalid Range"
        Exit Sub
    End If
    
    

    ' Check if a valid range is selected
    'Dim glossaryRange As Range
    On Error Resume Next
    Set glossaryRange = Range(RefEditGlosseryRange.Text)
    On Error GoTo 0

    ' Check if RefEditGlossaryRange is empty
    If frmCreateGlosserySub.RefEditGlosseryRange.Text = "" Then
        MsgBox "Please select a range using the RefEdit control before running this macro.", vbExclamation, "Range Selection Error"
        'Exit Sub
    End If

    If glossaryRange Is Nothing Then
        MsgBox "Please enter a valid range in the RefEdit control before running this macro."
        
        Exit Sub
    End If

    ' Construct the entries using the selected range
    Dim entries As String
    Dim cell As Range

    
      'Loop through each row in the selected range
    For Each Row In glossaryRange.Rows
        'entries = entries & Row.Cells(1, 1).value & "/" & Row.Cells(1, 2).value & vbCrLf
       entries = entries & Row.Cells(1, 1).value & "\t" & Row.Cells(1, 2).value & "\n"
    Next Row

    ' Remove the trailing vbCrLf
    entries = Left(entries, Len(entries) - 2)
    
    

    ' Create DeepL glossary request body
    Dim requestBody As String
    requestBody = "{""name"":""" & glossaryName & """,""source_lang"":""" & sourceLang & """,""target_lang"":""" & targetLang & """,""entries"":""" & entries & """,""entries_format"":""tsv""}"

    ' Send the request to DeepL API
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    ' Set the URL
    Dim url As String
    url = "https://api.deepl.com/v2/glossaries"

    ' Set the request method and URL
    xmlhttp.Open "POST", url, False

    ' Set the request headers
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    xmlhttp.setRequestHeader "Authorization", "DeepL-Auth-Key " & apiKey ' Replace YOUR_API_KEY with your actual DeepL API key
    xmlhttp.setRequestHeader "User-Agent", "YourApp/1.0"

    ' Send the request
    xmlhttp.send requestBody

    ' Check for errors
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description
    Else
        ' Check the response status
        If xmlhttp.Status = 201 Then
            ' Request was successful
            MsgBox "Request successful. Response: " & xmlhttp.responseText
        Else
            ' Request failed
            MsgBox "Request failed. Status: " & xmlhttp.Status & vbCrLf & "Response: " & xmlhttp.responseText & "Glossery Name: " & glossaryName _
            & " Source Language: " & sourceLang & " Target Language: " & targetLang & " RangeSlection: " & frmCreateGlosserySub.RefEditGlosseryRange.Text
        End If
    End If
End Sub

I tried chatGPT to amend the code to create the glossary CSV separated but keep getting error:

Request failed. Status: 400
Response: {"message":"Bad request","detail":"Missing or invalid argument: $"}

...

Sub SendDeepLAPIRequestNew()
    On Error Resume Next ' Enable error handling
    
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP")

    ' Set the URL
    Dim url As String
    url = "https://api.deepl.com/v2/glossaries"

    ' Set the request method and URL
    xmlhttp.Open "POST", url, False

    ' Set the request headers
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    xmlhttp.setRequestHeader "Authorization", "DeepL-Auth-Key " & APIkey ' Replace YOUR_API_KEY with your actual DeepL API key
    xmlhttp.setRequestHeader "User-Agent", "YourApp/1.2.3"

    ' Construct the request body with CSV entries
    Dim requestBody As String
    Dim csvEntries As String
    csvEntries = """sourceEntry1"",""targetEntry1"",""en"",""de""\n""\""source\""\""Entry\"""",""\""target,Entry\"""",""en"",""de"""

    requestBody = "{""name"":""HENRIK_TEST2"",""source_lang"":""en"",""target_lang"":""de"",""entries_format"":""csv"",""entries"":""" & csvEntries & """}"

    Range("C11").Value = "RequestBody" & vbNewLine & requestBody

    ' Send the request
    xmlhttp.send requestBody

    ' Check for errors
    If Err.Number <> 0 Then
        MsgBox "Error: " & "Is an actual error, inserted by Henrik" & Err.Description
        Exit Sub
    End If

    ' Check the response status
    If xmlhttp.Status = 201 Then
        ' Request was successful
        MsgBox "Request successful. Response: " & xmlhttp.responseText
    Else
        ' Request failed
        MsgBox "Request failed. Status: " & xmlhttp.Status & vbCrLf & "Response: " & xmlhttp.responseText
    End If
End Sub

Answer 1

This code works for CSV format, but I cannot get it to work for CSV.

Do you mean the code works for TSV, but doesn't work for CSV?

If so, I think there is a problem with your csvEntries variable - according to the DeepL docs, it needs to be 2 or 4 comma separated values, but when I print your csvEntries variable I get "sourceEntry1","targetEntry1","en","de"\n"\"source\"\"Entry\"","\"target,Entry\"","en","de", which has more than 4 comma separated values.

I just noticed that this example value is from the DeepL docs, this is erroneous and I'll fix it.

If you change

Dim csvEntries As String
    csvEntries = """sourceEntry1"",""targetEntry1"",""en"",""de""\n""\""source\""\""Entry\"""",""\""target,Entry\"""",""en"",""de"""

to

Dim csvEntries As String
    csvEntries = "sourceEntry1,targetEntry1,en,de"

your code should work.

DeepL Docs: https://developers.deepl.com/docs/api-reference/glossaries#csv-comma-separated-values