РАБОТА С ИНТЕРНЕТОМ ЧЕРЕЗ EXCEL VBA

Сегодня расскажу Вам как работать с интернетом через VBA Excel.

Давайте перейдем в редактор кода Excel, для этого нажмите сочетание горячих клавиш Alt + F11 или через вкладку «просмотр кода». В открывшемся окне редактора через вкладку insert -> procedure добавим простую процедуру “Test” и нажмем ок.

Теперь нужно подключить специальный внешний компонент т.к. excel по умолчанию не умеет работать с удаленными серверами. Сам компонент представляет из себя файл библиотеку с расширением .dll (dynamic link library).

Чтобы добавить библиотеку нажмите на вкладку Tools -> References

В открывшемся окне найдите библиотеку с названием (Microsoft XML, v6.0). При установке windows данная библиотека устанавливается на Ваш компьютер автоматически. Проставьте галочку и нажмите ок.

Теперь внутри любой процедуры нам будет доступен класс XMLHTTP60. Внутри нашей процедуры напишем следующее выражение.

Public Sub Test()

    Dim xhr As New XMLHTTP60

End Sub

Вообще данный класс XMLHTTP60 в большинстве случаев известен как XmlHttpRequest, используется сегодня в разработке современных веб приложений, которые модно называют web 2.0 или Ajax. Однако сам класс довольно древний, впервые был разработан как не странно компанией Microsoft и работал уже в Internet Explorer 5. В те времена основным форматом обмена дынными был XML, поэтому в название класса присутствует xml хотя ничего общего c xml на сегодняшний день не имеет.

Давайте просто получим разметку странички google. Пока не очень полезно, но для примера сойдет.

В коде напишем следующий текст.

Public Sub Test()

    Dim xhr As New XMLHTTP60
    xhr.Open "GET", "https://google.com", False
    xhr.send
    
    Debug.Print xhr.responseText

End Sub

Если пример вернут ошибку 70, то вставьте другой адрес. Ошибка может быть связанна с механизмом безопасности google, потому просто проигнорируйте её и добавьте любой другой адрес, любимого сайта.

Запустим нажав на иконку run или клавишу F5

Как мы видим в окне immediate мы получили сырую разметку гугла.

Давайте откроем браузер. В области открытой страницы браузера нажмите правую кнопку мышки и выберете «Посмотреть код страницы»

В открывшейся вкладке мы увидим то, что получили через нашу среду разработки в Excel. Можете сравнить разметку, чтобы убедится в этом, но я не рекомендую терять время.

Теперь давайте пробежимся по строчкам нашего кода.

Сначала мы объявляем переменную xhr и стразу инициализируем её через ключевое слово new. Если не совсем понятно, как работает данная строчка, то посмотрите уроки по объектам в Excel ссылка в описание.

Далее мы обязательно должны указать основные параметры через метод Open

Первый параметр — это глагол запросов протокола http. Официально их не много около 8 штук, могут быть и пользовательские. Но сейчас достаточно запомнить два часто употребляемых глагола GET и POST (более подробно сюда)

Второй параметр – это адрес веб ресурса, тут всё ясно.

Третий параметр – это указатель на… хотя просто запомните, что сюда ставится false))

Потом мы просто вызываем удаленный сервер методом send

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

Свойство responseText содержит все данные которые вернул сервер, обычно это текстовый формат.

Необходимо добавить, что если всё хорошо и удаленный сервер работает верно, то сервер вместе с текстом или телом ответа, возвращает код ответа.

Если всё хорошо, то код ответа начинается с цифры 200 (201, 202, 203, 204, 205, 206) (код ответов можно посмотреть тут (http://lib.ru/WEBMASTER/rfc2068/))

Однако бывает такое, что удаленный ресурс отработал неверно. Скорее всего сервер в таком случае вернет ошибку с кодом ответа начинающуюся с цифры 400, например, знаменитую ошибку 404 (страница не найдена) или 500 (ошибка сервера).

Чтобы проверить, что сервер отработал верно и вернул нам то, что надо проверим код ошибки.

Изменим на следующий блок кода.

Public Sub Test()

    Dim xhr As New XMLHTTP60

    xhr.Open "GET", "https://google.com", False
    xhr.send
    
    If xhr.Status = 200 Then 'If xhr.Status >= 200 And xhr.Status < 300 Then
        Debug.Print xhr.responseText
    Else
        Debug.Print xhr.responseText
    End If

End Sub

В комментариях я указал более надежный способ проверки положительного ответа от сервера всё что выше 200, но ниже 300 считается ок.

Очистите окно Immediate

Нажмите F5 или иконку старт.

Мы видим, что всё тоже самое ответ пришел верный.

Чтобы проверить блок else давайте в адрес добавим что-то неправильное, например, восклицательные знаки https://google.com/!!

Нажмите F5 или иконку старт.

В окне Immediate уже пришла страничка с ошибкой.

Таким образом можно обращается к серверу через VBA Excel

Я рекомендую нажать клавишу F8 чтобы по шагам прогрессировать программу и посмотреть каждый шаг.