Vba excel http запросы
Система проектировалась только для IE версии не выше 8, для работы требует установленных библиотек MSXML4.0.
Может это важно.
Задача такова: создать клиентскую часть на базе формы access, чтобы из нее делать запросы и получать данные в заранее созданную таблицу.
Перерыл форум, но вот как обычно бывает - нет четкого ответа. Не получается построить целостную картину вопроса, прочитав кучу статей и примеров.
Начал с того, что установил HHTPAnalyzer, дабы понять, что браузер делает с ресурсом. Картина такова, что на сервере действует механизм PRG (POST-REDIRECT-GET):
1. происходит авторизация методом POST.
2. сервер выдает referer и куки на новый ресурс и переводит туда пользователя.
3. пользователь формирует условия отбора в появившейся веб форме и нажимает "сформировать отчет". Появляется табличка со статистическими данными.
4. я не знаток веб-серверных технологий, там информация выдается с исходным кодом такого вида (это фрагмент):
Т.е. явно это поддается парсингу. Возможно, тут даже голову ломать не придется и это сформировано по стандарту XML (не придется делать текстовый парсинг "в лоб").
Собственно, вопросы по существу:
1. Как реализовать PRG в vba? Т.е. есть конструкция:
Как ее "допилить", чтобы после нее на основе полученных куков (или кук, тут по ходу все непросто) и рефера сделать редирект?
2. Как формируется переменная "str" в вышеприведенном коде? Она вообще там нужна? На этом шаге мы сообщаем серверу только url/логин/пароль.
3. Как в коде vba передать серверу условия отбора после редиректа? Я так понял, нужно применять метод
Как узнать, какие вообще там условия можно передать и как они называются? Смотреть лог сниффера?
4. Как результат запихнуть в таблицу access? Ну т.е. что сформировать recordset - это понятно. Но как выдернуть именно тело с данными из полученного массива?
5. И последнее. Понятно, что есть GET и POST директивы. Понятно, что POST - эти типа ГЕТ, но "ширше" по возможностям.
Правильно ли я понимаю, что без GET можно вообще обойтись? Вопрос не в общем, а только применительно к моей задачке.
3. Если передача условий идёт по GET (а вы говорите, что так) то просто.
Покажите какой URL после этих действий
| DarkSlavyanin |
|---|
| пользователь формирует условия отбора в появившейся веб форме и нажимает "сформировать отчет". Появляется табличка со статистическими данными. |
Но вменяемых данных там не было (ну т.е. типа XML-узлов таблицы тех самых статистических данных). Был какой-то фрагмент, начинался с узла/тега типа
Сдается мне, высылаются данные первой формы авторизации (которая предваряет основную форму).
Дальше убейте - не помню. Завтра скриншоты по всем вопросам запостю.ее
А что с PRG? Можно как-то "схватить" куки/реферы, чтобы использовать их как ключик ко второй (основной) веб форме (в которой все фильтры и настраиваются)?
Работая в IoT-сфере и плотно взаимодействуя с одним из основных элементов данной концепции технологий – сетевым сервером, столкнулся вот с какой проблемой (задачей): необходимо отправлять много запросов для работы с умными устройствами на сетевой сервер. На сервере был реализован REST API с оболочкой Swagger UI, где из графической оболочки можно было отправлять только разовые запросы. Анализ сторонних клиентов, типа Postman или Insomnia показал, что простого визуального способа поместить в скрипт массив из необходимого перечня идентификаторов устройств (или любых других элементов сервера), для обращения к ним – не нашлось.
Так как большая часть работы с выгрузками и данными была в Excel, то решено было вспомнить навыки, полученные на учебе в университете, и написать скрипт на VBA, который бы мою задачку решал.
получать информацию по устройствам с различными параметрами фильтрации (GET);
применять изменения в конфигурации по устройствам: имя, профиль устройства, сетевые лицензии и пр. (PUT);
отправлять данные для конфигурации и взаимодействия с устройствами (POST).
И сегодня я расскажу вам про то, как с помощью Excel, пары формул и самописных функций на VBA можно реализовать алгоритм, отправляющий любое необходимое количество REST-API запросов с использованием авторизации Bearer Token.
Данная статья будет полезная тем, кто воспользуется данным решением под Windows, но еще больше она будет полезна тем людям, которые хотят использовать данное решение на MacOS (с Excel x64). Как вы уже догадались, ниже будут рассмотрены два варианта реализации под разные системы, так как с MacOS есть нюанс.
Часть 1. Реализация решения под Windows
GET
Начнем с самого простого: GET – запросов. В данном примере необходимо получить ответ (информацию) от сервера по заданному списку устройств.
Для реализации GET – запросов нам дано:
1) Ссылка, в которой указываются параметры запроса.
2) Заголовки запроса + Токен авторизации (Bearer Token)
--header 'Accept: application/json' --header 'Authorization: Bearer
3) Параметр, указываемый в ссылке (в данном примере это идентификаторы устройств – DevEUI):
Имея такие данные на входе, делаем в Excel лист-шаблон, который заполняем в соответствии с тем, что имеем:
столбец А уходит вот значения параметров
столбец F уходит под ссылку-родителя
столбец H уходит под заголовки, где в ячейке H1 единоразово для текущего листа указывается токен:
=СЦЕП("--header 'Accept: application/json' --header 'Authorization: Bearer ";$H$1;"'")
столбец I уходит под URL (ссылки-дети, на основе ссылки-родителя)
столбец J уходит под результат (ответ от сервера)
Далее, нам необходимо реализовать подпрограмму(макрос) отправки GET-запросов. Состоит она из четырех частей:
цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.
временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания
таймером, который показывает время выполнения всего макроса после завершения
Привязываем подпрограмму к кнопкам для удобства и выполним скрипт. Получается:
Таким образом, скрипт проходит по столбцу I, забирая из значения каждой ячейки URL, для тех строк, где в столбце А есть значения (которые и подставляются в URL). Для удобства также сделаны кнопки очистки полей и подсветка запросов условным форматированием, в случае успешного ответа на запрос.
PUT
Чуть-чуть усложним задачу и перейдем к PUT-запросам. В данном примере необходимо изменить профиль устройства, чтобы сервер по-другому с ним взаимодействовал.
К исходным данным для GET – запроса добавляется тело запроса с ключем-значением (п4). Итого дано:
1) Ссылка, в которой указываются параметры запроса.
2) Заголовки запроса + Токен авторизации (Bearer Token)
--header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Bearer
3) Параметр, указываемый в ссылке (в данном примере это внутренние идентификаторы устройств – hRef):
4) Тело запроса, с ключом и значением:
Немного дополняем новый PUT-лист в Excel по сравнению с GET (остальное без изменений):
новый столбец B теперь отвечает за ключ deviceProfileId (ячейка B1), а все значения ниже за его возможные значения)
Немного поменяем макрос и вынесем его в отдельную подпрограмму:
Привяжем макрос к кнопке и выполним.
Логика абсолютно аналогична GET запросу.
POST
Для POST запросов все аналогично PUT. Только немного меняется код в части типа запроса. В данном примере на устройство отправляется команда-конфигурация с указанием тела посылки (payload_hex) и порта (fport) для конкретного устройства.
Получившаяся таблица выглядит следующим образом:
На этом часть для Windows заканчивается. Здесь все оказалось довольно просто: стандартная библиотека, простенький алгоритм перебора значений в цикле.
Часть 2. Реализация решения под MacOS и Excel 64-bit
Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 10.14 и Excel 16.51. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:
Была отлажена функция вызова ExecShell:
И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):
В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS c использованием Excel 64-bit.
На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.
На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)
Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.
Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен:
Сегодня расскажу Вам как работать с интернетом через VBA Excel.
Давайте перейдем в редактор кода Excel, для этого нажмите сочетание горячих клавиш Alt + F11 или через вкладку «просмотр кода». В открывшемся окне редактора через вкладку insert -> procedure добавим простую процедуру “Test” и нажмем ок.
Теперь нужно подключить специальный внешний компонент т.к. excel по умолчанию не умеет работать с удаленными серверами. Сам компонент представляет из себя файл библиотеку с расширением .dll (dynamic link library).
Чтобы добавить библиотеку нажмите на вкладку Tools -> References
В открывшемся окне найдите библиотеку с названием (Microsoft XML, v6.0). При установке windows данная библиотека устанавливается на Ваш компьютер автоматически. Проставьте галочку и нажмите ок.
Давайте просто получим разметку странички google. Пока не очень полезно, но для примера сойдет.
В коде напишем следующий текст.
Если пример вернут ошибку 70, то вставьте другой адрес. Ошибка может быть связанна с механизмом безопасности google, потому просто проигнорируйте её и добавьте любой другой адрес, любимого сайта.
Запустим нажав на иконку run или клавишу F5
Как мы видим в окне immediate мы получили сырую разметку гугла.
Давайте откроем браузер. В области открытой страницы браузера нажмите правую кнопку мышки и выберете «Посмотреть код страницы»
В открывшейся вкладке мы увидим то, что получили через нашу среду разработки в Excel. Можете сравнить разметку, чтобы убедится в этом, но я не рекомендую терять время.
Теперь давайте пробежимся по строчкам нашего кода.
Сначала мы объявляем переменную xhr и стразу инициализируем её через ключевое слово new. Если не совсем понятно, как работает данная строчка, то посмотрите уроки по объектам в Excel ссылка в описание.
Далее мы обязательно должны указать основные параметры через метод Open
Второй параметр – это адрес веб ресурса, тут всё ясно.
Третий параметр – это указатель на… хотя просто запомните, что сюда ставится false))
Потом мы просто вызываем удаленный сервер методом send
Наша программа на некоторое время зависает т.к. ожидает ответа от сервера, возможно вы не почувствуете этого. Но если Ваш интернет слабый Вы можете долго ожидать ответа до тех пор, пока не вылети таймаут.
Свойство responseText содержит все данные которые вернул сервер, обычно это текстовый формат.
Необходимо добавить, что если всё хорошо и удаленный сервер работает верно, то сервер вместе с текстом или телом ответа, возвращает код ответа.
Однако бывает такое, что удаленный ресурс отработал неверно. Скорее всего сервер в таком случае вернет ошибку с кодом ответа начинающуюся с цифры 400, например, знаменитую ошибку 404 (страница не найдена) или 500 (ошибка сервера).
Чтобы проверить, что сервер отработал верно и вернул нам то, что надо проверим код ошибки.
Изменим на следующий блок кода.
В комментариях я указал более надежный способ проверки положительного ответа от сервера всё что выше 200, но ниже 300 считается ок.
Очистите окно Immediate
Нажмите F5 или иконку старт.
Мы видим, что всё тоже самое ответ пришел верный.
Нажмите F5 или иконку старт.
В окне Immediate уже пришла страничка с ошибкой.
Таким образом можно обращается к серверу через VBA Excel
Я рекомендую нажать клавишу F8 чтобы по шагам прогрессировать программу и посмотреть каждый шаг.
Функция GetQueryRange предназначена для автоматизации загрузки данных с веб-страниц.
Например, нам надо из макроса Excel получить данные с нескольких однотипных страниц сайта.
Самый простой способ достичь этого - выполнять почти идентичные веб-запросы (где незначительно отличаться будет только URL страницы),
каждый раз анализируя данные, загруженные веб-запросом на лист Excel
Поскольку количество обращений ко мне, с просьбами сделать программу загрузки данных из интернета, с каждым днём растёт, я решил сделать для этих целей универсальную функцию:
При запуске функции, создаётся скрытый лист с именем «tmpWQ» (если такой лист уже есть - используется существующий), и на этом листе выполняется веб-запрос к указанному сайту.
Комментарии
скопировал пример из статьи, чтобы посмотреть как это работает - никак.
GetQueryRange - sub or function not defined
Во 2-м примере функция GetQueryRange
в строке
.Refresh BackgroundQuery:=False
выдает ошибку
"Указаный сервер не может выполнить требуемую операцию"
Почему же не работают?
Макрос, опубликованный в этой статье, работает сам по себе, без каких-либо дополнительных прог.
Достаточно создать новый файл Excel, вставить туда ВЕСЬ код из статьи, — и всё заработает.
Вы прекрасно "разнесли" возможности по всему рунету. Но макросы без Вашей проги не работают. Изумительно! Писать код для юзеров, которые не работают без без проги! И стелить их по всему интернету.
Мне нравится Ваш подход к SEO.
Замечательная функция.
Только почему-то станица вида https://. не загружается в "tmpWQ."
Ну так выгрузите сначала одну таблицу, а потом другую.
Не имея никаких данных (что откуда выгружать, что должно получиться, что вы там написали в макросе), - не могу посоветовать ничего конкретного.
Если нужно готовое решение, - можем сделать под заказ.
Здравствуйте. Спасибо очень полезная функция для работы. Но возник вопрос, а как можно одновременно 2 опциональных таблицы выгрузить друг под другом, чтобы не выгружать лишнего? Пробовал сам скорректировать код, но у не получилось (( Заранее спасибо.
Добрый день. Авторизоваться получилось (точнее отправляется запрос на получение необходимых данных), а вот сохранить данные со страницы не получается. Сохраняется стартовая страница а не та, которая выводится после запроса. Подскажите как решить данный вопрос. Заранее спс.
Вам что не нравится? что я предусмотрительно не сделал макрос, идеально подходящий для вашей задачи?
Не нравятся мои макросы, вопросы возникают, - ну так берите макросы с других сайтов.
А те, кто обращается с платными заказами, - всегда получают решение, не требующее никаких доработок.
На этом сайте я публикую макросы прежде всего для себя (это для меня как записная книжка - выкладываю свои наработки, чтобы не потерялись)
PS: Пожалуйста, не посещайте больше мой сайт, раз мои бесплатные макросы вас не устраивают, и не пишите здесь коммменты.
Если тупо вставлять ссылку в процедуру, — конечно, ничего не получится
(да и вообще, «тупо вставлять» ни к чему хорошему не приводит. )
Вы хотите бесплатный универсальный макрос, чтобы работал с любыми исходными данными, и выводил результат в точности как вам надо?
Не бывает такого.
Код должен быть рабочим и универсальным
Да? Ну так напишите такой код на досуге
заголовки столбцов - сплошные кракозябры
Пожалуйтесь разработчику Excel (в Microsoft) - это они так сделали веб-запрос, что кодировку не указать.
У меня на сайте много макросов, - если умело их применять, то всегда можно получить желаемый результат.
Проблема точно не в коде. Мне маленько не ясно где должны появляться скачанные данные, допустим в первом столбце адрес вэб страницы, во втором я включаю макрос, в searchlinks делаю ссылку на первый столбик, в tables ничего не пишу, дабы скачалось полностью страница, жму окей, вижу процесс пошел, но данных нигде нет! Вот, где искать скачанные данные я не понял!
Кажется я понял, у меня не получалось потому что я запускал это как функцию, а теперь я запустил это все как макрос с поддержкой макроса, и все данные стали выводиться в книге с поддержкой макроса, хотя сама формула и адреса в другой книге! Вот. как то так!
Максим, а чем я смогу помочь, зная только то, что вы написали?
Если первый раз все сделали, и всё получилось, - значит, проблема не в моём коде.
А почему у вас не получается, - сказать не могу, т.к. не знаю, что и каким кодом вы скачиваете.
Не можете разобраться, - оформите заказ на сайте, я сам всё сделаю, - и всё всегда будет работать как вам надо.
Здравствуйте!
Я не могу разобраться, первый раз у меня получилось, все вэб данные появились на листе, хотя я на этот лист даже не нажимал и ничего с ним не делал. А теперь у меня ничего не получается, я вижу что он скачал, скопировал и вставил в лист, но скачанные страницы я не вижу? И как их вывести на видное место, то же не понимаю!
Руслан, моя функция дает вам только ссылку на диапазон
Вы ведь потом этот диапазон куда-то копируете своим кодом, в единую таблицу, верно?
Так вот копируйте на один столбец правее, а в первый столбец вставляйте ссылку
(вы же эти действия выполняете в цикле из своего макроса - так что все просто)
PS: Могу продемонстрировать на примере, - если вы покажете свой код.
Добрый день, а не подскажите, как сюда добавить еще и ссылку откуда скопировалась таблица?
Суть: Использовал данную функцию, добавил цикл с обработкой массива ссылок лежащих на отдельной странице.Результат представлен в виде идущих подряд таблиц, которые нужно подписывать вручную. Можно как то автоматизировать?
Здраствуйте.
Очень полезная фукция для меня - большое Вам спасибо.
Подскажите пожалуста, можна ли как-то получить после запроса URL страници которая загрузилась?
Здравствуйте, Михаил.
Встроенные средства Excel (веб-запрос) не поддерживают авторизацию.
Надо использовать более сложные макросы - сначала, например, авторизоваться на сервере, а потом работать уже с веб-страницей (анализируя текст веб-страницы, или её исходный код)
Если самостоятельно не разберетесь - оформляйте заказ, сделаем.
Здравствуйте, Сергей.
К сожалению, с видеоуроками помочь не смогу - на это потребуется много времени (которого у меня нет).
К тому же, есть куча сайтов, где выложены видеоуроки по работе с макросами в Excel.
Делать же аналогичное видео для каждой из опубликованных у меня на сайте функций - считаю необоснованной тратой времени.
А можете сделать видео как им пользоваться.
Штука нужная, но как использовать VAB макросы я не знаю.
На 6-й строке ругается Runtime Error -2147012890 (80072 ee6); Automation error.
Буду премного благодарен за помощь.
Спасибо.
Отправка запроса на сервер - получение ответа в xml и обработка
Всем добрый день. Искал по всем форумам и всем блогам, но нужного ответа не нашел и не добился.
Отправка запроса на сервер - получение ответа в xml и обработка
Пожалуйста помогите в решении проболемы. Извините если создаю дубль тем, ну очень нужно найти.
Проверка тИЦ, Я.Каталог и Зеркало
Для осуществления проверки тИЦ используются два API метода /session/new и /session/get.
Создадим проверку послав в API запрос вида
После выполнения запроса мы получим ответ,
что говорит об успешном создании сессии проверки
Id - уникальный идентификатор сессии
ExpireAt - сессия будет уничтожена в указанное время
SessionStatus - Статус ToCheck говорит нам о том, что сессия поставлена на проверку
Системе на проверку параметра тИЦ может уйти некоторое время, ответ вы можете получить не сразу.
Для получения результатов проверки необходимо использовать метод /session/get
Получаем ответ вида:
Я в основном работаю с таблицами/листами/ячейками - думал, вродь, логически, ничего сложного - интернет есть - разберусь, а ничего не получается..(( то ли руки кривые, то ли - мозги не соображают..
Макросы, которые получились в конечном итоге:
1. Отправка запроса в формате XML:
2. Запрос на получение данных в формате XML:
Вроде бы все расписал - вдруг кому пригодится.
Остался один вопрос - может подскажете готовое решение или ткнете носом куда-то:
Как быстро разбирать эти полученные ответы в формате XML?
Заранее, спасибо.
Пример XML прикладываю.
Читайте также: