Импорт данных с web-страниц в Excel средствами VBA

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

Вы обращали внимание, что когда вы ищете какие-то товары в интернет-магазине или когда смотрите расписание какого-либо маршрута на сайте вроде tutu.ru, в адресной строке браузера формируется url по вполне логичным правилам?

Пример
http://www.tutu.ru/rasp.php?st1=201&st2=1501&date=07.10.2015

Разбор ссылки по частям
http://www.tutu.ru/rasp.php — начальная страница сайта
Часть строки после знака вопроса — параметры, передаваемые на сервер
st1=201 — станция отправления (Беговая)
st2=1501 — станция прибытия (Трехгорка)
date=07.10.2015 — дата, за которую необходимо выгрузить расписание

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

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

Получаем ссылку вида
https://7bx.ru/radio_track/avtoradio/20151003

Здесь все просто и логично, никаких параметров
avtoradio — название радиостанции
20151003 — дата плейлиста в формате YYYYMMDD

Далее нам необходимо импортировать содержимое страницы в Excel. На помощь нам приходит макрорекордер и импорт данных. На вкладке Разработчик включаем запись макроса (или в строке состояния нажав на кружок), затем идем на вкладку «Данные» и запускаем импорт внешних данных «Из интернета»

Импорт

К сожалению, как и большинство сайтов интернета, этот также не очень удобно импортировать — вместо аккуратной таблички со списком песен придется импортировать все содержимое страницы (желтая стрелочка в левом верхнем углу страницы)

Макрорекордер записал следующий код

[code language=»vb»]
Sub Макрос1()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://7bx.ru/radio_track/avtoradio/20151005", Destination:=Range( _
"$A$1"))
.CommandType = 0
.Name = "20151005"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
[/code]

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

[code language=»vb»]
Sub Macro2()

Dim station As String
Dim nDate As String
Dim url As String: url = "https://7bx.ru/radio_track/"

station = "avtoradio"
nDate = Format(Date — 1, "YYYYMMDD") ‘ Смотрим плейлист за вчерашний день

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & url & station & "/" & nDate, Destination:=[A1])
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=False
End With

End Sub
[/code]

Остальное — дело техники, подчистить результат, задать массив с названиями радиостанциями и использовать цикл для загрузки плейлистов всех доступных радиостанций 🙂

1 комментарий к “Импорт данных с web-страниц в Excel средствами VBA”

  1. Добрый день. А можно ли в макросе сделать авторизацию на сайте внутри веб запроса? Вне веб-запроса авторизация не помогает достать все данные.

    Ответить

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

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.