Краткий ликбез для неопытных пользователей Excel VBA (Visual Basic for Applications)Для обмена со Спайдером используется текстовый протокол JSON. Он использует пары “ключ”:“значение”. Вот пример:
{"ключ1":"openFile", "ключ2":"значение"}
Значения могут быть сложными:
{ "docHandle" : 45718736, "ключ3" : { "Code" : "1km_road", "ключ4" : 1}}
К сожалению, в VBA Excel нет стандартных средств для обмена по JSON, поэтому решения могут быть разными.
В принципе, JSON-строки для запроса можно собрать “вручную”, с помощью строковых операций. Но это нудно, и легко ошибиться с балансом скобок. Гораздо удобнее использовать словари (Dictionary). Словарь хранит пары “ключ”-“значение” - как раз то, что нужно. Если значение сложное, то это будет вложенный словарь, или коллекция (Collection) для массивов.
Разберем пример. Откройте окно с VBA (Alt + F11). По умолчанию отображается не тот код, который нам интересен. Найдите в дереве проекта модуль
DemoSource и откройте его. На остальной код можно вообще не смотреть.
Процедура Main начинается с инициализации. Проект в строке "FileName =…" замените на любой свой.
Затем заполняются словари. Чтобы сократить объем рутинного кода предусмотрены функции для создания словарей с одной, двумя и тремя парами “ключ”-“значение”: NewD1, NewD2 и NewD3.
При запросе таблицы можно указать коды нужных колонок (иначе вернуться все видимые). Как узнать коды? В Спайдере в свойствах колонки есть поле “Код колонки”.
Когда главный словарь заполнен параметрами команды, его нужно преобразовать в JSON-строку (сериализовать). Это выглядит так:
S = JS.ConvertToJson(D)
Для передачи сформированной строки используется объект Http. Внутри он использует библиотеку WinHttp.WinHttpRequest.5.1, но знать это необязательно. Вот как выглядит вызов:
Set Result = Http.Post(S, Txt)
Параметр Txt (выходной) - необязательный, он содержит ответ в текстовом виде, это бывает полезно при отладке.
Разобрать полученную JSON-строку не так просто. Есть решения, которые делают это чисто на VBA (
https://github.com/VBA-tools/VBA-JSON), но там слишком большой объем кода. Да и быстродействие, скорее всего, не очень.
В данном примере используется библиотека MSScriptControl.ScriptControl, но знать это тоже не обязательно. Вот как выглядит получение простого значения (а структуру ответа мы знаем из документации):
Handle = JS.GetVal(Result, "docHandle")
Для сложных объектов (в примере – массив) используется JS.GetObj. Для GetObj обязательно использовать слово Set:
Set Arr = JS.GetObj(Result, "array")
Далее массив перебирается с помощью оператора For Each и лист заполняется полученными данными.