mail mail
Нужен макрос для Excel?
Сделайте заказ прямо сейчас!
Ищете готовое решение?
Выбирайте и покупайте!
У вас есть интернет-магазин?
Настроим парсер под любой сайт!

Функция VBA для выполнения веб-запроса (Web Query)

Функция GetQueryRange предназначена для автоматизации загрузки данных с веб-страниц.

Например, нам надо из макроса Excel получить данные с нескольких однотипных страниц сайта.

Самый простой способ достичь этого - выполнять почти идентичные веб-запросы (где незначительно отличаться будет только URL страницы),
каждый раз анализируя данные, загруженные веб-запросом на лист Excel

Поскольку количество обращений ко мне, с просьбами сделать программу загрузки данных из интернета, с каждым днём растёт, я решил сделать для этих целей универсальную функцию:

 

Sub ПримерИспользования()
    Dim ra As Range: On Error Resume Next
 
    Set ra = GetQueryRange("http://ExcelVBA.ru/", 6)
    Debug.Print ra.Address    ' переменная ra содержит ссылку на диапазон ячеек $A$1:$C$15,
    ' содержащий данные 6-й таблицы главной страницы сайта ExcelVBA.ru

    Set ra = GetQueryRange("http://excelvba.ru/sitemap.xml")
    Debug.Print ra.Address    ' теперь переменная ra содержит ссылку на диапазон ячеек $A$1:$D$502,
    ' содержащий данные карты сайта ExcelVBA.ru

End Sub

При запуске функции, создаётся скрытый лист с именем «tmpWQ» (если такой лист уже есть - используется существующий), и на этом листе выполняется веб-запрос к указанному сайту.

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

Номер (или номера) требуемых страниц можно узнать, записав макрорекордером код веб-запроса к нужной веб-странице.

Функция загружает данные на лист в формате HTML, - соответственно, мы можем получить доступ к гиперссылкам в результатах веб-запроса.

 

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

Sub ЗагрузкаСпискаТемСФорума_ПланетаExcel()
    Dim ra As Range, cell As Range, n As Long: On Error Resume Next
 
    For i = 1 To 10
        ' формируем ссылку
        URL$ = "http://www.planetaexcel.ru/forum.php?forum_id=129&page_forum=" & i
 
        Set ra = GetQueryRange(URL$, 6)    ' выполняем веб-запрос

        ' перебирая ячейки таблицы-результата, выводим список тем в окно Immediate
        For Each cell In ra.Columns(2).Cells
            If cell.Hyperlinks.Count Then
                n = n + 1: Debug.Print "Тема №" & n, cell.Text
                Debug.Print "Ссылка на тему: " & cell.Hyperlinks(1).Address: Debug.Print
            End If
        Next cell
    Next i
End Sub

Скриншот результата этого макроса:

Результат выполнения веб-запросов из Excel (парсинг страниц сайта)

 

Код функции GetQueryRange:

Function GetQueryRange(ByVal SearchLink$, Optional ByVal Tables$) As Range
    On Error Resume Next: Err.Clear
    Dim tmpSheet As Worksheet: Set tmpSheet = ThisWorkbook.Worksheets("tmpWQ")
    If tmpSheet Is Nothing Then
        Application.ScreenUpdating = False
        Set tmpSheet = ThisWorkbook.Worksheets.Add
        tmpSheet.Name = "tmpWQ"
        tmpSheet.Visible = xlSheetVeryHidden
    End If
    If tmpSheet Is Nothing Then
        msg$ = "Не удалось добавить скрытый лист «tmpWQ» в файл программы"
        MsgBox msg, vbCritical, "Невозможно выполнить запрос к сайту": End
    End If
 
    tmpSheet.Cells.Delete: DoEvents: Err.Clear
    With tmpSheet.QueryTables.Add("URL;" & SearchLink$, tmpSheet.Range("A1"))
        If Len(Tables$) Then
            .WebSelectionType = xlSpecifiedTables
            .WebTables = Tables$
        Else
            .WebSelectionType = xlEntirePage
        End If
        .FillAdjacentFormulas = False: .PreserveFormatting = True
        .RefreshOnFileOpen = False: DoEvents
        .WebFormatting = xlWebFormattingAll
        .Refresh BackgroundQuery:=False: DoEvents
        If Err = 0 Then Set GetQueryRange = tmpSheet.UsedRange
        .Delete: DoEvents
    End With
End Function

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

Почему же не работают?
Макрос, опубликованный в этой статье, работает сам по себе, без каких-либо дополнительных прог.
Достаточно создать новый файл Excel, вставить туда ВЕСЬ код из статьи, — и всё заработает.

Вы прекрасно "разнесли" возможности по всему рунету. Но макросы без Вашей проги не работают. Изумительно! Писать код для юзеров, которые не работают без без проги! И стелить их по всему интернету.
Мне нравится Ваш подход к SEO.

Макс, тут не от протокола (https) зависит, а от конкретного сайта.
Какие-то сайты можно обрабатывать веб-запросами, а какие-то не получается.
В таких случаях поможет надстройка «Парсер сайтов»
http://excelvba.ru/programmes/Parser

Замечательная функция.
Только почему-то станица вида https://... не загружается в "tmpWQ."

Отличный материал. А кому мешают кракозябры раскройте глаза ведь есть http://excelvba.ru/code/Encode (отличная функция этого же автора)

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

Здравствуйте. Спасибо очень полезная функция для работы. Но возник вопрос, а как можно одновременно 2 опциональных таблицы выгрузить друг под другом, чтобы не выгружать лишнего? Пробовал сам скорректировать код, но у не получилось (( Заранее спасибо.

Добрый день. Авторизоваться получилось (точнее отправляется запрос на получение необходимых данных), а вот сохранить данные со страницы не получается. Сохраняется стартовая страница а не та, которая выводится после запроса. Подскажите как решить данный вопрос. Заранее спс.

Вам что не нравится? что я предусмотрительно не сделал макрос, идеально подходящий для вашей задачи?
Не нравятся мои макросы, вопросы возникают, - ну так берите макросы с других сайтов.
А те, кто обращается с платными заказами, - всегда получают решение, не требующее никаких доработок.
На этом сайте я публикую макросы прежде всего для себя (это для меня как записная книжка - выкладываю свои наработки, чтобы не потерялись)
PS: Пожалуйста, не посещайте больше мой сайт, раз мои бесплатные макросы вас не устраивают, и не пишите здесь коммменты.

Блин, совсем забыл, что тут за бесплатно рта никто не откроет! Так вот, братцы, чтобы Вам деньги люди платили, нужно, чтобы эти люди реально Вам поверили. А как? Если какой макрос отсюда не возьмёшь - куча вопросов появляется. Заплатишь Вам, а потом будешь сам сидеть и править. И незачем огрызаться, принимайте к сведению желания и просьбы потребителя.

Если тупо вставлять ссылку в процедуру, — конечно, ничего не получится
(да и вообще, «тупо вставлять» ни к чему хорошему не приводит...)

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

Код должен быть рабочим и универсальным

Да? Ну так напишите такой код на досуге

заголовки столбцов - сплошные кракозябры

Пожалуйтесь разработчику Excel (в Microsoft) - это они так сделали веб-запрос, что кодировку не указать.
У меня на сайте много макросов, - если умело их применять, то всегда можно получить желаемый результат.

К примеру мне нужно скачать данные с таблицы http://finance.yahoo.com/q/hp?s=^DJI+Historical+Prices и как это сделать? Да никак. Если тупо вставлять ссылку в процедуру - ничего не работает, надо сидеть и разбираться детально в коде самому и править-править-править. И на кого написан код? На новичка? Он ничего не поймёт. На профи - ему это не надо. Код должен быть рабочим и универсальным. (цитирую Уокенбаха)

http://excelvba.ru/sitemap.xml
вот с этой страницы заголовки столбцов - сплошные кракозябры. НЕ могу уяснить где какое свойство пропущено.

Проблема точно не в коде. Мне маленько не ясно где должны появляться скачанные данные, допустим в первом столбце адрес вэб страницы, во втором я включаю макрос, в searchlinks делаю ссылку на первый столбик, в tables ничего не пишу, дабы скачалось полностью страница, жму окей, вижу процесс пошел, но данных нигде нет! Вот, где искать скачанные данные я не понял!
Кажется я понял, у меня не получалось потому что я запускал это как функцию, а теперь я запустил это все как макрос с поддержкой макроса, и все данные стали выводиться в книге с поддержкой макроса, хотя сама формула и адреса в другой книге! Вот...как то так!

Максим, а чем я смогу помочь, зная только то, что вы написали?
Если первый раз все сделали, и всё получилось, - значит, проблема не в моём коде.
А почему у вас не получается, - сказать не могу, т.к. не знаю, что и каким кодом вы скачиваете.
Не можете разобраться, - оформите заказ на сайте, я сам всё сделаю, - и всё всегда будет работать как вам надо.

Здравствуйте!
Я не могу разобраться, первый раз у меня получилось, все вэб данные появились на листе, хотя я на этот лист даже не нажимал и ничего с ним не делал. А теперь у меня ничего не получается, я вижу что он скачал, скопировал и вставил в лист, но скачанные страницы я не вижу? И как их вывести на видное место, то же не понимаю!

Руслан, моя функция дает вам только ссылку на диапазон
Вы ведь потом этот диапазон куда-то копируете своим кодом, в единую таблицу, верно?
Так вот копируйте на один столбец правее, а в первый столбец вставляйте ссылку
(вы же эти действия выполняете в цикле из своего макроса - так что все просто)

PS: Могу продемонстрировать на примере, - если вы покажете свой код.

Добрый день, а не подскажите, как сюда добавить еще и ссылку откуда скопировалась таблица?
Суть: Использовал данную функцию, добавил цикл с обработкой массива ссылок лежащих на отдельной странице.Результат представлен в виде идущих подряд таблиц, которые нужно подписывать вручную. Можно как то автоматизировать?

Здраствуйте.
Очень полезная фукция для меня - большое Вам спасибо!..
Подскажите пожалуста, можна ли как-то получить после запроса URL страници которая загрузилась?

Здравствуйте, Михаил.
Встроенные средства Excel (веб-запрос) не поддерживают авторизацию.
Надо использовать более сложные макросы - сначала, например, авторизоваться на сервере, а потом работать уже с веб-страницей (анализируя текст веб-страницы, или её исходный код)

Если самостоятельно не разберетесь - оформляйте заказ, сделаем.

Здравствуйте! У меня такой вопрос: есть сайт с онлайн базой данных, для того что бы получить доступ к базе нужно авторизоваться. Через обычный импорт данных в Excel не получается. Как можно изменить этот макрос что бы сначала делал авторизацию и потом импорт данных.
Для логина нужно заполнить 2 поля(логин и пароль) и нажать на кнопку "Вход". Можете помочь?
Заранее спасибо

Здравствуйте, Сергей.
К сожалению, с видеоуроками помочь не смогу - на это потребуется много времени (которого у меня нет).
К тому же, есть куча сайтов, где выложены видеоуроки по работе с макросами в Excel.
Делать же аналогичное видео для каждой из опубликованных у меня на сайте функций - считаю необоснованной тратой времени.

А можете сделать видео как им пользоваться.
Штука нужная, но как использовать VAB макросы я не знаю.

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  _____  __  __              _         _     
| ____| \ \/ / _ __ ___ | | ____ | |__
| _| \ / | '_ ` _ \ | | |_ / | '_ \
| |___ / \ | | | | | | | | / / | | | |
|_____| /_/\_\ |_| |_| |_| |_| /___| |_| |_|
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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