Excel vba json запрос

Обновлено: 06.07.2024

У меня такая же проблема, как в Excel VBA: Parsed JSON Object Loop, но не может найти никакого решения. У моего JSON есть вложенные объекты, поэтому предлагаемое решение вроде VBJSON и vba-json не работает для меня. Я также исправил один из них для правильной работы, но результатом было переполнение стека вызовов из-за многих рекурсий функции doProcess.

Проблема на этом этапе заключается в том, что я не могу определить, какова будет структура объектов, поэтому я заранее не знаю ключи, которые будут находиться в каждом родовом объекте. Мне нужно пропустить общий объект VBA для получения ключей/свойств.

Если моя функция разбора javascript может вызвать функцию или подпрограмму VBA, это будет отлично.

Я помню ваш предыдущий вопрос, поэтому интересно снова его увидеть. У меня есть один вопрос: допустим, вы преуспели в анализе вашего JSON в VBA - как бы вы тогда использовали этот «объект» в VBA? Обратите внимание, что структура JSON может быть любого типа, так как бы вы могли ориентироваться в конечном результате в VBA? Моей первой мыслью может быть создание JScript, который будет анализировать JSON (используя eval или даже одну из «лучших» существующих библиотек) и затем выполнять итерацию по структуре, чтобы создать вложенный объект на основе словаря сценариев для передачи обратно в VBA. Что вы делаете с вашим разобранным JSON? Я создам лист для каждого объекта и добавлю записи в каждую строку, создав столбец, если он еще не существует (добавляя в строку 1). Ваш предложенный asp-xtreme-evoluton кажется интересным. Был в процессе создания чего-то очень похожего. Мне предоставили исправленный и почти работающий (я исправил небольшую «проблему») класса vba-json. Мы будем использовать это на данный момент. Рабочую версию vba-json предоставил Рэндир, автор соответствующего вопроса. @tim, мой предыдущий комментарий может не ответить на ваш вопрос должным образом. Я знаю, что структура в основном представляет собой список таблиц с записями. Таким образом, у меня есть объект (ключ: значение), представляющий таблицы. «Ключ» - это имя таблицы, а значение - массив [] записей, которые являются объектами (ключ: значение). Я не знаю точно, какая таблица была предоставлена и какие столбцы (поля) доступны. Для людей, которые не могут обойтись без строгой структуры, это дикое общее программирование :-) Конечно, никому не в обиду. Проще следовать, если структуры похожи, но «ключи» разные. Из интереса откуда берутся данные?

Если вы хотите построить поверх ScriptControl , вы можете добавить несколько вспомогательных методов, чтобы получить необходимую информацию. Объект JScriptTypeInfo немного неудачен: он содержит всю необходимую информацию (как вы можете видеть в окне "Смотреть" ), но, похоже, это невозможно с VBA. Однако механизм Javascript может помочь нам:

Если экземпляр JScriptTypeInfo ссылается на объект Javascript, For Each . Next не будет работать. Однако он работает, если он ссылается на массив Javascript (см. Функцию GetKeys ).
Свойства доступа, имя которых известно только во время выполнения, используют функции GetProperty и GetObjectProperty .
Массив Javascript предоставляет свойства length , 0 , Item 0 , 1 , Item 1 и т.д. С нотной точкой VBA ( jsonObject.property ) доступно только свойство length, и только если вы объявляете переменную с именем length со всеми строчными буквами. В противном случае случай не соответствует, и он не найдет его. Другие свойства недействительны в VBA. Поэтому лучше используйте функцию GetProperty .
В коде используется раннее связывание. Поэтому вам нужно добавить ссылку на "Microsoft Script Control 1.0".
Вам нужно позвонить InitScriptEngine один раз, прежде чем использовать другие функции, чтобы выполнить некоторую базовую инициализацию.

