Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel access

Как в Access на VBA выгрузить Recordset в Excel?

Всем привет! Материал сегодня будет посвящен рассмотрению возможности 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 у нас может выступать любой запрос, а не только данные формы.

На этом у меня все! Надеюсь, материал был Вам полезен, пока!

VBA Excel: РАБОТА С БАЗОЙ ДАННЫХ, МИНИ ORM НА VBA

ORM переводится, как object relational mapping (объектно-реляционное отображение). Это означает, что мы работаем с базой не на уровне SQL запросов, а на уровне объектов.

Давайте я покажу пример и как это будет работать в итоге.

Для начала скажу, что у меня есть база «SQL Server 2016» серьезная база для промышленной реализации, но не пугайтесь она так же доступна простым смертным. Где её скачать расскажу позже.

Каждый раз для использования нашей ORM необходимо объявить и инициализировать несколько переменных c типом DataBase, RecordSet, UnitOfWork. И соединить их с помощью метода Init.

Давайте объявим эти переменные.

Давайте посмотрим, как получить данные.

Для этого объявим переменную с типом User. И реализуем цикл с помощью метода GetAll . Посмотрите, как удобно получать данные о пользователях. Всё что нам надо это просто вызвать метод GetAll в свойстве UserRepository. Точно такие же репозитории будут добавляться и для других типов данных, например, некие документы или накладные InvoiceRepository. После чего в самом цикле мы переберем всех пользователи из базы и выведем их в консоль.

В цикле можно перебрать любые данные. Использовать такие методы, как GetAll очень удобно. Мы не заморачиваемся с SQL запросами и получаем объект определенного типа, свойства и методы которого можно получить и увидеть через IntelliSence. Свойства полученного объекта в нашем примере это Id, Email, UserName объект пока не содержит методов только свойства.

Теперь давайте посмотрим, как добавить запись в базу

Фактически всё что нам нужно это создать объект и задать этому объекту новые значения.

Потом добавить объект через метод “Add”, который есть у любых коллекций и зафиксировать изменения в базе через метод “uow.Commit”.

Если мы запустим наш цикл, который выводил данные мы увидим нашу новую запись.

Та же самая запись сохранилась в базе. При этом Id добавился автоматически, т.е. Id это так называемый первичный ключ в базе, который сама база добавляет автоматически.

И в этом примере добавление данных выглядит крайне удобно и вполне естественно. Создаем объект пользователь, задаем ему значения свойств и добавляем в репозиторий. Всё!

Итак, теперь подробнее про саму реализацию ORM.

Для работы с этим примером нам потребуется несколько программ. Первая программа — это SQL Server 2016. Вы сможете найти её по адресу (https://www.microsoft.com/en-us/sql-server/application-development). Также для работы нам потребуется Management studio (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms). Выберите и нажмите ссылку в зависимости от вашего языка.

Читать еще:  Как провести линию в excel

В ООП есть специальные шаблоны проектирования, чтобы другие программисты, увидев их понимали, что мы написали и как работает наша программа.

В данном случае у нас есть шаблон проектирования – репозиторий его суть — это удобное переключение баз данных, получение данных в объектном стиле. В моем примере мы работаем с SQL Server 2016, но с шаблоном проектирования репозиторий мы можем поменять базу, например, на MySql или MS Access довольно просто. При этом наша основная логика не пострадает.

Теперь, давайте посмотрим пример поподробнее. Для того, чтобы реализовать шаблон нам надо несколько классов сам репозиторий. В моем примере это – UserRepository. Класс, который объединяет репозитории это класс UnitOfWork. И классы, которые соответствуют таблицам в базе данных такие классы называют модели данных. Они же в идеале должны содержать большую часть логики (которые обычно реализуются через методы), так называемая, богатая модель. Если модель не содержит логики т.е. методов, то её принято называть ананимичная модель т.е. бедная. В моем простом примере нет пока логики, поэтому модель «User» пока можно отнести к ананимичной модели данных.

И так давайте посмотрим на модель User. В классе добавлено три псевдосвойства Id, Email, UserName. У меня есть отдельное видео рассказывающее про эти свойства. Те же самые поля у меня определенны в базе данных. В Management Studio я создал простую базу TestDb и добавил таблицу User. В исходниках у Вас будет SQL файл с запросом на создание этой таблицы. Просто запустите его в своей Management Studio.

Либо в Management Studio добавьте таблицу, нажав правую кнопку -> New -> Table. И добавьте поля Id, Email, UserName. Для Id добавьте тип данных bigint, для Email nvarchar(50), для UserName nvarchar(50).

Потом нажмите правую кнопку мышки по полю Id и выберите “Set primarity key” (добавить). Это тот самый первичный ключ, который автоматически увеличивается при каждом добавлении данных в базу.

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

UnitOfWork

Давайте теперь посмотрим класс UnitOfWork

Данный класс в основном должен содержать только репозитории и метод Commit для сохранения данных в базе. И может содержать несколько вспомогательных членов.

В моем примере я добавил метод Init, чтобы через этот метод можно было внедрить объекты типа DataBase и Recordset. Т.к. в стандартном конструкторе на VBA нет возможности передать параметры. Такой механизм передачи объектов извне класса называется внедрение зависимостей.

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

Репозиторий (UserRepository)

Сам репозиторий довольно простой для начала я добавил три публичных свойства, чтобы был доступ к самой базе SQL Server через объект типа DataBase. Добавил свойство RecordSet для работы с ним внутри метода GetAll. В принципе сам класс UserRepositroy можно запускать независимо, но для реализации шаблона я инициализирую репозиторий в классе UnitOfWork.

И далее два основных метода. Метод GetAll и метод Add. Метод GetAll просто получает все записи из базы данных и переводит их в объект коллекцию типа User c помощью встроенного объекта RecordSet. Внутри метода я запускаю SQL запрос на выборку нужных мне полей. А далее через объект RecorSet получаю их.

Метод Add добавляет объект типа User в глобальную переменную LocalUsers с ней мы будем работать через класс UnitOfWork, чтобы сохранить данные в базе.

Сводная из базы данных Access

Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах. Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к. хранить такое количество данных в книгах Excel не совсем правильно. Поэтому даже если у вас есть несколько книг/листов, забитых нужными данными по полной и надо эти данные объединить для дальнейшего анализа сводной таблицей — то самое лучшее на мой взгляд решение, это объединить их через MS Access в одну таблицу и потом уже построить сводную на основании таблицы не составит труда.

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

  • все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
  • заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов — оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
  • если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать — убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
  • необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек
Читать еще:  Interop excel dll

Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New)Новая база данных (Blank database) . Указываете имя базы и месторасположение(папку):

После создания базы проходим несколько шагов:

  1. переходим на вкладку Внешние данные (External Data) -группа Импорт и связи (Import & Link)Excel.
  2. Выбираем файл, данные из которого необходимо перенести в Access
  3. Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database) :
  4. на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges) :

    я выбрал лист «products», т.к. именно так у меня называется лист с данными. Жмем Далее (Next)
  5. на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет — устанавливаем
  6. жмем Готово (Finish)

Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг. Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table) . Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
Все, теперь можно приступать к созданию сводной таблицы.

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

  • Excel 2010Файл (File)Параметры (Options)Панель быстрого доступа (Quick Access Toolbar)
  • Excel 2007Кнопка офисПараметры Excel (Excel options)Панель быстрого доступа (Quick Access Toolbar)

или непосредственно с панели быстрого доступа:

Выбрать команды из: Все команды (All Commands) . Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source) :

на втором шаге жмем кнопку Получить данные (Get Data) :

В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.

Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.

Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

Подтверждаем выбор нажатием кнопки ОК.
Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»

Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой » (Return Data to Microsoft Excel) и жмем Готово. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные должна появиться надпись Данные получены (Data fields have been retrieved) :

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

Либо на имеющийся лист(придется указать ячейку, в которой будет размещаться первая строка данных), либо на новый лист.
Жмем Готово (Finish) .
Все, наша сводная готова к работе и теперь вы можете обрабатывать свои миллионы записей быстро и легко в привычном интерфейсе самой сводной.

Статья помогла? Поделись ссылкой с друзьями!

ШКОЛА ПРОГРАММИРОВАНИЯ

Вы здесь

Сообщение об ошибке

Импорт и Экспорт данных из mdb (Access) в Excel на VBA

Программные продукты MS Access и MS Excel относятся к одному пакету MS Office, но из-за лицензионных ограничений, не на все рабочие станции может быть установлен Access. Может возникнуть такая ситуация, что сотруднику, который работает только с Excel, потребуются некоторые данные, которые содержатся в базе Access. Как быть? Можно установить копию Access, но т.к. эта надобность может быть разовой или очень редкой, то приобретение лицензии экономически невыгодно. Можно попросить разработчика mdb создать отчет, который бы экспортировался в Excel. А можно, зная структуру таблиц БД Access, написать небольшой макрос (а можно и большой) который бы импортировал данные в книгу Excel и обрабатывал их особым образом. Есть еще один способ, это использовать инструменты Excel — «Импорт внешних данных«, но о нем в других статьях. А пока рассмотрим пример на VBA.

Для импорта/экспорта будем использовать библиотеку MS DAO 3.6 Object Library, которая поставляется вместе с VBA. Включите ее в новом проекте. Для этого в редакторе VBA (Alt+F11) откройте Tools — References, найдите в списке «Microsoft DAO 3.6 Object Library» и поставьте галочку.

Например, у нас есть некая база данных комплектующих к ПК, прайс лист проще говоря. Таблица называется «tbl_прайс» и имеет следующую структуру:

  • ID — поле типа счетчик;
  • Вид — поле типа «Текст (String)» с длинной 50 символов. Содержит принадлежность к виду комплектующих (Процессор, Материнка, ОЗУ и т.д.);
  • Производитель — тип текст, длина 50;
  • Модель — содержит номер и краткие характеристики модели. Поле так же, текст, длина 255;
  • Количество — поле типа «Числовой», Размер — «Длинное целое». Содержит кол-во комплектующих на складе;
  • Цена — поле типа «Числовой», Размер — «Действительное». Указывает цену за единицу товара.
Читать еще:  Функция find vba excel

Можете создать и наполнить данными базу mdb, а можете взять используемую базу в примерах ниже здесь.

Итак, база есть, например, нам необходимо полностью прочитать таблицу БД («tbl_прайс») и вывести результат на лист Excel. Cоздаем новый модуль и добавляем в него процедуру следующего содержания:

Sub ReadMDB()
‘переменная хранящая результат запроса
Dim tbl As Recordset
‘строка запроса SQL
Dim SQLr As String
‘переменная хранящая ссылку на подключенную БД
Dim dbs As Database

‘подключаемся к mdb
Set dbs = DAO.OpenDatabase(«E:price.mdb»)

‘составляем строку SQL запроса
SQLr = «SELECT * FROM tbl_прайс»

‘отправляем запрос открытой БД
‘результат в виде таблицы сохранен в tbl
Set tbl = dbs.OpenRecordset(SQLr)

‘вставляем результат в лист начиная с ячейки A1
Cells(1, 1).CopyFromRecordset tbl

‘Закрываем временную таблицу
tbl.Close

‘Очищаем память. Если этого не сделать, то таблица
‘так и останется висеть в оперативке.
Set tbl = Nothing

‘Закрываем базу
dbs.Close
Set dbs = Nothing
End Sub

Логика работы этой и всех последующих процедур чтения(записи) данных в БД проста. Сначала мы открываем БД, затем отправляем SQL запрос, получаем результат запроса в виде таблицы, закрываем БД, освобождаем память.

В данном варианте мы использовали метод CopyFromRecordset ячейки листа т.е. вставили результат запроса в лист так как есть, но что делать если результат нужно еще обработать некоторым образом который невозможно описать в запросе!? Ниже код демонстрирует построчное чтение результата запроса в цикле Do While (как работает цикл Do While описано в этой статье):

Sub ReadMDB_построчно()

Dim tbl As Recordset
Dim SQLr As String
Dim dbs As Database
Dim i As Integer

Set dbs = DAO.OpenDatabase(«E:price.mdb»)

SQLr = «SELECT * FROM tbl_прайс»
Set tbl = dbs.OpenRecordset(SQLr)
i = 1

‘выполняем цикл пока не конец tbl
Do While Not tbl.EOF
‘присваиваем каждой ячейке значение из полей таблицы
Cells(i, 1) = tbl.Fields(«ID»)
Cells(i, 2) = tbl.Fields(«Вид»)
Cells(i, 3) = tbl.Fields(«Производитель»)
Cells(i, 4) = tbl.Fields(«Модель»)
Cells(i, 5) = tbl.Fields(«Количество»)
Cells(i, 6) = tbl.Fields(«Цена»)
‘и для примера получим сумму (цена*кол-во)
Cells(i, 7) = tbl.Fields(«Количество») * tbl.Fields(«Цена»)

i = i + 1
tbl.MoveNext ‘переход к следующей записи

Loop

tbl.Close
Set tbl = Nothing

dbs.Close
Set dbs = Nothing
End Sub

Обратите внимание, второй вариант выводит результат на лист заметно медленнее, чем первый! Поэтому рекомендую по возможности использовать первый вариант.

Метод OpenRecordset позволяет только считывать данные из таблиц БД с помощью запросов. Для того чтобы выполнить запросы на изменение, добавление или удаление записей в таблицах используется метод Execute. Смотрим пример, который позволяет добавить запись в таблицу (при соответствующем SQL запросе можно изменить, удалить записи):

Sub ReadMDB_добавить_запись()

Dim tbl As Recordset
Dim SQLr As String
Dim dbs As Database
Dim kol As Long

Set dbs = DAO.OpenDatabase(«E:price.mdb»)

Set tbl = dbs.OpenRecordset(«tbl_прайс»)
‘метод RecordCount позволяет получить кол-во записей
‘Kol хранит ID для новой записи
kol = tbl.RecordCount + 1

SQLr = «INSERT INTO tbl_прайс (ID,Вид,Производитель, Модель,Количество, Цена)» _
& «Values (» & kol & «,’ОЗУ’,’Hyndai’, ‘DDR3’, 123, 600)»

tbl.Close
Set tbl = Nothing

dbs.Close
Set dbs = Nothing
End Sub

В этих примерах показаны основные моменты работы с БД mdb, которые помогут организовать обмен данными между Excel и Access, но эти способы не являются единственно верными и правильными. На этом все. До встреч!

Работа с БД Access через VBA Excel

Суть проекта: необходим макрос в Excel, который бы добавлял/изменял строки в таблице Access.
Пока что получается только добавлять новые данные в таблицу с помощью следующего кода (найдено на просторах гугла):

Код открывает базу данных, находит нужную таблицу и вставляет данные с листа Excel в соответствующие поля в Access.

Теперь мне, например, нужно найти какую-то запись с определенным значением в поле «ФИО» и внести изменения в найденную строчку (например, изменить номер договора).

Подскажите, пожалуйста, как это сделать?

Импорт таблицы Excel в БД Access через VBA через кнопку в Excel
Форумчане, помогите, пожалуйста с кодом макроса в excel. Задача состоит в следующем:в файле.

Подключение к БД access через VBA excel
Private Sub CommandButton1_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset.

Сохранение Excel-файла через макрос VBA в Access
Добрый день. полная информация есть здесь:https://www.cyberforum.ru/ms-access/thread1786317.html.

Как установить пароль в Access через VBA Excel?
Доброго времени суток! Есть Excel’евская программы которая подключается к БД Access, все работает.

Решение

Все, теперь работает. Огромное спасибо!
Приятно знать, что в 2018 форумы все так же актуальны и полезны

Добавлено через 36 минут
mobile, еще один мелкий нюанс. Как можно обновить окно Access после исполнения скрипта? Пробовал что-то типа db.SendKeys (««), выдает ошибку, мол, «объект не поддерживает данное свойство или метод».

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Найти нужную запись в таблице Access’а через VBA Excel
Народ, никак не получается. И как если нашел сделать ее текущей. Поделитесь куском кода.

Работа с Excel через VBA
Уважаемые формучане, я столкнулась с небольшой проблемой. Сейчас я дорабатываю проект, и не могу.

Работа с комментарием ячейки Excel через VBA
Здравствуйте. Прошу вашей помощи, уважаемые эксперты. Сейчас пилю таблицу учета рабочей.

Автоматичесская работа в Excel через Access
Здравствуйте! Очень прошу помощи! Существует база. с помощью запросов создаются отчёты, которые.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×