Руководство по веб-парсингу с помощью VBA

Из этого руководства по парсингу с помощью Excel вы узнаете:

• Что такое VBA.
• Позволяет ли пользовательский модуль VBA извлекать информацию из Интернета.
• Как выполнять веб-парсинг в Excel после прекращения поддержки Internet Explorer.
• Как написать код VBA для веб-парсинга с помощью Selenium.
• Устаревший подход к извлечению онлайн-данных в Excel.

Давайте узнаем подробности!

Что такое VBA?

VBA, сокращение от Visual Basic for Applications, — это язык программирования, разработанный корпорацией Microsoft. Его цель — предоставить пользователям возможность автоматизировать задачи и создавать собственные функции в различных приложениях Microsoft Office, включая Excel, Word и Access.

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

Можно ли выполнять веб-парсинг в Excel с помощью VBA?

Да, VBA открывает возможность веб-парсинга непосредственно в Excel. Благодаря VBA вы можете писать собственные модули, которые:

1. подключаются к веб-странице в браузере;
2. анализируют HTML-контент;
3. извлекают из него данные;
4. записывают полученные в ходе парсинга данные непосредственно в ячейки Excel.

В этом и заключается суть веб-парсинга. Таким образом, парсинг с помощью VBA не только возможен, но и эффективен, поскольку он напрямую импортирует данные в Excel. Узнайте больше о плюсах и минусах парсинга в Excel из этого раздела!

Плюсы и минусы веб-парсинга в Excel

Прежде чем углубиться в написание кода VBA для веб-парсинга, давайте рассмотрим некоторые преимущества и недостатки веб-парсинга в Excel с помощью VBA.

👍 Плюсы

• Доступ к функциям Excel. Полученные при парсинге данные напрямую импортируются в Excel, что позволяет использовать мощные функции Excel для их обработки и анализа.
• Готовность к работе. Microsoft Office поставляется с поддержкой VBA «из коробки». На вашем ПК установлен только Microsoft Office 365, и у вас есть все необходимое для веб-парсинга.
• Автоматизация «все в одном». С помощью скрипта веб-парсинга VBA вы можете автоматизировать всю задачу сбора данных, от извлечения данных до представления данных в Excel.

👎 Минусы

• Некоторые функции доступны только в Windows. Пакеты VBA для элементов управления Active-X и автоматизации COM доступны только в Windows и не работают в Office для Mac.
• VBA кажется устаревшим. Visual Basic — не самый современный язык программирования. Если вы никогда не работали с ним, возможно, вам будет сложно его использовать из-за старого синтаксиса и подхода к кодированию.
• Internet Explorer устарел. Автоматизация VBA COM для управления веб-браузером основана на Internet Explorer, который уже устарел.

Последняя проблема довольно серьезна, поэтому она заслуживает более глубокого анализа.

Как решить проблему с устареванием Internet Explorer при веб-парсинге VBA?

Устаревший подход к написанию кода VBA для веб-парсинга основывался на интерфейсе автоматизации COM, поставляемом с объектом InternetExplorer. Это позволяло получить все необходимое для посещения сайта в Internet Explorer, анализа его HTML-содержимого и извлечения из него данных. Проблема в том, что Internet Explorer больше не поддерживается с 15 июня 2022 года.

Другими словами, Internet Explorer просто отсутствует в самых последних версиях Windows. В результате этого использование объекта InternetExplorer в скрипте VBA приведет к ошибке. Поскольку Edge является заменой Internet Explorer, вы можете подумать об использовании эквивалентного объекта в VBA. Однако Edge не поставляется с интерфейсом автоматизации COM. Поэтому вы не сможете управлять им программным путем в отличие от Internet Explorer.

Вместо этого Edge поддерживает автоматизацию с помощью веб-драйвера, которым можно управлять с помощью технологий автоматизации браузеров, таких как Selenium. Поэтому в настоящее время поддерживаемый способ веб-парсинга Excel VBA заключается в использовании привязки Selenium для VBA. Это позволяет управлять такими браузерами, как Chrome, Edge и Firefox.

В следующем разделе вы создадите скрипт для веб-парсинга Excel с использованием Selenium и Edge. Позже вы также увидите фрагмент кода устаревшего подхода, который не требует сторонних зависимых объектов.

Как написать код VBA для веб-парсинга с помощью Selenium

Из этого раздела руководства вы узнаете, как выполнять веб-парсинг Excel в VBA с помощью SeleniumBasic, привязки Selenium API для VBA.

Целевым сайтом будет песочница Scrape This Site Country, содержащая список всех стран мира:

Целью парсера VBA будет автоматическое извлечение этих данных и их запись в электронную таблицу Excel.

Пора начать писать код VBA!

Предварительные условия

Убедитесь, что на вашем компьютере установлена последняя версия Microsoft Office 365. В этом разделе используется Windows 11 и обновление Office 2024. В то же время приведенные ниже шаги будут одинаковыми или похожими для macOS и других версий Office.

Обратите внимание, что для выполнения указаний этого руководства требуется версия Office для ПК. Бесплатная веб-платформа Microsoft 365 не поддерживает написание сценариев VBA.

Шаг 1. Установка и настройка SeleniumBasic

Скачайте инсталлятор SeleniumBasic со страницы релизов репозитория GitHub:

Дважды нажмите на установщик с расширением .exe и дождитесь завершения процесса установки.

Как и большинство пакетов VBA, SeleniumBasic не обновлялся уже много лет. Из-за этого он поставляется с веб-драйверами, которые больше не работают в последних браузерах. Чтобы исправить это, вам необходимо вручную переопределить исполняемые файлы драйверов в папке установки SeleniumBasic.

Здесь вы узнаете, как переопределить веб-драйвер Edge, но вы можете выполнить ту же процедуру для Chrome и Firefox.

Сначала загрузите последнюю стабильную версиюMicrosoft Edge WebDriver:

вам нужна версия «x64».

Теперь у вас должен иметься файл edgedriver_win64.zip. Распакуйте этот файл, войдите в папку с распакованным содержимым и убедитесь, что в ней содержится исполняемый файл msedgedriver.exe. Это исполняемый файл Edge WebDriver.

Переименуйте файл в edgedriver.exe и приготовьтесь поместить его в нужную папку.

Откройте папку установки SeleniumBasic, которая должна находиться по адресу:

C:\Users\\AppData\Local\SeleniumBasic

Поместите файл edgedriver.exe в эту папку, переопределив существующий исполняемый файл Edge WebDriver.

Замечательно! Теперь SeleniumBasic сможет управлять последней версией Edge в Excel.

Шаг 2. Запуск Excel

Откройте меню «Пуск» Windows, введите «Excel» и нажмите на приложение Excel. Выберите опцию «Пустая книга», чтобы создать новую электронную таблицу:

После выполнения указаний этого раздела будут собраны данные парсинга.

Шаг 3. Включение вкладки «Разработчик»

На панели вкладок вверху нет функции создания сценария VBA. Это связано с тем, что сначала необходимо включить эту функцию в конфигурациях Excel.

Для этого нажмите «Файл» в верхнем левом углу:

Затем выберите «Параметры», как показано ниже:

Во всплывающем окне «Параметры» перейдите на вкладку «Настроить ленту» и отметьте параметр «Разработчик» в разделе «Основные вкладки»:

Нажмите «ОК», и появится новая вкладка «Разработчик»:

Шаг 4. Инициализация модуля веб-парсинга VBA

Перейдите на вкладку «Разработчик» и нажмите на кнопку «Visual Basic»:

Откроется следующее окно:

Здесь нажмите «Вставить» в верхнем меню, а затем «Модуль», чтобы инициализировать модуль парсинга VBA:

Вот что вы должны увидеть сейчас:

Во внутреннем окне «Book1 – Module1 (Code)» вам нужно написать код VBA для веб-парсинга.

Шаг 5. Импорт Seleniumbasic

В верхнем меню нажмите «Инструменты», а затем «Ссылки…»

Во всплывающем окне найдите «Библиотеку типов Selenium» и отметьте ее:

Нажмите кнопку «ОК», и теперь вы сможете использовать Selenium в Excel для веб-парсинга.

Шаг 6. Автоматизация Edge для открытия целевого сайта:

Вставьте следующий код в окно модуля VBA:

Sub scrape_countries()

' initialize a Selenium WebDriver instance

Dim driver As New WebDriver

' open a new Edge window

driver.Start "Edge"

' navigate to the target page

driver.Get "https://www.scrapethissite.com/pages/simple/"

' wait 10 seconds before shutting down the application

Application.Wait Now + TimeValue("00:00:10")

' close the current driver window

driver.Quit

End Sub

Это инициализирует инстанс Selenium и использует его для дачи инструкции Edge посетить целевую страницу. Проверьте код, нажав кнопку запуска:

Откроется следующее окно Edge:

Обратите внимание на сообщение «Microsoft Edge управляется программой для автоматического тестирования». В сообщении говорится, что Selenium работает на Edge должным образом.

Если вы не хотите, чтобы Edge отображался, вы можете включить режим без заголовка с помощью следующей строки:

driver.SetCapability "ms:edgeOptions", "{""args"":[""--headless""]}"

Шаг 7. Проверка HTML-кода страницы

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

Чтобы определить эффективные селекторы CSS, сначала необходимо ознакомиться с HTML-кодом целевой страницы. Итак, откройте песочницу Scrape This Site Country в браузере, нажмите правой кнопкой мыши на элементе страны и выберите опцию «Проверить»:

Здесь вы можете увидеть, что каждый HTML-элемент country является

.country

Получив HTML-узел .country, вам следует настроить таргетинг на:

• Название страны в элементе .country-name.
• Название столицы в элементе .country-capital.
• Информацию о населении в элементе .country-population.
• Площадь страны в км² в элементе .country-area.

Это все CSS-селекторы, необходимые для выбора нужных HTML-узлов и извлечения из них данных. Узнайте, как их использовать на следующем этапе!

Шаг 8. Написание логического механизма веб-парсинга VBA

Используйте метод FindElementsByCss(), предоставленный драйвером, чтобы применить CSS-селектор .country и выбрать все HTML-узлы country на странице:

Dim countryHTMLElements As WebElements

Set countryHTMLElements = driver.FindElementsByCss(".country")

Then, define an integer counter to keep track of the current Excel row to write data in:

Dim currentRow As Integer

currentRow = 1

Затем выполните итерацию по HTML-узлам country, извлеките из них нужные данные и запишите их в ячейки Excel с помощью функции Cells():

For Each countryHTMLElement In countryHTMLElements

' where to store the scraped data

Dim name, capital, population, area As String

' data retrieval logic

name = countryHTMLElement.FindElementByCss(".country-name").Text

capital = countryHTMLElement.FindElementByCss(".country-capital").Text

population = countryHTMLElement.FindElementByCss(".country-population").Text

area = countryHTMLElement.FindElementByCss(".country-area").Text

' write the scraped data in Excel cells

Cells(currentRow, 1).Value = name

Cells(currentRow, 2).Value = capital

Cells(currentRow, 3).Value = population

Cells(currentRow, 4).Value = area

' increment the row counter

currentRow = currentRow + 1

Next countryHTMLElement

Потрясающе! Вы готовы ознакомиться с финальным кодом для веб-парсинга Excel.

Шаг 9. Объединение всего в одно целое

Теперь ваш модуль веб-парсинга VBA должен содержать:

Sub scrape_countries()

' initialize a Selenium WebDriver instance

Dim driver As New WebDriver

' enable the "headless" mode

driver.SetCapability "ms:edgeOptions", "{""args"":[""--headless""]}"

' open a new Edge window

driver.Start "Edge"

' navigate to the target page

driver.Get "https://www.scrapethissite.com/pages/simple/"

' select all country HTML nodes on the page

Dim countryHTMLElements As WebElements

Set countryHTMLElements = driver.FindElementsByCss(".country")

' counter to the current row

Dim currentRow As Integer

currentRow = 1

' iterate over each country HTML node and

' apply the Excel scraping logic

For Each countryHTMLElement In countryHTMLElements

' where to store the scraped data

Dim name, capital, population, area As String

' data retrieval logic

name = countryHTMLElement.FindElementByCss(".country-name").Text

capital = countryHTMLElement.FindElementByCss(".country-capital").Text

population = countryHTMLElement.FindElementByCss(".country-population").Text

area = countryHTMLElement.FindElementByCss(".country-area").Text

' write the scraped data in Excel cells

Cells(currentRow, 1).Value = name

Cells(currentRow, 2).Value = capital

