Отправка web-запроса и получение ответа XML (Макрос) Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по VBA » Отправка web-запроса и получение ответа XML (Макрос) Excel (Макросы/Sub)

NaMoRZA

Дата: Среда, 04.11.2015, 12:14 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Здравствуйте, уважаемые форумчане.
Я в VBA не очень (учусь) и с такими задачами еще не сталкивался (в основном работаю с таблицами/листами/ячейками), - руководство поставило задачу сделать макрос проверки параметров сайта в Excel на основании API c сервиса recipdonor.com (пытаюсь сделать такой запрос: Проверка тИЦ).
4-й день уже гуглю и читаю форумы, вроде все более-менее понятно, но у меня никак не получается отправить запрос.. я уже молчу чтобы получить ответ..(( Подскажите пож-та, что я делаю не так. Я думал, логически, ничего сложного не должно быть - интернет есть - разберусь, а ничего не получается..((
[vba]

Код

Sub Zapros()
Set HTTP = CreateObject("MSXML2.XMLHTTP")
apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong=" 'ключ АПИ в Base64 - здесь тестовый
sURL1 = "http//recipdonor.com/api/session/new HTTP/1.1"
sql1 = "<InitSession><Parameters><TaskVariant>Cy</TaskVariant></Parameters><DomainNames><string>mozilla.com</string></DomainNames><Refresh>true</Refresh></InitSession>"
HTTP.Open "GET", sURL1, False
HTTP.SetRequestHeader "Host", "recipdonor.com:977"
HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
HTTP.SetRequestHeader "Authorization", apiBase64
HTTP.SetRequestHeader "Content-Length", Len(sql1)
HTTP.send sql1
otvet = HTTP.responseText
[A1].Value = otvet
End Sub

[/vba]
На 6-й строке ругается Runtime Error -2147012890 (80072 ee6); Automation error.

Буду премного благодарен за помощь.
Спасибо.
Положу еще здесь описание API для даного запроса с сайта recipdonor.com:
Проверка тИЦ, Я.Каталог и Зеркало
Для осуществления проверки тИЦ используются два API метода /session/new и /session/get.

В примере мы проверим на тИЦ сразу несколько сайтов mozilla.com, mozilla.ru, google.com, msdn.microsoft.com. Инициализация проверки осуществляется методом /session/new.

Создадим проверку послав в API запрос вида:

После выполнения запроса мы получим ответ,

что говорит об успешном создании сессии проверки, где
Id - уникальный идентификатор сессии
ExpireAt - сессия будет уничтожена в указанное время
SessionStatus - Статус ToCheck говорит нам о том, что сессия поставлена на проверку

Системе на проверку параметра тИЦ может уйти некоторое время, ответ вы можете получить не сразу.
Для получения результатов проверки необходимо использовать метод /session/get

Формируем запрос:
GET http://recipdonor.com:977/api....fb3886e HTTP/1.1
Authorization: Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong=
Content-Type: text/xml; charset=utf-8
Host: recipdonor.com:977

Получаем ответ вида:

Код

После выполнения запроса мы получим ответ,

NaMoRZA

К сообщению приложен файл: zapros.xls (35.5 Kb)

Сообщение отредактировал NaMoRZA - Среда, 04.11.2015, 12:21

Ответить

СообщениеЗдравствуйте, уважаемые форумчане.
Я в VBA не очень (учусь) и с такими задачами еще не сталкивался (в основном работаю с таблицами/листами/ячейками), - руководство поставило задачу сделать макрос проверки параметров сайта в Excel на основании API c сервиса recipdonor.com (пытаюсь сделать такой запрос: Проверка тИЦ).
4-й день уже гуглю и читаю форумы, вроде все более-менее понятно, но у меня никак не получается отправить запрос.. я уже молчу чтобы получить ответ..(( Подскажите пож-та, что я делаю не так. Я думал, логически, ничего сложного не должно быть - интернет есть - разберусь, а ничего не получается..((
[vba]

Код

После выполнения запроса мы получим ответ,

Автор - NaMoRZA
Дата добавления - 04.11.2015 в 12:14

miver

Дата: Среда, 04.11.2015, 12:43 | Сообщение № 2

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 190

Репутация: 37 ±

Замечаний: 0% ±

Excel 2010

NaMoRZA, Забыли двоеточие в адресе поставить
[vba]

Код

sURL1 = "http://recipdonor.com/api/session/new HTTP/1.1"

[/vba]

Ответить

NaMoRZA

Дата: Среда, 04.11.2015, 13:58 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Уважаемый, miver, Спасибо большое! Уже столько раз все переписывал и столько вариантов перепробовал, что в мелочах начал ошибки делать..(
+ в 6-й строке "HTTP.Open "GET", sURL1, False" надо было поставить True вместо False (т.е., True - создавать Асинхронное соединение).
Теперь макрос отрабатывает, но ответа нет как в описании АПИ.

Может этот ответ надо как-то обрабатывать? А не просто вставлять responseText в ячейку?
Заранее, спасибо за помощь.
Добавил проверку состояния, но результат все равно ноль:
[vba]

Код

Sub Zapros()
Set HTTP = CreateObject("MSXML2.XMLHTTP")
apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong="
sURL1 = "http://recipdonor.com/api/session/new HTTP/1.1"
sql1 = "<InitSession><Parameters><TaskVariant>Cy</TaskVariant></Parameters><DomainNames><string>mozilla.com</string></DomainNames><Refresh>true</Refresh></InitSession>"
HTTP.Open "PUT", sURL1, True
HTTP.SetRequestHeader "Host", "recipdonor.com:977"
HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
HTTP.SetRequestHeader "Authorization", apiBase64
HTTP.SetRequestHeader "Content-Length", Len(sql1)
HTTP.send sql1
Do While HTTP.readyState <> 4
DoEvents
Loop
otvet = HTTP.responseText
[A1].Value = otvet
End Sub

[/vba]

Сообщение отредактировал NaMoRZA - Среда, 04.11.2015, 13:58

Ответить

miver

Дата: Среда, 04.11.2015, 14:42 | Сообщение № 4

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 190

Репутация: 37 ±

Замечаний: 0% ±

Excel 2010

NaMoRZA, Вы дочитали описание до конца ? :)

Цитата NaMoRZA, 04.11.2015 в 13:58, в сообщении № 3

<ApiSessionItemSlimContract>
<Id>f1218977-7a63-43eb-8e5c-a272ffb3886e</Id>
<ExpireAt>2014-12-18T20:24:50.8100568+02:00</ExpireAt>
<Progress>0</Progress>
<SessionStatus>ToCheck</SessionStatus>
</ApiSessionItemSlimContract>

Это авторизация. После этого нужно послать запрос на получение данных

Цитата из описания сайта

GET http://recipdonor.com:977/api....fb3886e HTTP/1.1
Authorization: Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong=
Content-Type: text/xml; charset=utf-8
Host: recipdonor.com:977

Сообщение отредактировал miver - Среда, 04.11.2015, 14:45

Ответить

NaMoRZA

Дата: Среда, 04.11.2015, 16:02 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата miver, 04.11.2015 в 14:42, в сообщении № 4

Вы дочитали описание до конца ? :)

Ну да, если я правильно понял, то после моего запроса - мне должен вернуться такой ответ, :
<ApiSessionItemSlimContract>
<Id>f1218977-7a63-43eb-8e5c-a272ffb3886e</Id> - ID сессии
<ExpireAt>2014-12-18T20:24:50.8100568+02:00</ExpireAt>
<Progress>0</Progress>
<SessionStatus>ToCheck</SessionStatus>
</ApiSessionItemSlimContract>

в котором указан ID сессии, который мне уже надо будет указать в следующем запросе на получение данных.

А уже в запросе на получение данных указываю этот ID сессии (4-я строка, переменная sURL1), по идее - так:
[vba]

Код

Sub OtvetSDannymi()
Set HTTP = CreateObject("MSXML2.XMLHTTP")
apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong="
sURL1 = "http://recipdonor.com:977/api/session/get/f1218977-7a63-43eb-8e5c-a272ffb3886e HTTP/1.1"
HTTP.Open "PUT", sURL1, True
HTTP.SetRequestHeader "Host", "recipdonor.com:977"
HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
HTTP.SetRequestHeader "Authorization", apiBase64
HTTP.send
otvet = HTTP.responseText
[A1].Value = otvet
End Sub

[/vba]

Или я что-то все-таки неправильно понял? Подскажите, пож-та.
Спасибо.

Сообщение отредактировал NaMoRZA - Среда, 04.11.2015, 17:57

Ответить

miver

Дата: Среда, 04.11.2015, 17:59 | Сообщение № 6

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 190

Репутация: 37 ±

Замечаний: 0% ±

Excel 2010

Цитата NaMoRZA, 04.11.2015 в 16:02, в сообщении № 5

А уже в запросе на получение данных указываю этот ID сессии (4-я строка, переменная sURL1)

Все верно
Единственое могу предположить что

Цитата

Чтобы получить права доступа, клиент посылает API ключ пользователя (apikey) и символ двоеточия (":").
f9168c5e-ceb2-4faa-b6bf-329bf39fa1e4:x
Строку необходимо закодировать в base64. Конечный Authorization header имеет вид:
............
API ключ можно получить в профайле по ссылке

То есть для каждого пользователя свой ключ. Который нужно получить отдельно <_<

Ответить

NaMoRZA

Дата: Среда, 04.11.2015, 18:33 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата miver, 04.11.2015 в 17:59, в сообщении № 6

То есть для каждого пользователя свой ключ. Который нужно получить отдельно

Ну да, оригинальный ключ выглядит как "f9168c5e-ceb2-4faa-b6bf-329bf39fa1e4:x", а закодированный в base64 - "RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong=".
В переменной apiBase64 как раз он и указан. Тут в примере, тестовый с документации, а у меня в макросе - мой.
По идее, тестовый должен работать - как минимум - отдавать какой-то ответ.
Я проверял оба варианта - результат один и тот же..(

Ответить

doober

Дата: Среда, 04.11.2015, 19:11 | Сообщение № 8

Группа: Друзья

Ранг: Ветеран

Сообщений: 947

Репутация: 323 ±

Замечаний: 0% ±

Excel 2010

NaMoRZA, здравствуйте.Где присутствует яндекс,используйте ServerXMLHTTP
HTTP/1.1-это лишнее
А так.
[vba]

Код

Sub Zapros()
    Dim HTTP
    Set HTTP = CreateObject("MSXML2.ServerXMLHTTP")
    apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong="
    sURL1 = "http://recipdonor.com/api/session/new"
    sql1 = "<InitSession><Parameters><TaskVariant>Cy</TaskVariant></Parameters><DomainNames><string>mozilla.com</string></DomainNames><Refresh>true</Refresh></InitSession>"
    HTTP.Open "GET", sURL1, False
    HTTP.SetRequestHeader "Host", "recipdonor.com:977"
    HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
    HTTP.SetRequestHeader "Authorization", apiBase64
    HTTP.SetRequestHeader "Content-Length", Len(sql1)
    HTTP.send sql1
    otvet = HTTP.responseText
    [A1].Value = otvet
End Sub

[/vba]

Сообщение отредактировал doober - Среда, 04.11.2015, 19:11

Ответить

NaMoRZA

Дата: Четверг, 05.11.2015, 01:13 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 7

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Еще раз, здравствуйте, уважаемый Doober!
Спасибо большое за помощь!

Попробовал макрос с Вашими правками - Макрос возвращает ответ - HTML-страница ошибки.. - не удивительно, Вы же не можете полностью его проверить без живого ключа((
Пока писал - в который раз прошелся по справке АПИ на сайте recipdonor.com и увидел ссылку на страницу тестирования запросов в АПИ - http://www.recipdonor.com/help/apitest, на которой видно, что в конце строки ссылки надо указывать "?format=xml", т.е., ссылка должна выглядеть так:
"http://recipdonor.com:977/api/session/new?format=xml"
в таком случае, сервер нормально принимает запрос и возвращает ответ в формате XML.
Если же в ссылке оставить в конце " HTTP/1.1", то запрос в формате XML отправляется нормально, но ответ приходит в формате JSON.
Одним словом, получается что некоторые части справки противоречат друг другу - наверно что-то менялось в API, но не везде в описании поправили.
Таким образом - с Вашей помощью, Doober и пользователя Игорь с друго форума, разобрался!
Спасибо Вам Огромное!

Макросы, которые получились в конечном итоге:
1. Отправка запроса в формате XML:
[vba]

Код

Sub Zapros()
Set HTTP = CreateObject("MSXML2.XMLHTTP")
apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong="
sURL1 = "http://recipdonor.com:977/api/session/new?format=xml"
sql1 = "<InitSession><Parameters><TaskVariant>Cy</TaskVariant></Parameters><DomainNames><string>mozilla.com</string></DomainNames><Refresh>true</Refresh></InitSession>"
HTTP.Open "PUT", sURL1, True
HTTP.SetRequestHeader "Host", "recipdonor.com:977"
HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
HTTP.SetRequestHeader "Authorization", apiBase64
HTTP.SetRequestHeader "Content-Length", Len(sql1)
HTTP.send sql1
Do While HTTP.readyState <> 4
DoEvents
Loop
[A1].Value = HTTP.responseText
End Sub

[/vba]
Получаем ответ в формате XML:
[vba]

Код

<ApiSessionItemSlimContract
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Id>38fa4bcd-ee36-4cb5-992b-96068a87f4d6</Id>
<ExpireAt>2015-11-04T21:23:22.3829571+02:00</ExpireAt>
<Progress>0</Progress>
<SessionStatus>AtChecking</SessionStatus>
</ApiSessionItemSlimContract>

[/vba]
С ответа берем Id сессии (38fa4bcd-ee36-4cb5-992b-96068a87f4d6) и используем его в следующем запросе на получение данных:
2. Запрос на получение данных в формате XML:
[vba]

Код

Sub ZaprosEnd()
Set HTTP = CreateObject("MSXML2.XMLHTTP")
apiBase64 = "Basic RjkxNjhDNUUtQ0VCMi00ZmFhLUI2QkYtMzI5QkYzOUZBMUU0Ong="
sURL1 = "http://recipdonor.com:977/api/session/get?id=38fa4bcd-ee36-4cb5-992b-96068a87f4d6&format=xml"
sql1 = "<InitSession><Parameters><TaskVariant>Cy</TaskVariant></Parameters><DomainNames><string>mozilla.com</string></DomainNames><Refresh>true</Refresh></InitSession>"
HTTP.Open "GET", sURL1, True
HTTP.SetRequestHeader "Host", "recipdonor.com:977"
HTTP.SetRequestHeader "Content-type", "text/xml; charset=utf-8"
HTTP.SetRequestHeader "Authorization", apiBase64
HTTP.send
Do While HTTP.readyState <> 4
DoEvents
Loop
[A2].Value = HTTP.responseText
End Sub

[/vba]
И получаем ответ в формате XML :
[vba]

Код

<ApiSessionItemContract
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <Id>38fa4bcd-ee36-4cb5-992b-96068a87f4d6</Id>
   <ExpireAt>2015-11-04T21:23:22.3829571+02:00</ExpireAt>
   <Progress>100</Progress>
   <SessionStatus>Completed</SessionStatus>
   <Domains>
      <DomainData IsValid="true">
         <DomainName>mozilla.com</DomainName>
         <Values>
            <Data>
               <Parameter>Cy</Parameter>
               <Value xsi:type="CyApiData">
                  <Cy>13000</Cy>
                  <Yaca>0</Yaca>
                  <YaBarMirrow>0</YaBarMirrow>
               </Value>
            </Data>
         </Values>
      </DomainData>
   </Domains>
</ApiSessionItemContract>

[/vba]
А тем, кто хочет получать данные в формате JSON надо совсем чуть-чуть поменять в запросах:
в обоих запросах в конец ссылки добавить " HTTP/1.1", например, в первом запросе строка со ссылкой должна быть такой:
sURL1 = "http://recipdonor.com:977/api/session/new?format=xml HTTP/1.1"

Вроде бы все расписал - вдруг кому пригодится.
Остался один вопрос - может подскажете готовое решение или ткнете носом куда-то:
Как быстро разбирать эти полученные ответы в формате XML?
Заранее, спасибо.

Код

[/vba]
Получаем ответ в формате XML:
[vba]

Код

[/vba]
И получаем ответ в формате XML :
[vba]

Код

Ответить

СообщениеЕще раз, здравствуйте, уважаемый Doober!
Спасибо большое за помощь!

Попробовал макрос с Вашими правками - Макрос возвращает ответ - HTML-страница ошибки.. - не удивительно, Вы же не можете полностью его проверить без живого ключа((
Пока писал - в который раз прошелся по справке АПИ на сайте recipdonor.com и увидел ссылку на страницу тестирования запросов в АПИ - http://www.recipdonor.com/help/apitest, на которой видно, что в конце строки ссылки надо указывать "?format=xml", т.е., ссылка должна выглядеть так:
"http://recipdonor.com:977/api/session/new?format=xml"
в таком случае, сервер нормально принимает запрос и возвращает ответ в формате XML.
Если же в ссылке оставить в конце " HTTP/1.1", то запрос в формате XML отправляется нормально, но ответ приходит в формате JSON.
Одним словом, получается что некоторые части справки противоречат друг другу - наверно что-то менялось в API, но не везде в описании поправили.
Таким образом - с Вашей помощью, Doober и пользователя Игорь с друго форума, разобрался!
Спасибо Вам Огромное!

Макросы, которые получились в конечном итоге:
1. Отправка запроса в формате XML:
[vba]

Код