Перенос веб-страниц VBA в Excel (пошаговое руководство)

Вот руководство на 2220 слов по использованию VBA для очистки веб-страниц в Excel:

Введение в парсинг веб-страниц VBA
Веб-скрапинг — это процесс извлечения данных с веб-сайтов и сохранения их в структурированных форматах, таких как Excel. VBA (Visual Basic для приложений) — это язык программирования, разработанный Microsoft, который позволяет автоматизировать задачи в приложениях Office. Объединив VBA с Excel, вы можете писать сценарии для сбора данных с веб-страниц и автоматически загружать их в электронные таблицы для анализа и составления отчетов.

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

• VBA поставляется в комплекте с Excel, поэтому вам не нужно устанавливать дополнительные библиотеки или зависимости, чтобы начать очистку. Просто включите инструменты разработчика в Excel, и все готово.
• Тесная интеграция между VBA и Excel позволяет легко переносить очищенные данные между кодом и электронной таблицей. Вы можете напрямую выводить значения в ячейки и диапазоны.
• VBA имеет встроенную поддержку автоматизации браузера с помощью объектной модели Internet Explorer. Это обеспечивает простой способ загрузки страниц, щелчка по элементам и извлечения данных.
• Веб-скрейперы VBA можно упаковать вместе с книгой Excel и распространить среди конечных пользователей. Макрос может собирать свежие данные одним щелчком мыши, не требуя знаний программирования.

Конечно, у VBA есть и некоторые недостатки по сравнению с более современными инструментами, такими как Python:

• VBA работает только в Windows и Excel. Парсеры, созданные с помощью Python или JavaScript, могут работать в разных операционных системах.
• Кривая обучения VBA более сложна, чем Python и другие языки, удобные для начинающих. Синтаксис может быть немного сложным для новичков в программировании.
• Автоматизация браузера опирается на Internet Explorer, который на данный момент является устаревшей технологией. Поддержка современных браузеров ограничена.
• Динамические веб-сайты, которые загружают контент через JavaScript, не могут быть очищены напрямую. Требуются обходные пути.

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

Настройка среды VBA
Прежде чем писать код очистки, сначала необходимо активировать инструменты разработчика в Excel и добавить ссылки, чтобы получить возможности анализа HTML и Интернета. Вот шаги:

1. Откройте книгу Excel и выберите «Файл» > «Параметры» > «Настроить ленту». Установите флажок «Разработчик» и нажмите «ОК». Это активирует вкладку «Разработчик» на ленте.
2. Перейдите на вкладку «Разработчик» и выберите Visual Basic, чтобы открыть редактор VBA.
3. В редакторе VBA нажмите «Инструменты» > «Ссылки», чтобы открыть диспетчер ссылок библиотеки.
4. Прокрутите вниз и установите флажки «Библиотека объектов Microsoft HTML» и «Элементы управления Интернетом Microsoft». Нажмите ОК.

Эти две библиотеки добавляют основные объекты и методы, такие как HTMLDocument и InternetExplorer, которые позволяют VBA взаимодействовать с веб-страницами. Теперь вы можете начать программировать свой веб-скребок!

Переход к веб-страницам
Первым шагом в любом скрипте парсинга веб-страниц является переход к целевому URL-адресу, из которого вы хотите извлечь данные. Вот пример кода для открытия веб-страницы:

Sub WebScraper()

  Dim IE As New InternetExplorer ‘Create IE browser object
  IE.Visible = True ‘Make it visible

  IE.Navigate "https://example.com" ‘Navigate to URL

  While IE.ReadyState <> 4 ‘Wait for page to load
    DoEvents 
  Wend

  ‘Remaining scraping code goes here

End Sub

Ключевые моменты:

• Объект InternetExplorer представляет собой экземпляр Internet Explorer, которым мы можем управлять с помощью кода.
• Если для параметра «Видимость» установлено значение «Истина», пользовательский интерфейс браузера становится видимым на экране во время очистки. Установка значения False скрывает это.
• Метод Navigate переходит по указанному URL-адресу.
• Цикл ожидает, пока ReadyState станет равным 4, что означает, что страница завершила загрузку.
• DoEvents обеспечивает оперативность реагирования Excel, поскольку цикл останавливает выполнение VBA до тех пор, пока страница не загрузится.

Теперь, когда мы можем надежно загружать веб-страницы, пришло время приступить к извлечению данных!

Основные методы парсинга веб-страниц
Содержимое веб-страницы находится в объекте HTMLDocument, который можно проанализировать для сбора данных. Вот некоторые распространенные методы:

• QuerySelector – находит первый элемент, соответствующий селектору CSS.
• getElementsByTagName – получает все элементы по имени тега.
• getElementById – получает один элемент по его идентификатору.
• getElementsByClassName – получает все элементы, соответствующие имени класса.

Например, чтобы очистить весь текст абзаца с сайта:

Dim doc As HTMLDocument
Set doc = IE.document

Dim paras As Object
Set paras = doc.getElementsByTagName("p")

For Each p In paras
  MsgBox p.innerText 
Next p

Мы можем комбинировать эти методы с циклами и условиями, чтобы извлечь именно те данные, которые нам нужны:

‘Get product data from ecommerce site

Dim products As Object, product As Object
Set products = doc.getElementsByClassName("product")  

For Each product In products
  Dim name = product.getElementsByClassName("name")(0).innerText
  Dim price = product.getElementsByClassName("price")(0).innerText

  ‘Save data to Excel here

  MsgBox name & " - " & price
Next product

Это циклически проходит по каждому продукту, извлекает название и цену и может сохранить их в электронной таблице с помощью Range/Cells. Ключом является детализация и выбор именно тех элементов, которые содержат нужные вам данные.

Обработка динамического контента веб-сайта
Ограничением парсинга веб-страниц VBA является то, что страницы, динамически отображаемые с помощью JavaScript, не могут быть парсингованы напрямую. HTMLDocument содержит только исходную статическую разметку. Однако есть несколько способов обойти эту проблему:

Браузерная автоматизация

Мы можем автоматизировать нажатие кнопок и прокрутку, что запускает JavaScript для выполнения и загрузки дополнительного контента. Например:

‘Click "Load More" button to reveal additional data

Dim loadMore As Object
Set loadMore = doc.getElementById("loadMore")

Do While loadMore IsNot Nothing
  loadMore.Click

  ‘Wait for new content to load

  Set loadMore = doc.getElementById("loadMore") 
Loop

‘Extract updated page content

Это имитирует нажатие кнопки пользователем-человеком до тех пор, пока контент не перестанет загружаться.

XMLHTTP запросы

Мы можем имитировать запросы AJAX, которые страница использует для получения динамических данных. Это напрямую возвращает контент, отображаемый JavaScript:

Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

xmlhttp.Open "GET", "https://api.example.com/data", False
xmlhttp.Send

Dim ajaxResults As String = xmlhttp.ResponseText
‘Parse ajaxResults JSON/HTML here

Это очищает конечную точку без необходимости имитировать действия браузера.

Хранение очищенных данных в Excel
Основным преимуществом очистки веб-страниц VBA является встроенная поддержка выгрузки данных непосредственно в Excel. Вот несколько способов вывода результатов:

• Запишите в отдельные ячейки:

  Cells(2, 1).Value = "Name" 
  Cells(2, 2).Value = scrapedName

• Запишите массивы в диапазоны:

  Dim results(2,2) As String
  
  results(0,0) = "Product" 
  results(0,1) = "Price"
  results(1,0) = scrapedProduct
  results(1,1) = scrapedPrice
  
  Range("A2").Resize(UBound(results, 1)+1, UBound(results, 2)+1).Value = results

• Прокрутите результаты:

  Dim rowCounter As Long
  
  For Each product In products
    rowCounter = rowCounter + 1
  
    Cells(rowCounter, 1).Value = product.name
    Cells(rowCounter, 2).Value = product.description
  Next product

При этом очищенные данные записываются в ячейки и диапазоны, динамически расширяясь по мере необходимости.

Имея данные в Excel, вы можете использовать формулы, сводные таблицы и диаграммы для дальнейшего анализа.

Отладка и устранение неполадок сценариев веб-скрапинга VBA
Как и в случае с любым другим кодом, при написании веб-скрейпера VBA вы рано или поздно столкнетесь с ошибками. Вот несколько советов по отладке и устранению неполадок:

• Установите точки останова, чтобы приостановить выполнение на определенных строках. Во время паузы наведите указатель мыши на переменные, чтобы проверить их значения.
• Используйте MsgBox и Debug.Print для вывода сообщений в ключевых точках кода.
• Грамотно обрабатывайте ошибки с помощью операторов On Error, а не простого возобновления при ошибке.
• Если элементы не найдены, попробуйте выбрать их вручную в браузере, чтобы проверить селектор.
• Временно записывайте очищенные данные в текстовый файл с помощью FileSystemObject при отладке неудачного вывода Excel.
• При возникновении проблем с HTTP-запросами проверьте коды состояния и заголовки ответов.
• Используйте инструменты разработчика Firefox/Chrome для выявления динамически загружаемого контента, требующего специальной обработки.
• Попробуйте включить QC (быструю компиляцию) в настройках VBA, чтобы получить более четкие сообщения об ошибках.
• Если браузеры заблокированы или ограничены, меняйте пользовательские агенты или добавляйте задержки между запросами.

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

Расширенные возможности парсинга веб-страниц
Хотя основы охватывают большинство простых задач парсинга, VBA открывает несколько расширенных возможностей:

Вход на веб-сайты

• Найдите поля имени пользователя и пароля по идентификатору, атрибуту имени или имени тега.
• Установите для полей ввода учетные данные.
• Найдите и нажмите кнопку «Отправить», чтобы войти в систему.

Скачивание файлов

• Создать объект файловой системы
• Вызовите метод .DownloadFile для загрузки в выбранную локальную папку.

Заполнение и отправка форм

• Установите значение каждого поля ввода.
• Выберите параметры из раскрывающихся списков
• Нажмите кнопку отправки
• Данные формы POST напрямую через XMLHTTPRequest

Автоматизация браузера

• Взаимодействие с дополнительными событиями браузера, такими как всплывающее окно оповещения.
• Извлечение файлов cookie и передача между сеансами
• Прокручивайте веб-страницы, устанавливая положение полосы прокрутки

Междоменный парсинг

• Маршрутизация запросов через прокси, например объект WinHttp.
• Управляйте файлами cookie на более высоком уровне, чем браузер.
• Имитировать заголовки и междоменные настройки

Они дают представление о некоторых более сложных задачах, которые вы можете выполнить, помимо базового извлечения данных. Предел – ваше воображение!

Пример проекта парсинга веб-страниц VBA

Чтобы связать концепции воедино, давайте рассмотрим пример проекта по сбору списков отелей с сайта TripAdvisor. Вот обзор логики скрипта:

1. Запросить у пользователя ключевые слова для поиска и количество страниц для парсинга
2. Создайте URL-адрес поиска на основе ключевых слов и номера страницы.
3. Прокручивать каждую страницу от 1 до указанного максимума
   • Загрузить HTML-страницу в объект документа
   • Извлеките название отеля, цену, количество отзывов, рейтинг, местоположение.
   • Записать данные в следующую доступную строку Excel
4. Отобразить сообщение после завершения очистки

А вот полный код:

Sub TripAdvisorScraper()

  ‘Collect inputs
  Dim keywords As String, maxPages As Long
  keywords = InputBox("Enter desired destination")
  maxPages = InputBox("Enter max pages to scrape")

  ‘Initialize variables
  Dim url As String, page As Long, doc As HTMLDocument
  Dim rowCounter As Long

  rowCounter = 2 ‘Start writing data to row 2

  ‘Loop through pages
  For page = 1 To maxPages

    ‘Construct URL
    url = "https://www.tripadvisor.com/Hotels-" & keywords & "-Hotels-oa" & page & ".html"

    ‘Load page
    Set doc = GetHTMLDoc(url) 

    ‘Find hotels
    Dim hotels As Object
    Set hotels = doc.getElementsByClassName("listing")

    ‘Extract data from each hotel
    For Each hotel In hotels

      ‘Hotel name
      Dim name As String
      name = hotel.getElementsByClassName("listing_title")(0).innerText

      ‘Price
      Dim price As String
      If hotel.getElementsByClassName("price").Length Then
        price = hotel.getElementsByClassName("price")(0).innerText
      Else
        price = "N/A"
      End If

      ‘Review count
      Dim reviews As String
      reviews = hotel.getAttribute("data-reviews-count")

      ‘Rating
      Dim rating As String
      rating = hotel.getElementsByClassName("rating")(0).innerText

      ‘Location 
      Dim location As String
      location = hotel.getElementsByClassName("location")(0).innerText

      ‘Write hotel data to Excel
      Cells(rowCounter, 1).Value = name 
      Cells(rowCounter, 2).Value = price
      Cells(rowCounter, 3).Value = reviews 
      Cells(rowCounter, 4).Value = rating
      Cells(rowCounter, 5).Value = location

      rowCounter = rowCounter + 1

    Next hotel

  Next page

  MsgBox "Scraping complete!"

End Sub

‘Returns HTML document for given URL 
Function GetHTMLDoc(url As String) As HTMLDocument

  Dim IE As New InternetExplorer
  IE.Visible = False ‘Disable UI

  IE.Navigate url

  Do While IE.ReadyState <> 4
    DoEvents 
  Loop 

  Set GetHTMLDoc = IE.document

End Function

Это демонстрирует основные методы, такие как:

• Запрос ввода данных пользователем
• Объединение значений в URL-адрес
• Циклическое перебор страниц
• Извлечение нескольких атрибутов из списков
• Запись каждого результата в следующую строку
• Использование вспомогательной функции для уменьшения дублирования кода

После запуска у вас будет таблица с отелями, ценами и другими данными, готовая для дальнейшего анализа!

Ресурсы для изучения веб-скрапинга VBA

Вот несколько рекомендаций, которые помогут улучшить ваши навыки парсинга веб-страниц:

• Документация Microsoft – Официальный справочник VBA
• Автоматизируйте скучные вещи – Практические проекты по программированию
• Excel Exposure - Курсы, посвященные Excel VBA
• Переполнение стека — решения распространенных вопросов по VBA
• Reddit – Активные обсуждения программирования VBA
• YouTube – видеоуроки по темам парсинга веб-страниц.
• Udemy — Платные курсы по Excel, VBA и веб-скрапингу

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