Cells(currentRow, 3).Value = population

Cells(currentRow, 4).Value = area

' increment the row counter

currentRow = currentRow + 1

Next countryHTMLElement

' close the current driver window

driver.Quit

End Sub

Запустите модуль и дождитесь завершения его выполнения. В конце сценария VBA электронная таблица Excel будет содержать:

Этот файл содержит те же данные, что и целевой сайт, но в полуструктурированном формате. Анализировать и фильтровать эти данные теперь станет намного проще благодаря возможностям Excel.

Все получилось! Менее чем со 100 строками кода VBA вы только что выполнили веб-парсинг в Excel!

Узнайте больше в нашем руководстве о том, как работает веб-парсинг в Excel.

Устаревший подход к веб-парсингу в VBA с помощью Internet Explorer

Если вы используете старую версию Windows, вы можете выполнять веб-парсинг в VBA с помощью Internet Explorer.

Все, что вам нужно сделать, — это включить ссылки «Microsoft HTML Object Library» («Библиотека объектов Microsoft HTML») and «Microsoft Internet Controls» («Средства интернет-контроля Microsoft»):

Обратите внимание, что эти два пакета встроены в Excel, поэтому на этот раз вам не нужно устанавливать дополнительные библиотеки.

Затем вы можете достичь того же результата, что и раньше, с помощью следующего кода VBA для веб-парсинга:

Sub scrape_countries()

' start Internet Explorer

Dim browser As InternetExplorer

Set browser = New InternetExplorer

' enable the "headless" mode

browser.Visible = False

'visit the target page

browser.navigate "https://www.scrapethissite.com/pages/simple/"

' wait for the browser to load the page

Do: DoEvents: Loop Until browser.readyState = 4

' get the current page

Dim page As HTMLDocument

Set page = browser.document

' retrieve all country HTML nodes on the page

Dim countryHTMLNodes As Object

Set countryHTMLElements = page.getElementsByClassName("country")

' counter to the current row

Dim currentRow As Integer

currentRow = 1

' iterate over each country HTML node and

' apply the Excel scraping logic

For Each countryHTMLElement In countryHTMLElements

' where to store the scraped data

Dim name, capital, population, area As String

' data retrieval logic

name = countryHTMLElement.getElementsByClassName("country-name")(0).innerText

capital = countryHTMLElement.getElementsByClassName("country-capital")(0).innerText

population = countryHTMLElement.getElementsByClassName("country-population")(0).innerText

area = countryHTMLElement.getElementsByClassName("country-area")(0).innerText

' write the scraped data in Excel cells

Cells(currentRow, 1).Value = name

Cells(currentRow, 2).Value = capital

Cells(currentRow, 3).Value = population

Cells(currentRow, 4).Value = area

' increment the row counter

currentRow = currentRow + 1

Next countryHTMLElement

' close the current Internext Explorer window

browser.Quit

End Sub

Запустите этот модуль VBA, и вы получите тот же результат, что и раньше. Невероятно! Вы только что выполнили веб-парсинг в Excel с помощью Internet Explorer.

Заключение

Из этого руководства вы узнали, что такое VBA и почему он позволяет выполнять веб-парсинг в Excel. Проблема в том, что библиотека автоматизации браузера VBA использует Internet Explorer, который больше не поддерживается. Здесь вы изучили эквивалентный подход к автоматизации извлечения данных в Excel с помощью VBA с использованием Selenium. Кроме того, вы ознакомились с устаревшим подходом к старым версиям Windows.

В то же время имейте в виду, что при извлечении данных из Интернета необходимо учитывать множество проблем. В частности, на большинстве сайтов используются меры защиты от парсинга и ботов, которые могут обнаруживать и блокировать ваш скрипт веб-парсинга VBA. Обходите такие меры с помощью нашего решения Scraping Browser . Этот браузер нового поколения интегрируется с Selenium и может автоматически обрабатывать разрешение CAPTCHA, цифровые отпечатки браузера, автоматические повторные попытки и многое другое!

Совсем не хотите заниматься веб-парсингом, но вас интересуют данные Excel? Ознакомьтесь с нашими готовыми к использованию наборами данных. Не знаете, какое решение для обработки данных выбрать? Свяжитесь с нами сегодня!