Этот ответ кажется тем, что я хочу, но я получаю object variable not set при попытке DecodeJsonString функцию DecodeJsonString . Есть ли какие-либо другие ссылки, которые мне нужны, кроме Microsoft Script Control? Это происходит сразу после строки Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")") . JsonString - это просто объект json. Я пробовал с различными объектами Json и получаю ту же ошибку. Лучший ответ. Я только что закончил POC о том, как вызвать службу JSON Restful, проанализировать полученный json на основе вашего ответа и затем отобразить его в Excel. Это было очень хорошо получено нашими клиентами. Большое спасибо . +1 за это .. Я использую ваш код, но при возникновении проблем возвращается часть строки JSON, в которой нет подключей. Я получаю исключенное значение команды unescaped для всей "таблицы". Есть идеи? JSON возвращает: <"id": "primary_site", "gorithm ":" cs "," version ":" 02.05.50 "," name ":" Primary Site "," title ":" Primary Site "," last_modified ":" 2015-05-27T16: 19: 40.613Z "," definition ": [<" key ":" site "," name ":" Primary Site "," type ":" INPUT ">, ]," lines ": [[" C000 "," Внешняя верхняя губа "], [" C001 "," Внешняя нижняя губа " "], [" C002 "," Внешняя губа, NOS "] и т. Д. Проблема заключается в" строках " В эти дни я бы порекомендовал JsonBag . Только один класс, поставляется с документацией, и он очень прост в использовании. Я получил ваше решение для работы с VBScript , удалив типы и инициализировав, используя следующее: Set se = CreateObject("MSScriptControl.ScriptControl") . +1 Спасибо!

Вот еще один метод для анализа JSON в VBA, основанный на ScriptControl ActiveX, без внешних библиотек:

ОБНОВИТЬ

Пытаясь избежать этого, я создал JSON-парсер на основе RegEx. Объекты <> представлены словарей, что делает возможным использование словаря свойства и методы: .Count , .Exists() , .Item() , .Items , .Keys . Массивы [] - это обычные VB-массивы на основе нуля, поэтому UBound() показывает количество элементов. Вот код с некоторыми примерами использования:

Проверьте последнюю версию VBA-JSON-parser на GitHub (импортируйте модуль JSON.bas в проект VBA для обработки JSON).

Еще одна возможность этого анализатора JSON RegEx заключается в том, что он работает в 64-разрядной версии Office, где ScriptControl недоступен.

UPDATE2

Однако, если вы хотите проанализировать JSON в 64-битном Office с помощью ScriptControl , этот ответ может помочь вам заставить его работать.

Excel таблицы могут быть представлены в виде массива объектов в виде JSON. Каждый объект представляет строку в таблице. Это помогает извлекать данные из Excel в согласованном формате, который виден пользователю. Затем данные могут быть переданы другим системам Power Automate потоками.

Данные таблицы ввода

Вариант этого примера также включает гиперссылки в одном из столбцов таблицы. Это позволяет всплыть в JSON дополнительные уровни данных ячейки.

Данные таблицы ввода, включаемой гиперссылки

Диалоговое окно для редактирования гиперссылки

Пример Excel файла

Скачайте файлtable-data-with-hyperlinks.xlsx для готовой к использованию книги. Добавьте следующий скрипт, чтобы попробовать пример самостоятельно!

Пример кода: данные таблицы возврата в качестве JSON

Вы можете изменить interface TableData структуру, чтобы соответствовать столбцам таблицы. Обратите внимание, что для имен столбцов с пробелами обязательно поместите ключ в кавычках, например в "Event ID" примере.

Пример вывода из таблицы "PlainTable"

Пример кода. Возвращаем данные таблицы как JSON с текстом гиперссылки

Сценарий всегда извлекает гиперссылки из 4-го столбца (индекс 0) таблицы. Вы можете изменить этот порядок или включить несколько столбцов в качестве данных гиперссылки, изменяя код под комментарием // For the 4th column (0 index), extract the hyperlink and use that instead of text.

Пример вывода из таблицы "WithHyperLink"

Использование в Power Automate

О том, как использовать такой сценарий в Power Automate, см. в Power Automate.

Цель книги – предоставить инструменты Excel для автоматизации повторяющихся задач извлечения данных из Интернета. Автор предлагает несколько десятков готового кода VBA и описывает приемы работы в Power Query.

Eduardo Sanchez. Excel and The World Wide Web. Straight to the Point. – Holy Macro! Books, 2021. – 58 p.

Глава 1. Приступая к работе

Что такое HTML?

HTML (Hyper Text Markup Language) – язык гипертекстовой разметки. Он используется для создания веб-сайтов. Гипертекст – это контент, который ведет себя нелинейным образом. Представьте себе веб-сайт, на каждой странице которого есть несколько ссылок на другие страницы, как того же самого сайта, так и других сайтов. Пользователь перемещается, переходя с одной страницы на другую; это гипертекстовое поведение. Обычная печатная книга – это контрпример, ее предполагается читать последовательно.

Информация в HTML помечена тегами; ниже мы поговорим об этом подробнее. Существуют и другие языки, используемые для создания веб-сайтов, такие как CSS и JavaScript, но мы не будем подробно рассматривать их здесь.

CSS (Cascading Style Sheet) – каскадная таблица стилей, язык описания внешнего вида документа. Он работает вместе с HTML, который отвечать за содержимое страницы. Каскадирование означает, что можно использовать несколько CSS-файлов для создания окончательного визуального стиля. Этот язык управляет такими элементами, как размер шрифта, фоновые изображения и цветовая палитра.

JavaScript – язык программирования для реализации динамического поведения на веб-сайтах. С его помощью разработчики могут манипулировать содержимым страницы, создавать диаграммы и взаимодействовать с API (Application Programming Interface, интерфейс прикладного программирования). Обратите внимание, что JavaScript и Java – это два разных языка. Говорят, что в будущем JavaScript может заменить VBA в качестве языка программирования Office.

Одна из замечательных особенностей современных браузеров заключается в том, что они предоставляют исходный код страниц. Если вы используете Google Chrome, просто щелкните правой кнопкой мыши любой элемент страницы и выберите пункт Просмотреть код; в правой части окна появится панель, аналогичная показанной ниже:

Рис. 1. Фрагмент кода HTML веб-страницы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Ключевые слова header, div, aside, nav, h4, li и другие являются тегами.
Когда маленькие черные треугольники указывают вправо, это означает, что их можно щелкнуть, чтобы развернуть и отобразить дополнительную информацию.
Ключевые слова id, class и href являются атрибутами.
<li – это открывающий тег, а </li> – закрывающий.
Элемент может принадлежать к нескольким различным классам.

Если вы чувствуете себя подавленным всем этим, не паникуйте; не обязательно быть программистом HTML, чтобы работать с такого рода автоматизацией. Позже мы увидим, как читать код HTML и извлекать информацию с помощью VBA.

Хотя веб-дизайнеры используют профессиональные редакторы, можно создавать HTML-файлы в стандартных приложениях Windows, например, в Блокноте. Это особенно быть полезно, когда вы хотите протестировать код VBA, который будет взаимодействовать с веб-страницей, но по какой-то причине реальный сайт недоступен.

В этом посте я покажу, как с помощью VBA сделать, то, для чего VBA вроде бы как изначально не предназначен – как получить значения нужных переменных из структуры JSON.

Поэтому создаваемому на VBA паучку придется постараться, чтобы понять разметку «для людей».

Для работы паука необходимо дополнительно подключить три библиотеки:

Microsoft XML parser (MSXML) – тот же, что использовался для получения курсов ЦБ с сайта Банка России.
Библиотеку для работы с объектной моделью HTML.
Библиотеку для использования возможностей JavaScript из VBA.

В результате выполнения нижепредставленного кода в переменной strJson должна оказаться структура с данными в JSON формате.

Теперь самое интересное – как распарсить эту JSON структуру? Чистый VBA это делать не умеет. Но с JSON прекрасно работает JavaScript.

А в VBA есть инструмент для использования возможностей JavaScript для пользователей MS Excel.

Мы можем в VBA получить уже распарсенную JSON переменную:

Проблема в том, что с объектом objJSON ничего нельзя сделать в рамках VBA – у него нет ни свойств, ни методов. Поэтому создаем эти методы на языке JavaScript. Нам нужно вытащить даты (xAxis) и количество (series->data):

Вот что пишем в VBA редакторе:

Загоняем данные в привычные VBA массивы:

Ну и раскатываем эти массивы по рабочему листу:

Вот, что получилось в результате на листе рабочей книги:

По этим данным легко построить график, например, такой:

Excel файл с кодом можно скачать здесь. Если будут вопросы – пишите их сюда.

Читайте также: