Выгрузка в файл excel в vba

Обновлено: 06.07.2024

Копирование данных с сохранением формата исходных ячеек
Доброго времени суток. Совсем не разбираюсь в написании макросов, пользуюсь похожими. .

Копирование листа excel с сохранением формата ячеек
Добрый день! Копирую лист с одного excel файла в другой вот таким способом: .

"Текст по столбцам" или "Данные - импорт внешних данных - из текста" и пройти всё, что предложит мастер. "Текст по столбцам" или "Данные - импорт внешних данных - из текста" и пройти всё, что предложит мастер. а программно можно как-то реализовать? или будет слишком много забот?
На крайний случай: можно ли как-то программно хотя бы открыть этот файл и для него запустить импорт? Можно - включаете рекордер, производите импорт, выключаете рекордер - получаете решение программно. Если не знаете полный путь файла csv, открываете его и пишете вот такой код:

Рекомендую также вот такую процедуру перед копированием:

где рейндж - это столбик с датами. Если файлы csv хоть немного отличаются, с датами может начаться расколбас - дойдете до Ивана Грозного. где рейндж - это столбик с датами. Если файлы csv хоть немного отличаются, с датами может начаться расколбас - дойдете до Ивана Грозного.

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

Добавлено через 1 час 33 минуты

В части из макрорекордера, где идет импорт, подскажите что добавить, чтоб новый лист создавался каждый раз?

Тогда засуньте все, что выше написано, в цикл, и добавьте:

Добавлено через 9 минут
чо то вы наколбасили. все проще.
Идем в папку, где лежат csv. Открываем один из них. Узнаем путь:

Потом пишем цикл в книге, куда копировать:

Что-то я уже совсем запуталась((

Добавлено через 11 минут

Путь я буду знать, он задается параметром в книге, в которую копируются листы (в ней же и запускается макрос)ю Здесь косяк в том, что я не буду знать окончательное имя файла. Название будет заканчиваться на число от 1 до 31 (дни месяца), при чем 1 это 01, к примеру.

Добавлено через 1 час 15 минут
поправка: не отдельными листами,а все в один лист

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 предложить.

Версия этой статьи для Microsoft Visual Basic 6,0 приведена в статье 247412.

Обзор

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

С помощью автоматизации вы можете использовать различные методы для переноса данных:

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

Для переноса данных без автоматизации можно использовать следующие подходы:

Способ

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

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

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

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

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

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

Этот код представляет два запроса интерфейса: один для объекта Range, возвращаемого методом Range, и другой для объекта Range, который возвращает метод Resize. В отличие от переноса ячейки данных по ячейке, необходимо запросить интерфейсы 300 для объектов Range. Если это возможно, вы можете воспользоваться преимуществами для массового переноса данных и уменьшения количества запросов к интерфейсу.

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

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

Объектные модели для Excel 2000, Excel 2002 и Excel 2003 предоставляют метод Копифромрекордсет для переноса набора записей ADO в диапазон листа. В приведенном ниже коде показано, как автоматизировать Excel для переноса содержимого таблицы Orders в образце базы данных Northwind с помощью метода Копифромрекордсет:

Использование автоматизации для создания объекта QueryTable на листе

Объект QueryTable представляет таблицу, созданную на основе данных, возвращаемых из внешнего источника данных. При автоматизации Excel можно создать QueryTable, предоставив строку подключения к OLE DB или источнику данных ODBC, а также строку SQL. Excel создает набор записей и вставляет набор записей на лист в указанном расположении. Объекты QueryTable имеют следующие преимущества по сравнению с методом Копифромрекордсет:

  • Excel обрабатывает создание набора записей и его расположение на листе.
  • Вы можете сохранить запрос с помощью объекта QueryTable и обновить его позже, чтобы получить обновленный набор записей.
  • Когда на лист добавляется новый QueryTable, вы можете указать, что данные, которые уже существуют в ячейках листа, будут сдвинуты для обработки новых данных (Дополнительные сведения см. в свойстве Рефрешстиле).

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

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

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

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

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

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

Приведенный ниже код добавляет две новые записи в таблицу в Book7. xls. В этом случае таблицей является Лист1:

Для получения дополнительных сведений об использовании поставщика OLEDB для Jet с источниками данных Excel щелкните номера статей ниже, чтобы просмотреть статьи базы знаний Майкрософт:

278973 пример: в ексцеладо показано, как использовать ADO для чтения и записи данных в книгах Excel

257819 практическое руководство: использование ADO с данными Excel из Visual Basic или VBA

Передача XML-данных (Excel 2002 и Excel 2003)

Excel 2002 и 2003 могут открыть любой XML-файл с правильным форматом. XML-файлы можно открыть непосредственно с помощью команды открыть в меню файл или программным путем с помощью методов Open и OpenXML коллекции книги. Если вы создаете XML-файлы для использования в Excel, вы также можете создать таблицы стилей для форматирования данных.

Создание новой папки с именем К:\ексцелдата. В этом примере программа будет хранить книги Excel в этой папке.

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

  1. Создайте новую книгу в Excel.
  2. На листе Sheet1 новой книги введите FirstName в ячейке a1 и LastName в ячейке B1.
  3. Выберите a1: B1.
  4. В меню Вставка выберите пункт имя, а затем — команду определить. Введите имя MyTable и нажмите кнопку ОК.
  5. Сохранение книги в виде C:\Exceldata\Book7.xls.
  6. Закройте Excel.

Добавьте ссылку на библиотеку объектов Excel и основную сборку взаимодействия ADODB. Для этого выполните следующие действия:

  1. On the Project menu, click Add Reference.
  2. На вкладке Сеть найдите ADODB и нажмите кнопку Выбрать.

Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.
3. На вкладке COM найдите объектная Библиотека Microsoft Excel 10,0 или библиотека объектов Microsoft Excel 11,0, а затем нажмите кнопку Выбрать.

Обратите внимание, что в Visual Studio 2005 нет необходимости щелкать кнопку выбрать.

Примечание Если вы используете Microsoft Excel 2002, а вы еще не сделали это, корпорация Майкрософт рекомендует скачать и установить основные сборки взаимодействия Microsoft Office XP (PIA).

В диалоговом окне Добавление ссылок нажмите кнопку ОК, чтобы принять выбранные параметры.

Добавление элемента управления "поле со списком" и элемента управления "Кнопка" в форму Form1.

Добавьте обработчики событий для события загрузки формы и событий Click элемента управления Button:

  1. В представлении конструктора для Form1.cs дважды щелкните элемент Form1.

Обработчик события Load для формы создан и отображается в Form1.cs.
2. В меню Вид выберите конструктор, чтобы переключиться в режим конструктора.
3. Дважды щелкните элемент Button1.

Обработчик события нажатия кнопки создается и отображается в Form1.cs.

В Form1.cs замените приведенный ниже код.

Добавьте следующие директивы using в директивы using в Form1.cs:

Нажмите клавишу F5 для сборки и запуска примера.

Ссылки

Для получения дополнительных сведений посетите следующий веб-сайт Майкрософт:

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

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

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

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

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

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

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

216400 Автоматизация COM может привести к зависанию клиентского приложения в Win 95/98 (Эта ссылка может указывать на содержимое полностью или частично на английском языке).

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

Примеры сценариев для автоматизации Excel см. в следующей статье базы знаний Майкрософт:
219151 Использование Visual Basic для автоматизации Microsoft Excel

Передача массива данных в диапазон ячеек листа с помощью программирования объектов

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

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

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

Перенос набора записей ADO в диапазон листа с помощью автоматизации

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

Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:

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

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

Дополнительные сведения об использовании ADO и метода CopyFromRecordset см. в следующей статье базы знаний Майкрософт:
246335 Использование программирования объектов для передачи данных из набора записей в Excel
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Создание объекта QueryTable с помощью программирования объектов

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

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

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

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

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

Ниже приведен сценарий, иллюстрирующий создание текстового файла с разделителями-запятыми из набора записей ADO:

Примечание. При использовании версии базы данных "Борей" для Office 2007 необходимо заменить в примере следующую строку кода:

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

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

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

Дополнительные сведения об операциях ввода-вывода файлов из приложения Visual Basic см. в следующей статье базы знаний Майкрософт:
172267 RECEDIT.VBP демонстрирует ввод-вывод файлов в Visual Basic recedit.rar
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)

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

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

Excel 97, Excel 2000 и Excel 2003

1. Откройте новую книгу Excel.
2. Добавьте следующие заголовки в ячейки A1:B1 листа Sheet1:
A1: FirstName B1: LastName
3. Выровняйте ячейку B1 по правому краю.
4. Выделите A1:B1.
5. В меню Вставка выберите Имя, а затем Присвоить. Введите имя MyTable и нажмите кнопку OK.
6. Сохраните новую книгу как C:\Book1.xls и закройте Excel.

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

Excel 2007

1. В Excel 2007 создайте книгу.
2. Добавьте следующие заголовки в ячейки A1:B1 листа "Лист1":
A1: FirstName B1: LastName
3. Выровняйте ячейку B1 по правому краю.
4. Выделите диапазон A1:B1.
5. На ленте откройте вкладку Формулы и выберите пункт Определить имя. Введите имя MyTable и нажмите кнопку ОК.
6. Сохраните новую книгу как C:\Book1.xlsx и закройте Excel.

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

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

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

Примечание. Обновление данных на листе Excel с помощью ADO или DAO невозможно в среде Visual Basic для приложений в Access после установки пакета обновления 2 (SP2) для Office 2003 или обновления для Access 2002, описанного в статье 904018 базы знаний Майкрософт. Однако этот способ можно использовать в среде Visual Basic для приложений в других приложениях Office, например в Word, Excel и Outlook. Дополнительные сведения см. в следующих статьях базы знаний Майкрософт:
904953 Запрещается вносить изменения, добавлять или удалять данные, источником которых являются книги Excel в Office Access 2003 или в Access 2002
904018 Описание обновления для Access 2002: от 18 октября 2005 г.

Дополнительные сведения об использовании ADO для доступа к книгам Excel см. в следующих статьях базы знаний Майкрософт:
195951 Создание запросов и обновление данных Excel с помощью ADO со страниц ASP
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Передача данных в Excel с помощью DDE

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

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

Примечание. При использовании Excel 2007 для сохранения книг можно использовать новый формат файла (XLSX). Обязательно обновите имя файла в приведенном ниже примере кода.

Примечание. В данном примере Text1 представляет элемент управления Text Box формы Visual Basic:

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

Если метод LinkExecute используется для выполнения команды в Excel, синтаксис команды должен соответствовать языку Excel Macro Language (XLM). Документация по XLM не входит в состав Excel 97 и более поздних версий. Дополнительные сведения о получении документации по XLM см. в следующей статье базы знаний Майкрософт:

143466 Файл Macro97.exe доступен для загрузки. macro97.rar
(Эта ссылка может указывать на содержимое полностью или частично на английском языке)

DDE не является рекомендуемым способом связи с Excel. Программирование объектов предоставляет больше возможностей и обеспечивает лучший доступ к новым функциям Excel.

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