Экспорт запроса из Access в Excel vba
Обновлено: 21.11.2024
У меня около 20 запросов Select в базе данных Access. Запросы называются GBQuery01, GBQuery02, GBQuery03 и т. д.
Я хочу иметь возможность использовать VBA для запуска GBQuery01, а затем экспортировать результат в файл Excel 2010, это будет только что созданный файл с именем GBQuery01.xlsx.
Затем я хотел бы перейти к GBQuery02, запустить, экспортировать и т. д. по всем 20 запросам.
Конечным результатом будет 20 вновь созданных файлов xlsx.
Может ли кто-нибудь помочь с кодированием VBA для выполнения этого требования.
Заранее спасибо.
Факты об Excel
MrExcel MVP, младший администратор
Создайте макрос, который экспортирует ваш первый запрос в Excel (используя команду TransferSpreadsheet). Затем на ленте «Макросы» выберите «Преобразовать макросы в Visual Basic». Это даст вам код VBA, эквивалентный этому макросу. Вы можете использовать это как "кирпичики" для своего кода VBA.
Если вы разместите этот код здесь, мы поможем вам написать цикл, который вам нужен, чтобы выполнить цикл по всем 20 запросам и экспортировать их все.
бренданолеар
Активный участник
Основной перенос исходного запроса выглядит следующим образом:
Private Sub ExportExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "GBQuery01", "C:\Users\Gary\Documents\GBQuery01.xlsx", True
End Sub
С циклом или последовательным захватом после этого у меня возникают трудности.
MrExcel MVP, младший администратор
бренданолеар
Активный участник
Обычная доска
Оперные взломщики
Новый участник
Это фантастика!
Я надеюсь, что кто-нибудь сможет уточнить это со следующими отличиями:
1) Существует 46 запросов, отформатированных таким образом, что они соответственно пронумерованы от 01 до 46, за которыми следует пробел и заданное значение. имена.
2) Мне нужно, чтобы эти запросы выполнялись, а все результаты вставлялись (или изменялись) на один лист или в набор записей.
3) Экспорт набора записей в Excel и сохранение с номером партии/даты и .xlsx в указанном месте.
В основном я застрял на (2). Пункты (1) (разновидность вышеперечисленного) и (3), для которых у меня есть код. Я новичок в VBA, поэтому буду очень признателен за любые рекомендации и большое спасибо.
абсетиаван
Новый участник
С некоторыми отклонениями, это тоже помогает мне.
смикке
Новый участник
Работает нормально, но хотя у меня Office 13, Access поддерживает только Excel 97, то есть я могу вывести только 65000 строк.
Как использовать VBA для экспорта в рабочие книги Excel данных из таблиц запросов Access и для форматирования рабочих таблиц после получения данных.
Последнее обновление выполнено Дэвидом Уоллисом 27 октября 2020 г.
Предисловие
Для многих баз данных, которые я разрабатываю, я включаю возможность экспорта в Excel. Это для клиентов, которым нужны средства сброса данных, чтобы они могли заниматься своими делами без риска для первичных данных.
Довольно часто требуется экспорт сам по себе, а не тщательно созданный отчет Access, включенный в базу данных. Кроме того, в некоторых случаях экспорт служит полезной проверкой того, что собранные записи содержат именно то, что нужно клиенту, перед созданием полноценного отчета Access.
В этой статье описывается процедура VBA для сброса данных из Access в Excel с использованием метода DoCmd.TransferSpreadsheet, чтобы выйти за рамки объяснения работы этого метода, которое вы читали на многих веб-сайтах.
Первоначальные соображения
На основе многолетних требований клиентов я определил следующие основные функции возможности экспорта в Excel:
- Автоматический экспорт наборов данных из запросов
- Экспорт в рабочие книги в папках по выбору клиента.
- Автоматическое форматирование листов после завершения экспорта.
- Возможность создания пользователями собственных запросов.
Все базы данных DMW, предоставляемые клиентам, разделены — FE (передняя часть) и BE (внутренняя часть). FE содержат запросы, формы, отчеты, макросы и модули и, при необходимости, одну или две таблицы, как я объясню ниже. BE содержат только таблицы.
С точки зрения разработчика, вот мои соображения:
- Пользователям не разрешается изменять структуру BE.
- Пользователям не разрешается изменять FE
- Предоставление пользователям возможности создавать собственные запросы
- Возможность включения собственных запросов пользователей в FE
- Передайте надлежащий контроль над процессом ИТ-специалистам клиента.
Если бы пользователям было позволено возиться, для меня было бы непрактичной и трудоемкой задачей предоставлять обновления и постоянную поддержку клиенту. Клиенту не понравится счет.
Метод электронной таблицы VBA Transfer
Процедура dmwExport использует костяк метода TransferSpreadsheet для экспорта содержимого таблицы или таблицы запроса в виде именованного файла Excel в именованную папку:
Подпрограмма dmwExport(запрос$, путь$)
ИмяТаблицы:= запрос$ ,_
ИмяФайла:= путь$, _
dmwExport("qsResults", "S:\Reports\Results.XLSX") , например, экспортирует содержимое запроса qsResults в папку S:\Reports\ в виде файла Excel с именем Results.XLSX .
Открытие экспортированной книги
dmwExport создает книгу и сохраняет ее, но не отображает завершенную книгу. Опыт показывает, что большинство пользователей хотят видеть результаты экспорта сразу после его завершения.
Sub dmwExport(query$, path$)
Dim xlApp как объект, wkbk как объект
Установите xlApp = CreateObject("Excel.Application")
Установите wkbk = .Workbooks.Open(path$)
Обработка ошибок
На этом этапе мы включим обработку ошибок и удостоверимся, что процедура разрывает любое соединение с Excel после представления книги:
Sub dmwExport(query$, path$)
При ошибке Перейти к errHandler
Dim xlApp как объект, wkbk как объект
Установите xlApp = CreateObject("Excel.Application")
Установите wkbk = .Workbooks.Open(path$)
Установите wkbk = Ничего
Установите xlApp = Ничего
MsgBox msg$, vbExclamation, "Непредвиденная ошибка"
Именование путей и книг
Вы должны соблюдать осторожность при вводе значений в аргумент path$ функции dmwExport(query$, path$) . Рассмотрим эти значения:
"S:\Reports\Results.XLSX" Работает удовлетворительно — рабочая книга с именем Results.XLSX направляется в папку S:\Reports\.
"S:\Reports\Results" Это тоже работает удовлетворительно — процедура прикрепляет расширение .XLSX к имени книги, так что Results.XLSX направляется в папку S:\Reports\.
"S:\Reports\Results\" Здесь возникает проблема, заключающаяся в том, что финал \ заставляет процедуру рассматривать S:\Reports\Results\ как папку, вообще не указывая какую-либо рабочую книгу, с результирующими условиями ошибки, например —
Если в этом есть необходимость (дайте мне знать), я добавлю код для решения проблемы. Полнофункциональная программа экспорта, описанная ниже, включает такой код.
Предоставление возможности изменения папки
По моему опыту, клиенты хотят сами определять, в какую папку процесс экспорта направляет создаваемые книги. Кроме того, они хотят иметь возможность изменять эту папку, не заставляя меня настраивать какой-либо код. Это особое требование, когда ИТ-отделу клиента требуется свобода изменения расположения файлов в сети и переназначение дисков.
На протяжении многих лет я пробовал несколько способов обеспечить это, в настоящее время остановился на методе, который использует дополнение к двум основным файлам FE и BE. Этот третий файл я называю KEY.ini, это простой текстовый файл, содержание которого таково:
Этот файл нужно поместить в ту же папку, что и файл USER
Измените ExportPath, чтобы он соответствовал вашей структуре папок
Теперь нам нужно приспособить KEY.ini к процессу экспорта. Процесс должен выбрать путь экспорта из KEY.ini .
В соответствии с передовой практикой мы собираемся структурировать наше программирование, разделив наш код на несколько подпрограмм. Каждый из них будет выполнять отдельную операцию, одной из которых будет выбор из KEY.ini .
С этого момента dmwExport станет одной из подпрограмм, последовательность выполнения которых будет определяться общей управляющей процедурой.
Процедура контроля
Это последовательность подпрограмм, которые будет вызывать управляющая процедура или программа с именем dmwExportToXL:
dmwGetPath Эта подпрограмма функции VBA ищет файл KEY.ini и получает из него путь экспорта. Если dmwGetPath не может найти KEY.ini или не может найти информацию о пути экспорта, он вернет сообщение об ошибке в dmwExportToXL .
dmwCheckPath Эта подпрограмма функции VBA будет пытаться подтвердить существование папки, в которую должны быть направлены дампы. Если dmwCheckPath не может найти эту папку, он сообщит об ее отсутствии в dmwExportToXL .
dmwExport Это завершит программу экспорта, передающую данные из Access в Excel, открытие Excel для отображения данных и форматирование рабочего листа.
Это скелет программы dmwExportToXL:
Dim msg$, млрд как логическое значение
Подпрограмма получения пути из файла KEY
Заданием подпрограммы dmwGetPathFromKEY является получение пути к серверному файлу DATA из KEY.ini:
Функция dmwGetPathFromKEY(pathINI$, element$) как строка
При ошибке Перейти к обработчику ошибок
Тусклый i&, lenElement&
Тусклый fstChar34%, lstChar34%
Затемнить строкуINI$, путь$
Если Len(Dir(pathINI$)) > 0 И Len(element$) > 0 Тогда
Делать, пока не EOF(i&)
Если Left(lineINI$, lenElement&) = element$ Тогда
path$ = Mid(lineINI$, lenElement& + 1)
fstChar34% = InStr(path$, Chr(34)) + 1
lstChar34% = InStrRev(path$, Chr(34))
path$ = Mid(path$, fstChar34%, lstChar34% - fstChar34%)
Если он не может вернуть информацию о местонахождении DATA , выдается предупреждение dmwGetPathFromKEY, из которого dmwStartUp составляет сообщения для пользователя.
Подпрограмма проверки пути
Задачей второй подпрограммы, dmwCheckPath, в программе экспорта является подтверждение того, что папка, указанная в KEY.ini, действительно существует:
Функция dmwCheckPath(path$) как строка
При ошибке Перейти к обработчику ошибок
Если Dir( path$ , vbDirectory) = "." Затем
"Ни одна папка не соответствует записи в файле KEY"
dmwCheckPath возвращает нулевую строку, если папка на месте, или сообщение, если эта папка отсутствует или программа не может найти ее в ожидаемом месте.
Подпрограмма экспорта в Excel
Третья и последняя подпрограмма в программе — dmwExport. Но в отличие от версии в верхней части этой страницы, эта версия dmwExport содержит строки кода, направленные на оптимизацию содержимого рабочего листа, созданного при экспорте.
Метод TransferSpreadsheet выполняет действие TransferSpreadsheet в Visual Basic.
Синтаксис
выражение.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Диапазон, ИспользоватьOA)
выражение Переменная, представляющая объект DoCmd.
Параметры
| Имя | Обязательный/Необязательный | Тип данных | Описание |
|---|---|---|---|
| TransferType | Необязательно | AcDataTransferType | Тип переноса, который вы хотите сделать. Значение по умолчанию — acImport. |
| SpreadsheetType | Необязательно | AcSpreadSheetType | Тип электронной таблицы для импорта, экспорта или ссылку на. |
| TableName | Необязательный | Вариант | Строковое выражение, которое является именем Таблица Office Access, в которую вы хотите импортировать данные электронной таблицы, экспортировать данные электронной таблицы или связать данные электронной таблицы с ними, или запрос Access select, результаты которого вы хотите экспортировать в электронную таблицу. |
| Необязательно | Variant | Строковое выражение, представляющее собой имя файла и путь к электронной таблице, которую вы хотите импорт из, экспорт в или ссылка на. | |
| HasFieldNames | Необязательно | Вариант | Используйте True (1) использовать первую строку электронной таблицы в качестве имен полей при импорте или связывании. Используйте False (0), чтобы рассматривать первую строку электронной таблицы как обычные данные. Если оставить этот аргумент пустым, предполагается значение по умолчанию (False). Когда вы экспортируете таблицу Access или выбираете данные запроса в электронную таблицу, имена полей вставляются в первую строку электронной таблицы независимо от того, что вы вводите для этого аргумента. |
| Диапазон | Необязательно | Вариант | Строковое выражение, представляющее собой допустимый диапазон ячеек или имя диапазона в электронной таблице. Этот аргумент применим только к импорту. Оставьте этот аргумент пустым, чтобы импортировать всю электронную таблицу. При экспорте в электронную таблицу этот аргумент необходимо оставить пустым. Если вы введете диапазон, экспорт завершится ошибкой. |
| UseOA | Необязательный | Вариант | Этот аргумент не поддерживается. |
Примечания
Используйте метод TransferSpreadsheet для импорта или экспорта данных между текущей базой данных Access или проектом Access (.adp) и файлом электронной таблицы. Вы также можете связать данные в электронной таблице Excel с текущей базой данных Access. С помощью связанной электронной таблицы вы можете просматривать и редактировать данные электронной таблицы с помощью Access, сохраняя при этом полный доступ к данным из вашей программы работы с электронными таблицами Excel. Вы также можете создать ссылку на данные в файле электронной таблицы Lotus 1-2-3, но эти данные доступны только для чтения в Access.
Вы также можете использовать объекты данных ActiveX (ADO) для создания ссылки, используя свойство ActiveConnection для объекта Recordset.
Пример
В следующем примере данные из указанного диапазона электронной таблицы Lotus Newemps.wk3 импортируются в таблицу Access Employees.В качестве имен полей используется первая строка электронной таблицы.
Поддержка и обратная связь
Есть вопросы или отзывы об Office VBA или этой документации? См. раздел Поддержка и отзывы об Office VBA, чтобы узнать, как получить поддержку и оставить отзыв.
В этом примере мы будем использовать простую таблицу со списком названий пива. Наша цель — экспортировать содержимое этой таблицы в книгу Excel.
Access предлагает два способа сделать это. Если вы хотите экспортировать всю таблицу без каких-либо излишеств, Access предоставляет простой способ экспорта (а также импорта или связывания) данных Excel.
Если вам нужно использовать какую-то пользовательскую логику при экспорте, вы также можете использовать объектную модель VBA Excel. В этом руководстве вы изучите оба способа.
Тестовые данные
Как уже упоминалось, в этом руководстве мы будем использовать очень простые данные, приведенные ниже.
<Р>
Простой способ
1. Создать процедуру VBA
Microsoft Access предоставляет очень удобный метод DoCmd.TransferSpreadsheet для экспорта, импорта или связывания данных Excel. В приведенной ниже процедуре показано, как использовать этот метод для экспорта нашей простой таблицы. Создайте новый модуль VBA и вставьте код.
2. Выполнить процедуру
<Р>
Запустите процедуру и убедитесь, что файл был создан по пути, указанному в переменной excel_file_name в вашей процедуре. Вы заметите, что имена полей также были экспортированы. Это один из параметров TransferSpreadsheet, который при необходимости можно исключить.
Для более сложных случаев
1. Импорт ссылок Excel
Чтобы использовать объектную модель Excel в VBA, мы должны сначала импортировать ссылку на библиотеку объектов Excel. Это можно сделать, перейдя в Инструменты->Ссылки в меню редактора VBA.
<Р>
Прокрутите вниз до записи «Библиотека объектов Microsoft Excel» (версия Excel может отличаться), отметьте ее и нажмите «ОК».
2. Создать процедуру VBA
Несмотря на то, что название шага руководства такое же, в более сложных случаях требуется более одного метода. В приведенном ниже коде используется объектная модель VBA Excel для создания книги и записи в ячейки на листе.
3. Выполнить процедуру
Запустите процедуру и убедитесь, что файл был создан по пути, указанному в переменной Excelfilename для вашей процедуры.
Как видите, мы смогли выполнить некоторые настройки с помощью объектной модели Excel. Например, мы смогли добавить текст к каждой записи и отформатировать шрифт заголовка. По сути, все, что вы можете сделать в Excel, можно сделать и в VBA, используя объектную модель.
Читайте также: