Экспорт данных из access в excel

Импорт данных из Access в Excel

Известно, что в Excel можно создавать таблицы и работать с ними. Однако, часто возникает необходимость загрузить готовую таблицу из другого источника данных. Давайте рассмотрим, как можно в Excel загрузить данные из файла Access.

Предположим, мы имеем такую базу данных Access:

Чтобы загрузить данные, откроем пустой файл Excel, выберем в меню Данные — Получить внешние данные из Access.

В появившемся окне, выберем необходимый файл Access. Далее, появится следующее окно:

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

Теперь мы получили таблицу в 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.

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

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

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

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

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

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

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

Выгрузка данных из Access в шаблон Word и Excel

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

Здесь я также все подробно прокомментировал, но если есть вопросы, то задавайте их в комментариях к данной статье.

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

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

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

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

Номер ответа: 1
Автор ответа:
AndreyMp

Номер ответа: 2
Автор ответа:
vito

Разработчик Offline Client

Вот пример из хелпа.

Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim qdfNew As QueryDef

Set dbsNorthwind = OpenDatabase(«Northwind.mdb&quot

With dbsNorthwind
‘ Create temporary QueryDef.
Set qdfTemp = .CreateQueryDef(«», _
«SELECT * FROM Employees&quot
‘ Open Recordset and print report.
GetrstTemp qdfTemp
‘ Create permanent QueryDef.
Set qdfNew = .CreateQueryDef(«NewQueryDef», _
«SELECT * FROM Categories&quot
‘ Open Recordset and print report.
GetrstTemp qdfNew
‘ Delete new QueryDef because this is a demonstration.
.QueryDefs.Delete qdfNew. Name
.Close
End With

Function GetrstTemp(qdfTemp As QueryDef)

Dim rstTemp As Recordset

With qdfTemp
Debug . Print . Name
Debug . Print » » & .SQL
‘ Open Recordset from QueryDef.
Set rstTemp = .OpenRecordset(dbOpenSnapshot)

With rstTemp
‘ Populate Recordset and print number of records.
.MoveLast
Debug . Print » Number of records = » & _
.RecordCount
Debug . Print
.Close
End With

Номер ответа: 3
Автор ответа:
A n t o n i n a

Андрей, буду очень признательна, если поделишься своими замечательными процедурками.

Номер ответа: 4
Автор ответа:
AndreyMp

Номер ответа: 5
Автор ответа:
AndreyMp

Номер ответа: 6
Автор ответа:
A n t o n i n a

Номер ответа: 7
Автор ответа:
AndreyMp

Номер ответа: 8
Автор ответа:
A n t o n i n a

Номер ответа: 9
Автор ответа:
HACKER

Разработчик Offline Client

Ну и как вариант через ADO, а принцип с экселем тот же.

Public Function Export2XL(InitRow As Long , DBAccess As String , DBTable As String ) As Long ‘+
‘ Экспортирует с базы в Exel
Dim cn As New ADODB.Connection ‘Use for the connection string
Dim cmd As New ADODB.Command ‘Use for the command for the DB
Dim rs As New ADODB.Recordset ‘Recordset return from the DB
Dim MyIndex As Integer ‘Used for Index
Dim MyRecordCount As Long ‘Store the number of record on the table
Dim MyFieldCount As Integer ‘Store the number of fields or column
Dim ApExcel As Object ‘To open Excel
Dim MyCol As String
Dim Response As Integer

Set ApExcel = CreateObject(«Excel.application&quot ‘Creates an object

ApExcel.Visible = True ‘This enable you to see the process in Excel
ApExcel.Workbooks.Add ‘Adds a new book.

‘Set the connection string
cn.ConnectionString = «Provider=Microsoft.Jet.OLEDB.4.0ata Source=» & DBAccess
‘Open the connection
cn. Open

‘Check that the connection is open
If cn.State = 0 Then cn. Open
Set cmd.ActiveConnection = cn
cmd.CommandText = DBTable
cmd.CommandType = adCmdTable
Set rs = cmd.Execute
‘Count the number of fields or column
MyFieldCount = rs.Fields.Count

‘Fill the first line with the name of the fields
For MyIndex = 0 To MyFieldCount — 1
ApExcel.Cells(InitRow, (MyIndex + 1)).Formula = rs.Fields(MyIndex). Name ‘Write Title to a Cell
ApExcel.Cells(InitRow, (MyIndex + 1)).Font.Bold = True
ApExcel.Cells(InitRow, (MyIndex + 1)).interior.colorindex = 36
ApExcel.Cells(InitRow, (MyIndex + 1)).WrapText = True
Next

‘Draw border on the title line
MyCol = Chr((64 + MyIndex)) & InitRow
ApExcel.Range(«A» & InitRow & «:» & MyCol).Borders.Color = RGB(0, 0, 0)
MyRecordCount = 1 + InitRow

‘Fill the excel book with the values from the database
Do While rs.EOF = False
For MyIndex = 1 To MyFieldCount
ApExcel.Cells(MyRecordCount, MyIndex).Formula = rs((MyIndex — 1)).Value ‘Write Value to a Cell
ApExcel.Cells(MyRecordCount, MyIndex).WrapText = False ‘Format the Cell
Next
MyRecordCount = MyRecordCount + 1
rs.MoveNext
If MyRecordCount > 50 Then
Exit Do
End If
Loop

‘Close the connection with the DB
rs.Close

‘Return the last position in the workbook
Export2XL = MyRecordCount
End Function