Выгрузка данных с сайта в excel vba

Обновлено: 06.07.2024

Открыл для себя удобство при работе с Excel и таблицами на web-страницах.
Бывает нужно поместить таблицу, находящуюся на web-странице, в таблицу Excel. Обычным путем Ctrl+C / Ctrl+V не всегда получается нормально вставить, "Специальная вставка. " тоже не всегда помогает.

И вот не так давно наткнулся, совершенно случайно, на такую функцию Excel как "Обновляемый web запрос. ".
Периодически он меня выручает.

Цель статьи не в том что я "открыл Америку", а для тех кто еще не попробовал или не увидел эту функцию.

На оф сайте говорится что она была уже в версии Excel 2003, я ее для себя открыл только в Excel 2010.

Думаю многие знают про нее, раскажем и другим.

Ниже приведено описание с оф. сайта:

Веб-страницы зачастую содержат информацию, пригодную для анализа в Microsoft Excel.
Например, в Microsoft Excel можно анализировать котировки акций, используя данные, поступающие прямо с веб-страницы.
При необходимости можно извлечь обновляемые данные (в этом случае их можно обновлять непосредственно в Microsoft Excel в соответствии с последними изменениями веб-страницы) или получить данные с веб-страницы и хранить их на листе статически.
Использование веб-запроса позволяет получить данные, например отдельную таблицу, несколько таблиц или весь текст, с веб-страницы и провести их анализ, используя инструменты и свойства Microsoft Excel. Щелкнув соответствующую кнопку, можно обновить данные и получить последние сведения с веб-страницы.
Например, можно получить и обновить котировки акций с общедоступной веб-страницы или таблицу с данными о продажах с веб-страницы организации.

Что делаем:

1) Открываем web-страницу с нужной нам таблицей.

2) Копируем кусочек текста с нее (хоть 1 слово)

3) В Excel жмем кнопку "Вставить" - "Обновляемый web запрос. "

Открывается окно импорта данных:

помечаем нужную нам таблицу, и жмем кнопку "Импортировать"

( Можно пропустить пункты 1-2 и в окне создания web-запроса ввести адрес)

После нажатия на кнопку "Импорт" -запрос выполнится и таблица будет импортирована.

Далее делаем все что требуется.

Если сохраним файл, и при открытии его в следующий раз нажмем кнопку "Обновить" или "Обновить ВСЕ", вставленная нами таблица обновится с web-страницы.

Посмотрел код. К сожалению, не получается применить его .

Сейчас беру след. код за основу, и пытаюсь что-то делать . Подсказки/советы приветствую .
Спасибо.

Do Until IE.ReadyState = 4: DoEvents: Loop

Set doc = IE.Document

For Each tr In colTR

For Each td In colTD
MsgBox td.innertext

Посмотрел код. К сожалению, не получается применить его .

Сейчас беру след. код за основу, и пытаюсь что-то делать . Подсказки/советы приветствую .
Спасибо.

Do Until IE.ReadyState = 4: DoEvents: Loop

Set doc = IE.Document

For Each tr In colTR

For Each td In colTD
MsgBox td.innertext

Сейчас беру след. код за основу, и пытаюсь что-то делать . Подсказки/советы приветствую .
Спасибо.

Do Until IE.ReadyState = 4: DoEvents: Loop

Set doc = IE.Document

For Each tr In colTR

For Each td In colTD
MsgBox td.innertext

[/vba] Автор - Guest
Дата добавления - 06.11.2014 в 14:47

Благодарю за помощь, Сергей.

Да, код должен работать примерно так. Я попробую модифицировать его, чтобы получить таблицу в формате:

Дата RUB USD EUR GBP
01.01.2013 4,96 150,74 199,22 243,72
02.01.2013 4,96 150,74 199,22 243,72
03.01.2013 4,96 150,74 199,22 243,72
04.01.2013 4,99 150,37 197,96 244,20
05.01.2013 4,96 150,59 195,99 241,70

