Green-sell.info

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

Access экспорт vba

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

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

Канал в Telegram

Вы здесь

Импорт и Экспорт данных из 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;
  • Количество — поле типа «Числовой», Размер — «Длинное целое». Содержит кол-во комплектующих на складе;
  • Цена — поле типа «Числовой», Размер — «Действительное». Указывает цену за единицу товара.
Читать еще:  Access возраст по дате рождения

Можете создать и наполнить данными базу 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, но эти способы не являются единственно верными и правильными. На этом все. До встреч!

Экспорт данных в Excel

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

В этой статье

Экспорт данных в Excel: основы

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

Стандартные сценарии экспорта данных в Excel

В отделе или рабочей группе для работы с данными используется как Access, так и Excel. Данные хранятся в базах данных Access, но для анализа и распространения его результатов используется Excel. Группа выполняет экспорт данных в Excel по мере необходимости, но этот процесс требуется сделать более эффективным.

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

Об экспорте данных в Excel

В приложении Access нет команды «Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

Читать еще:  C работа с базой данных access

Можно экспортировать таблицу, запрос, форму или отчет, а также отдельные записи в представлении.

В приложении Microsoft Excel есть команда импорта данных из базы данных Access. Ее можно использовать вместо команды экспорта Access, однако с помощью команды импорта в Excel можно импортировать только таблицы и запросы. Дополнительные сведения см. в разделе справки Excel Подключение к внешним данных и их импорт.

Экспортировать макросы и модули нельзя. При экспорте формы, отчета или таблицы с подчиненными формами, отчетами или таблицами экспортируется только главная форма, отчет или таблица. Все подчиненные формы, отчеты и таблицы следует экспортировать в Excel отдельно.

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

Подготовка к экспорту

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

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

Access экспорт vba

Метод ADODB recordset
Общее описание:
Очень быстрый и мощный.
Особенности: вы должны определить x и y координаты верхней левой ячейки, и в переменные n и m, переданные по ссылке вы получаете высоту и ширину полученного диапазона. Установите значение переменной Headers равной True, если вам нужны в заголовки столбцов.
Этот метод — ошибко-независимый — ошибки игнорируется.
Детали этого решения — ADODB recordset — позволяет вернуть значения полей записи запроса и поместить их в массив, который затем транспонируется и выводится в MS Excel Range .
Требования:
Требуются ссылки на библиотеку MS Excel object library (необязательно, — используется, толко для проверки синтаксиса. Вы можете не устанавиливать ссылку на Excel, описав переменную WS как Object) также требуется ссылка на библиотеку ActiveX Data Objects Library
Преимущества:
Быстрый, универсальный, надежный.
Недостатки:
Этот метод весьма замедлен необходимостью транспонировать матрицу, полученную методом getrows. К сожалению, getrows помещает значения в транспонированном виде. Если этого удастся избежать каким либо способом, скорость значительно увеличится.
Code:

———————————————————————————
Public Function TXLOut (sql As String, Optional WS As Worksheet = Nothing, Optional ByRef x As Long = 1, Optional ByRef y As Long = 1, Optional ByRef n As Long = 1, Optional ByRef m As Long = 1, Optional Headers As Boolean = True) As Worksheet
‘Turbo Version
‘Notice, that you need References to ActiveX Data Objects Library and Microsoft Excel Objects Library
Dim a As Variant
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim c() As Variant
Dim i, j, l, k As Integer

ReDim c(UBound(a, 2), UBound(a, 1))

‘ Here comes matrix transposition
For k = 0 To UBound(a, 1)
For j = 0 To UBound(a, 2)
c(j, k) = a(k, j)
Next j
Next k

n = UBound(a, 2) + 1
m = UBound(a, 1) + 1

WS.Range(WS.Cells(y, x), WS.Cells(n + y — 1, m + x — 1)) = c

‘Here columns headers are put if necessary
If Headers Then
WS.Range(WS.Cells(y, x), WS.Cells(n + y — 1, m + x — 1)).rows(1).Insert
For j = 0 To m — 1
WS.Cells(y, j + x).Value = rs.Fields(j).Name
Next j
End If

Метод Copyfromrecordset
Общее описание:
Это встроенный метод Excel для получения значений из recordset на рабочий лист.
Требования: библиотека объектов MS Excel
Перимущества:
Простой. Данные могут быть помещены в любом месте страницы
Недостатки:
В Excel 97, метод принимает в качестве аргумента только DAO recordset. Как я уже упоминал, DAO recordset имеет очень неприятный дефект — при любой ошибке он обрезает данные до места ошибки, не выводя никаких сообщений об ошибке. Поэтому, если вы собираетесь использовать этот метод, вы должны проверять recordset на наличие ошибок перед или после вывода. Excel более поздних версий поддерживает ADO recordsets, который не содержит этого дефекта.

Set rs = CurrentDb.OpenRecordset(sql)
WS.Cells(3,2).CopyFromRecordset rs

Использование ADO + Clipboard
Общее описание:
При разработке этого метода, я думал, это — курьез, не более. Однако, полученные результаты показали, что это неожиданно хороший метод для небольшого (<500) количества записей.
Требуются: ссылки на библиотеку MS Excel object library (необязательно, — нужно только, чтобы иметь правильный синтаксис. Вы можете не устанавливать ссылку на EXCEL, описав переменную WS как Object), библиотеку ActiveX Data Objects Library и MSForms Object library.
Метод объединяет возможности ADO recordset, и MSForms Data Object. DataObject дает возможность взаимодействовать с буфером обмена (Clipboard). Мы заполняем буфер обмена строкой, где значения полей разделены CHR (9) и строки CHR (10), затем выполняем Paste. Есть способы ускорить эту процедуру, например использовать не DataObject, а API. Другой путь — использовать не заданный по умолчанию текстовый формат в SetText, а помещать в буфер обмена массив, что позволит на составлять строку.
Преимущества:
Быстро.
Недостатки:
Требуется 3 библиотеки. «умирает», если размер данных превышает 2 КБ (ограничения буфера обмена Windows).

Читать еще:  Project web access

———————————————————————————
Public Function CXLOut(sql As String, Optional WS As Worksheet = Nothing, Optional ByRef x As Long = 1, Optional ByRef y As Long = 1, Optional ByRef n As Long = 1, Optional ByRef m As Long = 1, Optional Headers As Boolean = True) As Worksheet
‘Clipboard version
Dim a As Variant
Dim rs As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim ors As Recordset
‘Dim l, i, j As Integer
Dim c As Variant
Dim i, j, l, k As Integer
Dim dum As String
Dim ddo As New MSForms.DataObject

dum = «»
Do
dum = dum + CStr(rs(0))
For i = 1 To rs.Fields.Count — 1
dum = dum + Chr(9) + CStr(Nz(rs(i)))
Next i
dum = dum + Chr(10)
j = j + 1
rs.MoveNext
Loop While Not rs.EOF

n = j
m = rs.Fields.Count

ddo.SetText (dum)
ddo.PutInClipboard
WS.Cells(1, 1).Activate
WS.Paste
‘WS.Range(WS.Cells(y, x), WS.Cells(n + y — 1, m + x — 1)) = Trans(a)

If Headers Then
WS.Range(WS.Cells(y, x), WS.Cells(n + y — 1, m + x — 1)).rows(1).Insert
For j = 0 To m — 1
WS.Cells(y, j + x).Value = rs.Fields(j).Name
Next j
End If

Exit Function
whoops:
Resume Next
End Function

Использование RunCommand + Clipboard
Общее описание:
Один из моих первых экспериментов в той области. Худший вариант из всех
Требования: сохраненный запрос, Microsoft excel object library (Optional)
Недостатки:
Медленно и во время выполнения вы ничего не можете делать.
Code:

Экспорт данных в Excel

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

В этой статье

Экспорт данных в Excel: основы

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

Стандартные сценарии экспорта данных в Excel

В отделе или рабочей группе для работы с данными используется как Access, так и Excel. Данные хранятся в базах данных Access, но для анализа и распространения его результатов используется Excel. Группа выполняет экспорт данных в Excel по мере необходимости, но этот процесс требуется сделать более эффективным.

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

Об экспорте данных в Excel

В приложении Access нет команды «Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

Можно экспортировать таблицу, запрос, форму или отчет, а также отдельные записи в представлении.

В приложении Microsoft Excel есть команда импорта данных из базы данных Access. Ее можно использовать вместо команды экспорта Access, однако с помощью команды импорта в Excel можно импортировать только таблицы и запросы. Дополнительные сведения см. в разделе справки Excel Подключение к внешним данных и их импорт.

Экспортировать макросы и модули нельзя. При экспорте формы, отчета или таблицы с подчиненными формами, отчетами или таблицами экспортируется только главная форма, отчет или таблица. Все подчиненные формы, отчеты и таблицы следует экспортировать в Excel отдельно.

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

Подготовка к экспорту

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

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

Ссылка на основную публикацию
Adblock
detector