【API-VBA】Web APIを使ってExcel文書を翻訳をする（DeepL API）

Sub ExcelTranlsation()

    Dim FileName As String
    Dim wb As Workbook
    
    'Excelファイルを選択する
    FileName = Application.GetOpenFilename(FileFilter:="Excelファイル,*.xls*")
    If FileName = "False" Then
        Exit Sub
    End If
    
    'Excelファイルを開く
    Set wb = Workbooks.Open(FileName, False, True)
    
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    Dim rng As Variant  '二次元配列として取得
    Dim SourceSentense As String '原文
    Dim TranslatedSentense As String '翻訳文
    
    '各シートをコピーし、シート内のセルを翻訳する
    For Each ws In wb.Worksheets
        If Not ws.Name Like "JP-EN" Then
            ws.Copy After:=ws
            rng = ws.Range(ws.Cells(1, 1), ws.UsedRange)
            
            If IsArray(rng) = False Then 'rngが"A1"単一セルの場合は変数となる為配列にする必要あり
                ReDim rng(1, 1)
                rng(1, 1) = ws.Cells(1, 1).Value
            End If

            For j = 1 To UBound(rng, 2)
                For i = 1 To UBound(rng, 1)
                    If Not rng(i, j) = "" Then '空白の場合無視
                        SourceSentense = rng(i, j)
                        TranslatedSentense = getTranslation(SourceSentense)
                        rng(i, j) = TranslatedSentense
                    End If
                Next i
            Next j
            wb.Worksheets(ws.Index + 1).Cells(1, 1).Resize(UBound(rng, 1), UBound(rng, 2)).Value = rng
            wb.Worksheets(ws.Index + 1).Name = ws.Name & "(JP-EN)"
        End If
    Next
    
    Application.DisplayAlerts = False
    wb.SaveAs wb.Path & "\(JP-EN)" & Dir(FileName)
    Application.DisplayAlerts = True
    
    
    
End Sub
Function getTranslation(ByVal SourceSentense As String) As String

    'DeepLで取得したkey
    Const APIkey As String = "(取得したAPI key)"
    
     '日本語の場合を考慮してUTF-8にエンコードする
    SourceSentense = WorksheetFunction.EncodeURL(SourceSentense)
    
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
   
   
    With http
    
        'HTTPリクエスト（POSTメソッド）
        Call .Open("POST", "https://api-free.deepl.com/v2/translate?text=" & SourceSentense & "&source_lang=JA&target_lang=EN")
        Call .setRequestHeader("Authorization", "DeepL-Auth-Key " & APIkey)
        Call .send
        
        Do
            If .readyState = 4 Then Exit Do
            DoEvents
        Loop
        
        'HTTPレスポンスの内容確認
        '*成功時：｛Res="status":200, "message":"ok"}
        'Debug.Print .responseText
        'Debug.Print .Status
        
        'ダブルクォーテーションで区切って配列として取得
        Dim buf As Variant
        Dim TranslatedSentense As String
        
        buf = Split(http.responseText, """")
        getTranslation = buf(9)
        'Debug.Print TranslatedSentense
               
    End With
End Function

FileName = Application.GetOpenFilename(FileFilter:="Excelファイル,*.xls*")

If Not ws.Name Like "JP-EN" Then
            ws.Copy After:=ws
            rng = ws.Range(ws.Cells(1, 1), ws.UsedRange)

For j = 1 To UBound(rng, 2)
                For i = 1 To UBound(rng, 1)
                    If Not rng(i, j) = "" Then '空白の場合無視
                        SourceSentense = rng(i, j)
                        TranslatedSentense = getTranslation(SourceSentense)
                        rng(i, j) = TranslatedSentense
                    End If
                Next i
            Next j
            wb.Worksheets(ws.Index + 1).Cells(1, 1).Resize(UBound(rng, 1), UBound(rng, 2)).Value = rng

wb.SaveAs wb.Path & "\(JP-EN)" & Dir(FileName)