Используете API и JSON в VBA?

Руководство VBA по использованию API

Используете API и JSON в VBA?

Если вашему макросу VBA нужны данные из других источников или внешний процесс должен быть реализован в вашей программе, API — это то, что вам нужно. Многие приложения имеют интерфейс программирования (API), который является самым простым способом взаимодействия программного обеспечения друг с другом.

Настройка для использования API

Чтобы использовать API в VBA, нам нужно настроить http-запрос, который включает в себя определенные параметры того, что мы хотим, чтобы API делал. В этом проекте я буду использовать api.nationalize.io, чтобы предсказать национальность имени, введенного в рабочую книгу, чтобы продемонстрировать подключение к API и работу с данными из него.

Прежде чем мы сможем подключиться к API, мы должны добавить библиотеку Microsoft XML, чтобы иметь возможность отправлять HTTP-запросы. Перейдите к инструментам›ссылки и проверьте его.

Теперь нам нужна необходимая информация к запросу. В объекте MSXML2.XMLHTTP мы открываем запрос на получение данных с веб-сайта, объединяем базовый URL со значением параметра baseURL + имя, заголовок с типом контента указывает формат данных json. Затем мы отправляем эти запросы и собираем статус ответа и текст в переменные. Статус сообщает нам, правильно ли обработан запрос или выдает конкретную ошибку о том, что не так.

Dim baseURL, name As String
baseURL = "https://api.nationalize.io"
name = "/?name=" & <cell value with name>
Dim status, response As String
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", baseURL & name, False
    .setRequestHeader "Content-Type", "application/json"
    .send
    status = .status
    response = .responseText
End With

Статус сообщает нам, правильно ли обработан запрос или выдает конкретную ошибку о том, что не так. Статус 200 означает, что запрос правильно оформлен с параметрами и выполнен. Список номеров ошибок обычно доступен в документации по API. Текст ответа приходит в виде текста и содержит прогнозы национальности.

Как обрабатывать JSON

Формат JSON хорошо обрабатывается большинством других языков программирования. В VBA лучше всего использовать одну из существующих библиотек для преобразования текста ответа в объект JSON. Я использую модуль VBA-JSON, который можно встроить в любой проект. Перенесем результаты в объект JSON и в книгу.

'transform response text to JSON
Dim JSON As Dictionary
Set JSON = JsonConverter.ParseJson(response)
Debug.Print (JsonConverter.ConvertToJson(JSON))
{"name":"Jurgen","country":[{"country_id":"BE","probability":0.36084541872899245},{"country_id":"AL","probability":0.2482687644789702},{"country_id":"MT","probability":0.1952108073789147}]}

Мы можем вставлять элементы словаря JSON один за другим, но мы также можем вставлять их все, используя цикл for each.

'we can adresss each element in country list
Range("A1") = JSON("country")(1)("country_id")  //BE
'or we can paste all of them in a loop
Dim iter As Long
iter = 1
Dim Value As Dictionary
For Each Value In JSON("country")
    Cells(4 + iter, 2) = Value("country_id")
    Cells(4 + iter, 3) = Value("probability")
    iter = iter + 1
Next Value

Резюме

Таким образом, мы получаем список стран и вероятностей, которые будут меняться в зависимости от имени, которое мы проверяем. Использование JSON в VBA определенно требует особого подхода, но это определенно возможно!

Используете API и JSON в VBA?

Руководство VBA по использованию API