Экспорт запроса из access в excel vba
Всем привет, сегодня мы поговорим о том, как можно выгрузить данные из Access в такие приложения как Word и Excel. Но не о стандартном способе, который есть в Access (связь с Office), а о способе, который позволяет выгружать данные в заданный шаблон как в Word, так и в Excel.
Другими словами, это нужно тогда, когда создать отчет в Access по шаблону, который уже существует, например, в Word, невозможно или слишком трудоемко. Как Вы знаете, отчет в Access может выводиться просто коряво или, самый распространенный вариант, это когда много текста, который в отчете Access не так хорошо форматируется как в Word, а данных не так много, но отчет необходимо автоматизировать, например это какие-то договора, заявления и так далее.
Использование слияния из самого Word-а не очень удобно, поэтому сегодня я расскажу, как можно заполнять такие шаблоны напрямую из Access, путем нажатия на одну кнопку.
Кроме выгрузки в шаблон Word, иногда возникает и необходимость выгрузки в шаблон Excel, и этот способ мы тоже сегодня рассмотрим.
Экспорт данных из Access в шаблон Word
Вся разработка делится на две части, это:
- Настройка шаблона Word;
- Настройка выгрузки данных в шаблон.
Суть настройки шаблона заключается в том, чтобы проставить необходимые поля в тех местах шаблона, где нужно выводить те или иные данные. Это делается с помощью полей формы.
Примечание! Я использую Microsoft Word 2003.
Открываем шаблон Word, для начала добавим необходимую панель инструментов, для этого нажимаем «Вид -> Панель инструментов» и ставим галочку «Формы». Теперь у Вас отобразилась панель инструментом «Формы». Все, что осталось сделать — это вставить в местах, в которых необходимо выводить данные, элементы «Текстовое поле», которые доступны на только что добавленной панели инструментов.
После добавления поля, у Вас появится серая область, которая свидетельствует о том, что поле добавлено. Теперь необходимо задать имя этого поля, для того чтобы потом из access вставлять в него значения (стандартное названия не очень удобное). Для этого щелкните правой кнопкой мыши по полю и нажмите «Свойства». В поле закладка напишите желаемое имя этого поля, я в примере назвал его MyTestPole.
Создайте столько полей, сколько Вам нужно.
На этом настройка шаблона закончена, рекомендую сделать этот шаблон только для чтения, а то пользователь возьмет, сформирует документ и сохранит его, и шаблон тем самым потеряется, а если сделать его только для чтения, то у него такой возможности не будет, только сохранять через «Сохранить как».
Переходим к более интересной задачи, это к реализации самой выгрузки из Access в этот шаблон на VBA.
Примечание! Я использую Access в связке с MS SQL 2008, поэтому и данные буду брать от туда.
Код VBA для выгрузки данных в шаблон Word
Допустим, у Вас есть форма, сделайте на ней кнопку (я назвал ее testbutton) и в событие нажатие кнопки вставьте следующий код VBA:
Код прокомментирован, поэтому сложностей возникнуть не должно. Здесь весь смысл сводится к созданию объекта word.document и word.application. А после мы уже работаем с нашими объектами, т.е. заполняем их.
Экспорт данных из Access в шаблон Excel
В шаблоне Excel уже не нужно создавать поля как в Word, так как здесь мы уже будем ориентироваться по адресам ячеек.
Существует несколько способов, как заполнять Excel шаблон, я опишу два, первый — это тогда, когда Вам просто необходимо проставить несколько полей, т.е. в источнике данных будет всего одна строка с несколькими столбцами. Второй — это когда строк будет уже несколько, причем Вы не знаете, сколько именно (в зависимости от каких то условий). В шаблоне по умолчанию отведено для этого все пару строк, поэтому мы будем нужные нам строки добавлять, для того чтобы наши данные не накладывалась на строки ниже (допустим там примечание, подпись руководителя и т.д.). И совет, я здесь, для примера, использую всего один источник данных, а Вы, если Вам необходимо заполнить шапку, примечание и некое количество строк (т.е. область данных), можете использовать несколько источников (Recordset).
Код VBA для выгрузки данных в шаблон Excel
Сначала добавьте кнопку на форму (я ее назвал testexcel) и вставьте следующий код в событие «Нажатие кнопки».
Здесь я также все подробно прокомментировал, но если есть вопросы, то задавайте их в комментариях к данной статье.
Для сведения, я здесь при создании объекта и Word.Application и Excel.Application использовал позднее связывание, для того чтобы не добавлять необходимые библиотеки и обеспечить совместимость.
Метод TransferSpreadsheet выполняет действие TransferSpreadsheet в Visual Basic.
Синтаксис
выражение.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
выражение: переменная, представляющая объект DoCmd.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
TransferType | Необязательный | AcDataTransferType | Нужный тип переноса. Значение по умолчанию — acImport. |
SpreadsheetType | Необязательный | AcSpreadSheetType | Тип электронной таблицы для импорта, экспорта или связи. |
TableName | Необязательный | Variant | Строковое выражение, являющееся именем таблицы Office Access, предназначенной для импорта данных электронной таблицы, экспорта данных электронной таблицы или связывания данных электронной таблицы, или запрос на выборку Access, результаты которого нужно экспортировать в электронную таблицу. |
FileName | Необязательный | Variant | Строковое выражение, являющееся именем и путем электронной таблицы для импорта, экспорта или связывания. |
HasFieldNames | Необязательный | Variant | Используйте значение True (1), чтобы использовать первую строку электронной таблицы в качестве имен полей при импорте или связывании. Используйте значение False (0), чтобы считать первую строку электронной таблицы обычными данными. Если оставить этот аргумент пустым, предполагается, что используется значение по умолчанию (False). При экспорте таблицы или данных запроса на выборку Access в электронную таблицу имена полей записываются в первую строку электронной таблицы независимо от введенного значения этого аргумента. |
Range | Необязательный | Variant | Строковое выражение, являющееся допустимым диапазоном ячеек или именем диапазона в электронной таблице. Этот аргумент применяется только для импорта. Чтобы импортировать электронную таблицу целиком, оставьте этот аргумент пустым. При экспорте в электронную таблицу необходимо оставить этот аргумент пустым. Если ввести диапазон, экспорт завершится сбоем. |
UseOA | Необязательный | Variant | Этот аргумент не поддерживается. |
Примечания
Используйте метод TransferSpreadsheet для импорта или экспорта данных между текущей базой данных Access или проектом Access (ADP) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Excel с текущей базой данных Access. Это позволит просматривать и изменять данные электронной таблицы с помощью Access, при этом не теряя возможность полного доступа к ним в Excel. Кроме того, вы можете связать данные в файле электронной таблицы Lotus 1-2-3, но они будут доступны в Access только для чтения.
Также можно использовать объекты данных ActiveX (ADO) для создания связи с помощью свойства ActiveConnection для объекта Recordset.
Пример
В следующем примере импортируются данные из указанного диапазона электронной таблицы Lotus Newemps.wk3 в таблицу Employees (Сотрудники) Access. В качестве имен полей используется первая строка электронной таблицы.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Макрокоманду ИмпортЭкспортЭлектроннойТаблицы можно использовать для импорта или экспорта данных между текущей базой данных Access (MDB или ACCDB) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Excel с текущей базой данных Access. Это позволит просматривать данные через Access, при этом не теряя возможность работы с ними в Excel. Кроме того, вы можете связать данные в файле электронной таблицы Lotus 1-2-3, но они также будут доступны в Access только для чтения.
Примечание: В Access 2010 макрокоманда ПреобразоватьЭлектроннуюТаблицу была переименована в ИмпортЭкспортЭлектроннойТаблицы.
Примечание: Эта макрокоманда доступна только для доверенных баз данных.
Настройка
Макрокоманда ИмпортЭкспортЭлектроннойТаблицы имеет следующие аргументы:
Аргумент макрокоманды
Тип преобразования
Нужный тип преобразования. В поле Тип преобразования в разделе Аргументы макрокоманды в окне конструктора макросов можно выбрать значение Импорт, Экспорт или Связь. Значение по умолчанию — Импорт.
Примечание: Тип преобразования Связь не поддерживается для проектов Access (ADP).
Тип электронной таблицы
Тип электронной таблицы для импорта, экспорта или связи. В этом поле можно выбрать один из типов электронной таблицы. Значение по умолчанию — Книга Excel.
Примечание: Можно импортировать данные из WK4-файлов Lotus или связать их (с доступом только для чтения), но нельзя экспортировать данные Access в этот формат. Кроме того, Access больше не поддерживает импорт, экспорт и связывание данных с помощью этой макрокоманды для WKS-файлов Lotus и электронных таблиц Excel версии 2.0. Если вы хотите импортировать или связать данные электронной таблицы в формате Excel версии 2.0 или Lotus WKS, необходимо преобразовать данные электронной таблицы в формат более поздней версии Excel или Lotus 1-2-3, прежде чем импортировать или связывать их.
Имя таблицы
Имя таблицы Access для импорта, экспорта или связывания данных электронной таблицы. Вы также можете ввести имя запроса на выборку Access, из которого следует экспортировать данные. Это обязательный аргумент.
Если для аргумента Тип преобразования выбрано значение Импорт, Access добавит данные электронной таблицы в указанную таблицу, если она уже существует. В противном случае Access создает новую таблицу для данных.
При использовании макрокоманды ИмпортЭкспортЭлектроннойТаблицы невозможно задать данные для экспорта с помощью инструкции SQL. Вместо этого необходимо сначала создать запрос, а затем указать имя запроса в аргументе Имя таблицы.
Имя файла электронной таблицы для импорта, экспорта или связи. Следует указывать полный путь. Это обязательный аргумент.
При экспорте данных из Access создается новая электронная таблица. Если имя файла совпадает с именем существующей электронной таблицы, Access заменяет ее, кроме случая экспорта в книгу Excel версии 5.0 или более поздней. В этом случае Access копирует экспортируемые данные в следующий доступный новый лист в книге.
При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно указать отдельный лист с помощью аргумента Диапазон.
С именами полей
Указывает, должна ли первая строка электронной таблицы содержать имена полей. Если выбрано значение Да, Access использует имена в этой строке как имена полей в таблице Access при импорте или связывании электронной таблицы. Если выбрано значение Нет, Access обрабатывает первую строку как обычную строку данных. По умолчанию используется значение Нет.
При экспорте таблицы или запроса на выборку Access в электронную таблицу имена полей записываются в первую строку электронной таблицы независимо от значения этого аргумента.
Диапазон ячеек для импорта или связи. Чтобы импортировать или связать электронную таблицу целиком, оставьте этот аргумент пустым. Вы можете ввести имя диапазона в электронной таблице или указать диапазон ячеек для импорта или связи, например А1:Е25 (обратите внимание, что синтаксис А1..Е25 не работает в Access 97 и более поздних версиях). При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно добавить имя листа и восклицательный знак перед диапазоном; например: Бюджет!А1:В7.
Примечание: При экспорте в электронную таблицу необходимо оставить этот аргумент пустым. Если задать диапазон, экспорт завершится сбоем.
Примечания
Вы можете экспортировать данные запросов на выборку Access в электронные таблицы. Access экспортирует результирующий набор запроса, обрабатывая его так же, как таблицу.
Данные электронной таблицы, которые добавляются в существующую таблицу Access, должны соответствовать ее структуре.
Каждое поле в электронной таблице должно иметь тот же тип данных, что и соответствующее поле в таблице Access.
Поля должны следовать в том же порядке (за исключением случая, когда для аргумента С именами полей задано значение Да: в этом случае названия полей в электронной таблице должны совпадать с именами полей в таблице Access).
Вызов данной макрокоманды аналогичен выбору Excel в группе Импорт или Экспорт на вкладке Внешние данные. Вы можете использовать эти команды, чтобы выбрать источник данных (базу данных Access или другого типа, электронную таблицу или текстовый файл). Если выбрана электронная таблица, на экране последовательно появляются диалоговые окна или запускается мастер Access, в котором можно указать имя электронной таблицы и другие параметры. Аргументы макрокоманды ИмпортЭкспортЭлектроннойТаблицы отражают параметры, заданные в этих диалоговых окнах или мастерах.
Примечание: В запросах и фильтрах для связанной электронной таблицы учитывается регистр.
При связывании с электронной таблицей Excel, открытой в режиме редактирования, Access будет ждать окончания работы электронной таблицы Excel в этом режиме, чтобы завершить связывание; при этом нет ограничений по времени.
Чтобы выполнить макрокоманду ИмпортЭкспортЭлектроннойТаблицы в модуле Visual Basic для приложений (VBA), используйте метод TransferSpreadsheet объекта DoCmd.
Макрокоманду ИмпортЭкспортЭлектроннойТаблицы можно использовать для импорта или экспорта данных между текущей базой данных Access (MDB или ACCDB) или проектом Access (ADP) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Microsoft Excel с текущей базой данных Microsoft Access. Это позволит просматривать и изменять данные электронной таблицы с помощью Access, при этом не теряя возможность полного доступа к ним в Excel. Кроме того, вы можете связать данные в файле электронной таблицы Lotus 1-2-3, но они будут доступны в Access только для чтения.
Эта макрокоманда доступна только для доверенных баз данных.
Параметры
Макрокоманда ПреобразоватьЭлектроннуюТаблицу имеет указанные ниже аргументы.
Тип преобразования
Нужный тип преобразования. В поле Тип преобразования в разделе Аргументы макрокоманды можно выбрать значение Импорт, Экспорт или Связь. Значение по умолчанию: Импорт.
ПРИМЕЧАНИЕ. Тип преобразования Связь не поддерживается для проектов Access (ADP).
Тип электронной таблицы
Тип электронной таблицы для импорта, экспорта или связи. В этом поле можно выбрать один из типов электронной таблицы. Значение по умолчанию: Книга Excel.
ПРИМЕЧАНИЕ. Можно импортировать данные из WK4-файлов Lotus или связать их (с доступом только для чтения), но нельзя экспортировать данные Access в этот формат. Кроме того, Access больше не поддерживает импорт, экспорт и связывание данных с помощью этой макрокоманды для WKS-файлов Lotus и электронных таблиц Excel версии 2.0. Если вы хотите импортировать или связать данные электронной таблицы в формате Excel версии 2.0 или Lotus WKS, необходимо преобразовать данные электронной таблицы в формат более поздней версии Excel или Lotus 1-2-3, прежде чем импортировать или связывать их.
Имя таблицы
Имя таблицы Access для импорта, экспорта или связывания данных электронной таблицы. Вы также можете ввести имя запроса на выборку Access, из которого следует экспортировать данные. Обязательный аргумент. Если для аргумента Тип преобразования выбрано значение Импорт, Access добавит данные электронной таблицы в указанную таблицу, если она уже существует. В противном случае Access создает новую таблицу для данных. При использовании в Access макрокоманды ИмпортЭкспортЭлектроннойТаблицы невозможно указать данные для экспорта с помощью инструкции SQL. Вместо этого необходимо сначала создать запрос, а затем указать имя запроса в аргументе Имя таблицы.
Имя файла
Имя файла электронной таблицы для импорта, экспорта или связи. Следует указывать полный путь. Обязательный аргумент. При экспорте данных из Access создается новая электронная таблица. Если имя файла совпадает с именем существующей электронной таблицы, Access заменяет ее, кроме случая экспорта в книгу Excel версии 5.0 или более поздней. В этом случае Access копирует экспортируемые данные в следующий доступный новый лист в книге. При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно указать отдельный лист с помощью аргумента Диапазон.
С именами полей
Указывает, должна ли первая строка электронной таблицы содержать имена полей. Если выбрано значение Да, Access использует имена в этой строке как имена полей в таблице Access при импорте или связывании электронной таблицы. Если выбрано значение Нет, Access обрабатывает первую строку как обычную строку данных. По умолчанию используется значение Нет. При экспорте таблицы или запроса на выборку Access в электронную таблицу имена полей записываются в первую строку электронной таблицы независимо от значения этого аргумента.
Диапазон
Диапазон ячеек для импорта или связи. Чтобы импортировать или связать электронную таблицу целиком, оставьте этот аргумент пустым. Вы можете ввести имя диапазона в электронной таблице или указать диапазон ячеек для импорта или связи, например А1:Е25 (обратите внимание, что синтаксис А1..Е25 не работает в Access 97 и более поздних версиях). При импорте или связывании электронной таблицы Excel версии 5.0 или более поздней можно добавить имя листа и восклицательный знак перед диапазоном, например Бюджет!А1:C7.
ПРИМЕЧАНИЕ. При экспорте в электронную таблицу необходимо оставить этот аргумент пустым. Если ввести диапазон, экспорт завершится сбоем.
Примечания
Вы можете экспортировать данные запросов на выборку Access в электронные таблицы. Access экспортирует результирующий набор запроса так же, как таблицу.
Данные электронной таблицы, которые добавляются в существующую таблицу Access, должны соответствовать ее структуре.
Каждое поле в электронной таблице должно иметь тот же тип данных, что и соответствующее поле в таблице Access.
Поля должны следовать в том же порядке (за исключением случая, когда для аргумента С именами полей установлено значение Да: в этом случае названия полей в электронной таблице должны совпадать с именами полей в таблице Access).
Вызов данной макрокоманды аналогичен выбору Excel в группе Импорт или Экспорт на вкладке Внешние данные, а также выбору Дополнительно в группе Импорт или Экспорт с последующим щелчком варианта Файл Lotus 1-2-3. Вы можете использовать эти команды, чтобы выбрать источник данных, например базу данных Access или другого типа, электронную таблицу или текстовый файл. Если выбрана электронная таблица, на экране последовательно появляются диалоговые окна или запускается мастер Access, в котором можно указать имя электронной таблицы и другие параметры. Аргументы макрокоманды ИмпортЭкспортЭлектроннойТаблицы отражают параметры, заданные в этих диалоговых окнах или мастерах.
В запросах и фильтрах для связанной электронной таблицы учитывается регистр.
При связывании с электронной таблицей Excel, открытой в режиме редактирования, Access будет ждать окончания работы электронной таблицы Excel в этом режиме, чтобы завершить связывание; при этом нет ограничений по времени.
Чтобы выполнить макрокоманду ИмпортЭкспортЭлектроннойТаблицы в модуле Visual Basic для приложений (VBA), используйте метод TransferSpreadsheet объекта DoCmd.
Всем привет! Материал сегодня будет посвящен рассмотрению возможности Access выгружать Recordset в Excel на VBA. Данный способ достаточно простой и предполагает использование Recordset как формы, так и специально созданного объекта.
Ранее мы с Вами уже рассматривали возможность выгрузки данных из Access в Excel в материале «Выгрузка данных из Access в шаблон Word и Excel», но там мы использовали специально созданный шаблон, что не совсем удобно, если например, нам необходимо выгрузить просто набор данных с заголовками полей.
Также если кого интересует, недавно мы рассматривали возможность выгрузки данных из базы MS SQL Server в формат CSV (текстовый файл с разделителями) с помощью VBA Access в материале «Экспорт данных в CSV файл из Microsoft SQL Server, используя Access 2003».
Сейчас я покажу простой пример реализации возможности выгружать наборы данных с заголовками из базы MS SQL Server средствами VBA Access в Excel.
Весь смысл сводится в использование метода CopyFromRecordset, который позволяет переносить Recordset в Excel в том виде, какой он и есть, т.е. в виде таблицы. Единственное что нам необходимо будет сделать, это выгрузить заголовки, чтобы было понятней, что за данные содержатся в той или иной колонке.
Итак, давайте приступать и для начала рассмотрим исходные данные.
Примечание! В качестве примера источником данных у меня будет выступать MS SQL Server 2012 Express, а в качестве клиента ADP проект Access 2003. Также на компьютере клиенте установлен Microsoft Office 2010.
Исходные данные
Допустим, на сервере у нас есть таблица TestTable.
И она содержит следующие данные.
Также допустим, что в ADP проекте Access у нас есть форма, источником данных которой выступает наша тестовая таблица TestTable.
Код VBA для выгрузки Recordset формы в Excel
Сначала давайте рассмотрим пример выгрузки объекта Recordset формы в Excel. Для этого добавляем на форму кнопку, для примера я ее назвал RSExportInExcel. В обработку события нажатие кнопки вставляем следующий код, я его прокомментировал:
Сохраняем изменения и пробуем нажать на кнопку. В итоге у нас запустится Excel, а в нем будут необходимые нам данные.
Примечание! Свойства HorizontalAlignment и VerticalAlignment могут не работать, если на компьютере не установлен Microsoft Office 2010, поэтому в случае возникновения ошибок связанных с этими свойствами просто закомментируйте их.
Код VBA для выгрузки объекта Recordset в Excel
Теперь давайте напишем код, который позволяет выгружать объект Recordset, данные которого получены, скажем с помощью запроса к базе данных. Для этого добавьте еще одну кнопку (я ее назвал RSExportInExcel2) и вставьте немного модифицированный код:
Снова сохраняем и пробуем нажать на кнопку, в итоге у нас получится точно такая же выгрузка, как и чуть ранее, только сейчас в качестве источника данных Recordset у нас может выступать любой запрос, а не только данные формы.
Читайте также: