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

Опубликовал Deys в ср, 16/10/2013 - 22:23

Версия для печати

Программные продукты 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" и поставьте галочку.

библиотека MS DAO 3.6 VBA

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

ID - поле типа счетчик;

Вид - поле типа "Текст (String)" с длинной 50 символов. Содержит принадлежность к виду комплектующих (Процессор, Материнка, ОЗУ и т.д.);

Производитель - тип текст, длина 50;

Модель - содержит номер и краткие характеристики модели. Поле так же, текст, длина 255;

Количество - поле типа "Числовой", Размер - "Длинное целое". Содержит кол-во комплектующих на складе;

Цена - поле типа "Числовой", Размер - "Действительное". Указывает цену за единицу товара.

Можете создать и наполнить данными базу 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)"

dbs.Execute SQLr

tbl.Close

Set tbl = Nothing

dbs.Close

Set dbs = Nothing

End Sub

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

Прикрепленный файл: Чтение mdb на VBA.zip