VBA Excel. Парсинг сайтов, html-страниц и файлов

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

Содержание

1. Парсинг html-страниц (msxml2.xmlhttp)

2. Парсинг сайтов (WinHttp.WinHttpRequest.5.1)

3. Парсинг файлов (ADODB.Stream)

4. Примеры записи текста в переменную

5. Извлечение данных из html

5.1. Парсинг содержимого тегов

5.2. Парсинг содержимого Id

Парсинг html-страниц (msxml2.xmlhttp)

Пользовательская функция GetHTML1 (VBA Excel) для извлечения (парсинга) текстового содержимого из html-страницы сайта по ее URL-адресу с помощью объекта «msxml2.xmlhttp»:

Function GetHTML1(ByVal myURL As String) As String

On Error Resume Next

With CreateObject("msxml2.xmlhttp")

.Open "GET", myURL, False

.send

Do: DoEvents: Loop Until .readyState = 4

GetHTML1 = .responseText

End With

End Function

Парсинг сайтов (WinHttp.WinHttpRequest.5.1)

Пользовательская функция GetHTML2 (VBA Excel) для извлечения (парсинга) текстового содержимого из html-страницы сайта по ее URL-адресу с помощью объекта «WinHttp.WinHttpRequest.5.1»:

Function GetHTML2(ByVal myURL As String) As String

On Error Resume Next

With CreateObject("WinHttp.WinHttpRequest.5.1")

.Open "GET", myURL, False

.send

Do: DoEvents: Loop Until .readyState = 4

GetHTML2 = .responseText

End With

End Function

Парсинг файлов (ADODB.Stream)

Пользовательская функция GetText (VBA Excel) для извлечения (парсинга) текстового содержимого из файла (.txt, .csv, .mhtml), сохраненного на диск компьютера, по его полному имени (адресу) с помощью объекта «ADODB.Stream»:

Function GetText(ByVal myFile As String) As String

On Error Resume Next

With CreateObject("ADODB.Stream")

.Charset = "utf-8"

.Open

.LoadFromFile myFile

GetText = .ReadText

.Close

End With

End Function

Примеры записи текста в переменную

Общая формула записи текста, извлеченного с помощью пользовательских функций VBA Excel, в переменную:

Dim htmlText As String

htmlText = GetHTML1("Адрес сайта (html-страницы)")

htmlText = GetHTML2("Адрес сайта (html-страницы)")

htmlText = GetText("Полное имя файла")

Конкретные примеры:

htmlText = GetHTML1("https://internettovary.ru/nabor-dlya-vyrashchivaniya-veshenki/")

htmlText = GetHTML2("https://internettovary.ru/nabor-dlya-vyrashchivaniya-veshenki/")

htmlText = GetText("C:\Users\Evgeniy\Downloads\Новый текстовый документ.txt")

htmlText = GetText("C:\Users\Evgeniy\Downloads\Использование msxml2.xmlhttp в Excel VBA.mhtml")

Извлечение данных из html

В понятие «парсинг», кроме извлечения текстового содержимого сайтов, html-страниц или файлов, входит поиск и извлечение конкретных данных из всего полученного текстового содержимого.

Пример извлечения email-адресов из текста, присвоенного переменной, смотрите в последнем параграфе статьи: Регулярные выражения (объекты, свойства, методы).

Парсинг содержимого тегов

Извлечение содержимого тегов с помощью метода getElementsByTagName объекта HTMLFile:

Sub Primer1()

Dim myHtml As String, myFile As Object, myTag As Object, myTxt As String

'Извлекаем содержимое html-страницы в переменную myHtml с помощью функции GetHTML1

myHtml = GetHTML1("https://internettovary.ru/sadovaya-nozhovka-sinitsa/")

'Создаем объект HTMLFile

Set myFile = CreateObject("HTMLFile")

'Записываем в myFile текст из myHtml

myFile.body.innerHTML = myHtml

'Присваиваем переменной myTag коллекцию одноименных тегов, имя которого

'указанно в качестве аргумента метода getElementsByTagName

Set myTag = myFile.getElementsByTagName("p")

'Выбираем, содержимое какого тега по порядку, начинающегося с 0, нужно извлечь

myTxt = myTag(5).innerText

MsgBox myTxt

'Большой текст может не уместиться в MsgBox, тогда для просмотра используйте окно Immediate

'Debug.Print myTxt

End Sub

С помощью этого кода извлекается текст, расположенный между открывающим и закрывающим тегами. В примере — это текст 6-го абзаца (p) между 5-й (нумерация с 0) парой отрывающего <p> и закрывающего </p> тегов.

Примеры тегов, используемых в html: "p", "title", "h1", "h2", "table", "div", "script".

Пример извлечения содержимого тега "title":

Sub Primer2()

Dim myHtml As String, myFile As Object, myTag As Object, myTxt As String

myHtml = GetHTML1("https://internettovary.ru/sadovaya-nozhovka-sinitsa/")

Set myFile = CreateObject("HTMLFile")

myFile.body.innerHTML = myHtml

Set myTag = myFile.getElementsByTagName("title")

myTxt = myTag(0).innerText

MsgBox myTxt

End Sub

Парсинг содержимого Id

Извлечение текстового содержимого html-элементов, имеющих уникальный идентификатор — Id, с помощью метода getElementById объекта HTMLFile:

Sub Primer3()

Dim myHtml As String, myFile As Object, myTag As Object, myTxt As String

myHtml = GetHTML1("https://internettovary.ru/sadovaya-nozhovka-sinitsa/")

Set myFile = CreateObject("HTMLFile")

myFile.body.innerHTML = myHtml

'Присваиваем переменной myTag html-элемент по указанному в скобках Id

Set myTag = myFile.getElementById("attachment_465")

'Присваиваем переменной myTxt текстовое содержимое html-элемента с Id

myTxt = myTag.innerText

MsgBox myTxt

'Большой текст может не уместиться в MsgBox, тогда для просмотра используйте окно Immediate

'Debug.Print myTxt

End Sub

Для реализации представленных здесь примеров могут понадобиться дополнительные библиотеки. В настоящее время у меня подключены следующие (к данной теме могут относиться последние шесть):

Visual Basic For Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library
Ref Edit Control
Microsoft Scripting Runtime
Microsoft Word 16.0 Object Library
Microsoft Windows Common Controls 6.0 (SP6)
Microsoft ActiveX Data Objects 6.1 Library
Microsoft ActiveX Data Objects Recordset 6.0 Library
Microsoft HTML Object Library
Microsoft Internet Controls
Microsoft Shell Controls And Automation
Microsoft XML, v6.0

С этим набором библиотек все примеры работают. Тестирование проводилось в VBA Excel 2016.

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

6 комментариев для “VBA Excel. Парсинг сайтов, html-страниц и файлов”

shurella 07.08.2022 в 07:38

Доброго дня, коллеги.
Задача следующая: в элементе ВэбБраузерКонтрол на странице поиска выполняю определенный запрос и визуально убедившись в получении нужного результата начинаю парсить содержимое. Вопрос как получить текстовое содержимое элемента ВэбБраузерКонтрол в момент когда там отображена нужная информация.
Заранее спасибо.
Сергей 23.05.2023 в 16:04

Доброго дня!
При попытке вычислить к примеру первое вхождение, ищет все равно следующее почему то.

Sub Primer1() Dim myHtml As String, myFile As Object, myTag As Object, myTxt As String 'Извлекаем содержимое html-страницы в переменную myHtml с помощью функции GetHTML1 myHtml = GetHTML1("https://bik-info.ru?040702802") 'Создаем объект HTMLFile Set myFile = CreateObject("HTMLFile") 'Записываем в myFile текст из myHtml myFile.body.innerHTML = myHtml 'Присваиваем переменной myTag коллекцию одноименных тегов, имя которого 'указанно в качестве аргумента метода getElementsByTagName Set myTag = myFile.getElementsByTagName("strong") 'Выбираем, содержимое какого тега по порядку, начинающегося с 0, нужно извлечь myTxt = myTag(0).innerText Debug.Print myTxt 'Большой текст может не уместиться в MsgBox, тогда для просмотра используйте окно Immediate 'Debug.Print myTxt End Sub

Function GetHTML1(ByVal myURL As String) As String On Error Resume Next With CreateObject("msxml2.xmlhttp") .Open "GET", myURL, False .send Do: DoEvents: Loop Until .readyState = 4 GetHTML1 = .responseText End With End Function
1. Евгений 23.05.2023 в 16:59
  
  Здравствуйте, Сергей!
  На HTML-странице, которую вы указали, тегом strong выделены только две одинаковые даты. Предположу, что отображается первая дата. Если strong заменить на b, отобразится знак $, который идет первый с этим тегом.
2. 1. Сергей 24.05.2023 в 12:31
    
    Евгений, приношу извинения, неверно указал в спешке адрес запроса.
    Верный myHtml = GetHTML1("https://bik-info.ru/bik/040702660")
    Там 2 тега "strong", первый как раз показывает что данный «БИК 040702660 аннулирован!»
    Если как Вы сказали поставить b, то выводит почему то только БИК 040702660. А слово аннулирован пропало.
  2. 1. Евгений 24.05.2023 в 17:04
      
      Тогда я не понял суть вопроса. БИК 040702660 аннулирован! — это первое вхождение с тегом strong.
    2. 1. Сергей 24.05.2023 в 17:20
        
        Приношу извинения. все работает!
        Наверно что то не верно делал.

Обсуждение закрыто.