и добавить фунцию, где можно указать временной период (start date: dd/mm/yyyy, end date: dd/mm/yyyy

Еще раз спасибо огромное.

Благодарю за помощь, Сергей.

Да, код должен работать примерно так. Я попробую модифицировать его, чтобы получить таблицу в формате:

и добавить фунцию, где можно указать временной период (start date: dd/mm/yyyy, end date: dd/mm/yyyy

Еще раз спасибо огромное. Guest

Да, код должен работать примерно так. Я попробую модифицировать его, чтобы получить таблицу в формате:

и добавить фунцию, где можно указать временной период (start date: dd/mm/yyyy, end date: dd/mm/yyyy

Еще раз спасибо огромное. Автор - Guest
Дата добавления - 07.11.2014 в 06:45

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

HTML код посмотрел, нашел вот эти строки где используется "rate":

td width="25" background="images/back_rates.jpg"
td valign="top" background="images/back_rates.jpg"
td height="35" colspan="6" background="images/back_rates.jpg" width="100%"

Можете показать строку, откуда собирается дата?

Хотелось бы выгрузить историю по всем валютам на одном листе. Немножко видоизменил ваш код:

[vba][code]Private Sub CommandButton1_Click()
Dim Id As String, edate As String

Но почему-то перезаписывает все столбцы .

HTML код посмотрел, нашел вот эти строки где используется "rate":

td width="25" background="images/back_rates.jpg"
td valign="top" background="images/back_rates.jpg"
td height="35" colspan="6" background="images/back_rates.jpg" width="100%"

Можете показать строку, откуда собирается дата?

Хотелось бы выгрузить историю по всем валютам на одном листе. Немножко видоизменил ваш код:

[vba][code]Private Sub CommandButton1_Click()
Dim Id As String, edate As String

Но почему-то перезаписывает все столбцы . Guest

HTML код посмотрел, нашел вот эти строки где используется "rate":

td width="25" background="images/back_rates.jpg"
td valign="top" background="images/back_rates.jpg"
td height="35" colspan="6" background="images/back_rates.jpg" width="100%"

Можете показать строку, откуда собирается дата?

Хотелось бы выгрузить историю по всем валютам на одном листе. Немножко видоизменил ваш код:

[vba][code]Private Sub CommandButton1_Click()
Dim Id As String, edate As String

Но почему-то перезаписывает все столбцы . Автор - Guest
Дата добавления - 11.11.2014 в 13:55

Автор - doober
Дата добавления - 11.11.2014 в 16:07

Ах . оказывается, там всего 40 валютных пар и есть пустые интервалы в порядке ID валют (например, отсутствуют 11, 19, 24, 27-38, 43, 47-49) .
За счет этого образовываются пустые колонны. Заметил я это когда запускал код, вследствие чего вышла ошибка "run time error 9 subscript out of range"

Объясните, пожалуйста, как вы Header ("1 АВСТРАЛИЙСКИЙ ДОЛЛАР", "1 ФУНТ СТЕРЛИНГОВ СОЕДИНЕННОГО КОРОЛЕВСТВА" . ) распарсировали? Я бы хотел применить такой же подход для получния Header-ов типа AUD/KZT, GBP/KZT и т. д. (например, строка align="center">AUD / KZT).

Порекомендуйте, пжлста, ресурсы по разборке HTML и парсинг сайтов в VBA.

Порекомендуйте, пжлста, ресурсы по разборке HTML и парсинг сайтов в VBA. Guest

Порекомендуйте, пжлста, ресурсы по разборке HTML и парсинг сайтов в VBA. Автор - Guest
Дата добавления - 12.11.2014 в 08:55

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Аннотация

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

Дополнительные сведения

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

Перенос ячейки данных по ячейке
Передача данных в массиве в диапазон ячеек
Передача данных в наборе записей ADO в диапазон ячеек с помощью метода CopyFromRecordset
Создание таблицы QueryTable на Excel, которая содержит результат запроса в источнике данных ODBC или OLEDB
Передача данных в буфер обмена, а затем вклейка содержимого буфера обмена в Excel таблицу

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

Передача данных в текстовый файл с запятой или запятой, который можно Excel позже разбора в ячейки на таблице
Передача данных на таблицу с помощью ADO
Передача данных в Excel с помощью динамических Exchange данных (DDE)

В следующих разделах подробно по каждому из этих решений.

Примечание При использовании Microsoft Office Excel 2007 года можно использовать новый формат файла Excel 2007 (*.xlsx) при сохранения книг. Для этого найдите следующую строку кода в следующих примерах кода:

Замените этот код следующей строкой кода:

Кроме того, база данных Northwind не включена в Office 2007 г. по умолчанию. Однако вы можете скачать базу данных Northwind из Microsoft Office Online.

Использование автоматизации для передачи ячейки данных ячейкой

С помощью автоматизации можно передавать данные на одну ячейку одновременно:

Перенос ячейки данных по ячейкам может быть вполне приемлемым подходом, если объем данных невелик. Вы можете разместить данные в любой точке книги и можете условно отформатировать ячейки во время запуска. Однако этот подход не рекомендуется, если у вас есть большой объем данных для передачи в Excel книгу. Каждый объект Range, приобретаемый во время запуска, приводит к запросу интерфейса, чтобы перенос данных таким образом был медленным. Кроме того, microsoft Windows 95 и Windows 98 имеют ограничение в 64K для запросов интерфейса. Если вы достигнете или превысите это ограничение в 64k для запросов интерфейса, сервер автоматизации (Excel) может перестать отвечать или вы можете получить ошибки, указывающие на низкую память.

Еще раз, перенос ячейки данных по ячейкам приемлем только для небольших объемов данных. Если необходимо перенести большие наборы данных в Excel, следует рассмотреть одно из решений, представленных позже.

Дополнительные примеры кода для автоматизации Excel см. в Microsoft Excel Visual Basic.

Автоматизация для передачи массива данных в диапазон на таблице

Массив данных может быть передан сразу нескольким ячейкам:

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

Эта строка представляет два запроса интерфейса (один для объекта Range, возвращаемого методом Range, и другой для объекта Range, возвращаемого методом Resize). С другой стороны, для передачи ячейки данных по ячейке потребуются запросы на 300 интерфейсов в объекты Range. По возможности вы можете получать выгоду от передачи данных оптом и уменьшения количества запросов на интерфейс.

Автоматизация для передачи наборов записей ADO в диапазон таблиц

Excel 2000 г. представлен метод CopyFromRecordset, который позволяет переносить набор записей ADO (или DAO) в диапазон на таблице. В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г. и передать содержимое таблицы заказов в базе данных образцов Northwind с помощью метода CopyFromRecordset.

Примечание Если вы используете Office 2007 года в базе данных Northwind, необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Excel 97 также предоставляет метод CopyFromRecordset, но использовать его можно только с набором записей DAO. CopyFromRecordset с Excel 97 не поддерживает ADO.

Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в статьи How to transfer data from an ADO recordset to Excel с помощью автоматизации.

Автоматизация для создания queryTable на таблице

Объект QueryTable представляет таблицу, созданную из данных, возвращаемых из внешнего источника данных. При автоматизации Microsoft Excel можно создать queryTable, просто предоставив строку подключения к OLEDB или источнику данных ODBC вместе с SQL строкой. Excel берет на себя ответственность за создание наборов записей и вставку его в таблицу в заявляемом вами расположении. Использование QueryTables предоставляет ряд преимуществ по сравнению с методом CopyFromRecordset:

Excel обрабатывает создание наборов записей и его размещение в таблицу.
Запрос можно сохранить с помощью QueryTable, чтобы затем обновить его, чтобы получить обновленный набор записей.
При добавлении в таблицу нового queryTable можно указать, что данные, уже существующие в ячейках на этом компьютере, будут перенесены для размещения новых данных (подробнее см. свойство RefreshStyle).

В следующем коде показано, как можно автоматизировать Excel 2000, Excel 2002 или Office Excel 2003 г., чтобы создать новый queryTable в Excel таблице с использованием данных из базы данных образцов Northwind:

Использование буфера обмена

Буфер Windows также может использоваться в качестве механизма передачи данных на таблицу. Чтобы вклеить данные в несколько ячеек на таблицу, можно скопировать строку, в которой столбцы делимитированы символами вкладок, а строки делимитированы возвращаемой каретой. В следующем коде показано, как Visual Basic использовать объект Clipboard для передачи данных в Excel:

Создание разнонародных текстовых файлов, Excel можно разрезать на строки и столбцы

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

В следующем коде показано, как создать текстовый файл с запятой из наборов записей ADO:

Обратите внимание, что Office версии базы данных Northwind 2007 года необходимо заменить следующую строку кода в примере кода:

Замените эту строку кода следующей строкой кода:

Если в текстовом файле есть расширение .CSV, Excel открывает файл без отображения мастера импорта текста и автоматически предполагает, что файл запятой. Аналогично, если в вашем файле .TXT расширение, Excel автоматически разборите файл с помощью делимитеров вкладок.

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

Передача данных на таблицу с помощью ADO

С помощью поставщика DB Microsoft Jet OLE можно добавить записи в таблицу в существующей Excel книге. "Таблица" в Excel — это просто диапазон с определенным именем. Первая строка диапазона должна содержать заглавные (или имена полей), а все последующие строки содержат записи. Ниже показано, как создать книгу с пустой таблицей MyTable.

Excel 97, Excel 2000 и Excel 2003 г.

Запустите новую книгу в Excel.

Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:

A1: FirstName B1: LastName

Формат ячейки B1 в виде правой выровненной.

В меню Insert выберите Имена, а затем выберите Определение. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:\Book1.xls и Excel.

Чтобы добавить записи в MyTable с помощью ADO, можно использовать код, аналогичный следующему:

Excel 2007

В Excel 2007 г. запустите новую книгу.

Добавьте следующие заглавные таблицы в ячейки A1:B1 листа1:

A1: FirstName B1: LastName

Формат ячейки B1 в виде правой выровненной.

На ленте нажмите вкладку Формулы и нажмите кнопку Определить имя. Введите имя MyTable и нажмите кнопку ОК.

Сохраните новую книгу как C:\Book1.xlsx, а затем Excel.

Чтобы добавить записи в таблицу MyTable с помощью ADO, используйте код, похожий на следующий пример кода.

При добавлении записей в таблицу таким образом форматирование в книге сохраняется. В предыдущем примере новые поля, добавленные в столбец B, форматированы с правильным выравниванием. Каждая запись, добавляемая в строку, заимствует формат из строки над ней.

Следует отметить, что при добавлении записи в ячейку или ячейки в таблицу она переописывание любых данных, ранее в этих ячейках; Другими словами, строки в таблице не "сдвинуты" при добавлении новых записей. Это следует иметь в виду при разработке макета данных на ваших таблицах.

Метод обновления данных в Excel таблице с помощью ADO или с помощью DAO не работает в Visual Basic для среды приложения в access после установки Office 2003 Пакет обновления 2 (SP2) или после установки обновления для Access 2002, включенного в статью Microsoft Knowledge Base 904018. Метод хорошо работает в Visual Basic среде приложений из других Office приложений, таких как Word, Excel и Outlook.

Дополнительные сведения см. в следующей статье:

Дополнительные сведения об использовании ADO для доступа к книге Excel см. в книге How To Query and Update Excel Data Using ADO from ASP.

Использование DDE для передачи данных в Excel

DDE является альтернативой автоматизации в качестве средства для общения с Excel и передачи данных; однако с появлением автоматизации и com DDE больше не является предпочтительным методом для общения с другими приложениями и должен использоваться только в том случае, если у вас нет другого решения.

Чтобы передать данные в Excel DDE, вы можете использовать метод LinkPoke для отправки данных в определенный диапазон ячейки или метод LinkExecute для отправки команд, которые Excel будут выполняться.

В следующем примере кода показано, как установить беседу DDE с Excel, чтобы можно было втыкать данные в ячейки на таблицу и выполнять команды. С помощью этого примера для успешного запуска беседы по DDE в linkTopic Excel|MyBook.xls книга с именем MyBook.xls должна уже открываться в запущенном экземпляре Excel.

При использовании Excel 2007 можно использовать новый формат .xlsx для сохранения книг. Убедитесь, что имя файла обновляется в следующем примере кода. В этом примере Text1 представляет собой управление текстовым полем на Visual Basic форме:

При использовании LinkPoke с Excel, вы указываете диапазон в строке-столбце (R1C1) для LinkItem. Если вы подтыкаете данные к нескольким ячейкам, можно использовать строку, в которой столбцы делимитированы вкладками, а строки делимитированы возвращаемой каретой.

При использовании LinkExecute для Excel выполнения команды необходимо Excel команду в синтаксисе Excel макроса (XLM). Документация XLM не включается в Excel версии 97 и более поздней версии.
DDE не рекомендуется для общения с Excel. Автоматизация обеспечивает наибольшую гибкость и предоставляет дополнительный доступ к новым функциям, которые Excel предложить.

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

Вставка статической информации

Один из способов получить данные из веб-страницы на лист — просто выделить текст в браузере, нажать Ctrl+C, чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал — в комплекте с настройками форматирования, гиперссылками и графикой.

Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная ► Буфер обмена ► Вставить, можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная ► Буфер обмена ► Вставить ► Специальная вставка, а затем пробовать различные варианты вставки.

Вставка обновляемой информации

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

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

Рис. 176.2. Данные, импортированные из веб-страницы

По умолчанию импортированные данные — это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.

Открытие веб-страницы напрямую

Еще один способ получить данные веб-страницы на лист — открыть URL-адрес напрямую, с помощью команды Файл ► Открыть. Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть. Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.

Читайте также: