Как импортировать таблицу из excel в access

Доступ к VBA — импорт / экспорт Excel — запрос, отчет, таблица и формы

В этом руководстве будут рассмотрены способы импорта данных из Excel в таблицу Access и способы экспорта объектов Access (запросов, отчетов, таблиц или форм) в Excel.

Импортировать файл Excel в Access

Чтобы импортировать файл Excel в Access, используйте acImport вариант DoCmd.TransferSpreadsheet :

Или вы можете использовать DoCmd.TransferText чтобы импортировать файл CSV:

Импортировать Excel в функцию доступа

Эту функцию можно использовать для импорта файла Excel или CSV в таблицу доступа:

Вы можете вызвать функцию так:

Доступ к экспорту VBA в новый файл Excel

Чтобы экспортировать объект Access в новый файл Excel, используйте DoCmd.OutputTo метод или DoCmd.TransferSpreadsheet метод:

Экспорт запроса в Excel

Эта строка кода VBA экспортирует запрос в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Примечание: Этот код экспортирует в формат XLSX. Вместо этого вы можете обновить аргументы для экспорта в формат файла CSV или XLS (например, acFormatXLSX к acFormatXLS).

Экспорт отчета в Excel

Эта строка кода экспортирует отчет в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт таблицы в Excel

Эта строка кода экспортирует таблицу в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт формы в Excel

Эта строка кода экспортирует форму в Excel с помощью DoCmd.OutputTo:

Или вы можете использовать вместо этого метод DoCmd.TransferSpreadsheet:

Экспорт в функции Excel

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

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

Экспорт в существующий файл Excel

Приведенные выше примеры кода отлично подходят для экспорта объектов Access в новый файл Excel. Однако их нельзя будет экспортировать в существующую книгу.

Чтобы экспортировать объекты Access в существующую книгу Excel, мы создали следующую функцию:

Вы можете использовать эту функцию так:

Обратите внимание, что вас просят определить:

• Что выводить? Таблица, отчет, запрос или форма
• Имя объекта
• Имя выходного листа
• Путь и имя выходного файла.

Экспорт SQL-запроса в Excel

Вместо этого вы можете экспортировать SQL-запрос в Excel, используя аналогичную функцию:

Где вас просят ввести:

• SQL-запрос
• Имя выходного листа
• Путь и имя выходного файла.

Функция экспорта в новый файл Excel

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

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

Электронные таблицы очень полезный инструмент. Но в целом ряде случаев для работы со структурированной информацией лучше использовать базы данных. Попробуем разобраться с этим вопросом.

• 1. Мы перенесем (импортируем) Список Excel Книги в Access и поработаем с формами, запросами и отчетами. Формы Access создаются Мастерами буквально несколькими кликами мыши. Запросы позволяют манипулировать информацией: отбирать, преобразовывать, вычислять. Это больше и легче, чем фильтрация Excel. Отчеты Access позволяют создавать на основе информации базы данных, часто, используя запросы, выходные документы с колонтитулами, названиями, заголовками и другими атрибутами документа.
• 2. Мы научимся использовать шаблон Microsoft Access электронного каталога Biblio и модифицируем его в соответствии с нашими потребностями. Серьезные специалисты работают с текстами, делают выписки. Но нужен инструмент, который бы позволил среди этих многочисленных выписок находить полезные именно для текущей работы. Делать это можно с использованием ключевых слов. Мы сделаем такую базу данных. Легко. Действительно легко.
• 3. Модифицировать — это часто, как носить костюм, сшитый для другого человека. Можно походить и в таком, но все же приятнее, когда вещь сделана именно для тебя. То есть, если это не очень сложно для вас, было бы полезно научиться делать несложные базы данных самостоятельно. Мы обозначим некоторый минимум знаний, который позволит создавать базы данных в 3—5 таблиц с отношениями многие-ко-многим, подстановками и т.д. И научимся с ними работать.

Таблицы Google, облако, таблицы Excel — это все очень хорошо. Но мы хотим показать, что манипулировать данными о книгах много проще в Access. Предварительно определим, что

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

Мы об этом говорили, но подчеркнем еще раз: очень важно определить перечень вопросов, на которые должны быть получены ответы с помощью базы данных и задач, которые необходимо решать. Также полезно определить некоторые рамки. Например, насколько большой будет эта база данных, на каком временном интервале эта база данных будет использоваться. Мне думается, что 3—5 тыс. книг это более чем приличная библиотека. Если человек активно использует несколько сотен книг — это уже очень много. Но в каталог библиотеки может попасть в несколько раз больше книг, поскольку далеко не все заинтересовавшие вас книги станут вашим рабочим инструментом.

Наши задачи следующие.

• 1. Определить наличие той или иной книги в домашней библиотеке.
• 2. Подготовить требование для книги из библиотеки Университета (вы работали с этой книгой и в вашем каталоге есть реквизиты книги).
• 3. Составить список литературы по учебной дисциплине.
• 4. Составить список литературы по проблеме, которой вы занимаетесь.
• 5. Составить список литературы по определенной проблеме за определенный период времени.
• 6. И, может быть главное, обеспечить возможность создания выписок из изученных книг и их систематизацию по ключевым словам.

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

При исключении повторяющихся записей можно использовать один из двух подходов. Во-первых, Расширенный фильтр. Если выполнить команду Данные / Дополнительно, то появится окно Расширенный фильтр (рис. 4.1). В этом окне нужно выставить флаг Только уникальные записи.

Рис. 4.1. Фильтрация только в уникальных записях

Во-вторых, на вкладке Данные в группе Работа с данными есть команда Удалить дубликаты. После выполнения команды появится окно оповещения (рис. 4.2). [1] [2]

Рис. 4.2. Окно оповещения об удалении дубликатов записей

Рис. 43. Шаблоны баз данных Access

A/c. 4.4. Начальная страница работы с пустой базой данных

Чтобы импортировать в Access таблицу Excel из Excel, необходимо выполнить команду Внешние данные / Excel. В результате появится окно Внешние данные (рис. 4.5).

Рис. 4.5. Импорт таблицы в Access таблицы Excel

Через кнопку Обзор нужно выбрать файл для импорта данных. Это также файл Книги80. Заголовки столбцов таблицы Excel будут полями таблицы Access. Лучше, если будут удалены пробелы между словами в этих заголовках. Система, конечно, укажет на ошибки, но чтобы потом с ними не возиться, следует перед импортом внимательно просмотреть таблицу Excel. Вот что получится, если этого не сделать (рис. 4.6).

Рис. 4.6. Пример ошибки при импорте

В нашем случае просто отсутствовал один из заголовков. Хорошо, если названия будут из одного слова: ГодИздания, но не Год издания. Первое начертание упростит дальнейшие манипуляции с именами полей (их не нужно будет брать в кавычки).

Система может автоматически создать ключевое поле, поле счетчика, когда каждой записи присваивается порядковый номер (рис. 4.7). Сейчас этот вопрос для нас не является принципиальным: можно выбрать опцию автоматического создания ключа, можно ключ не создавать.

Рис. 4.7. Работа с мастером Импорта

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

Чтобы работать в режиме конструктора, необходимо вызвать Контекстное меню (рис. 4.9). Работать с ним предпочтительнее, нежели обращаться к командам ленты.

Рис. 4.8. Импортированная таблица в режиме конструктора

Импорт электронной таблицы из Excel в таблицу базы данных Access

Создайте в Excel небольшую таблицу, по структуре напоминающую базу данных и начинающуюся с ячейки А1. Сохраните ее в виде файла. Вернитесь в базу данных Access. Откройте Файл/Внешние данные/Импорт, выберите тип фалла Microsoft Excel, укажите имя файла и следуйте указаниям.

Копирование таблицы из Access в Microsoft Excel

Копирование выполняется через буфер временного хранения. Выделить записи в таблице Правка / Выделить все записи. Скопируйте выделенный фрагмент в буфер временного хранения Правка / Копировать. Перейдите в Excel и выполните вставку фрагмента Правка / Вставить.

Самостоятельная работа

Образец таблицы для самостоятельной работы

Дата изготовления

Дата поступления

Создайте новый файл базы данных, в нём указанную таблицу. Сформируйте простые запросы, в которых отобразите:

Названия товаров, которые начинаются на букву Л;

Названия товаров, которые заканчиваются на букву н;

Названия товаров, срок хранения которых более 4 месяцев;

Названия товаров и дату их изготовления, которые произведены 2007 году;

Названия товаров и дату их поступления, которые поступили в 2008 году;

Определите товары, у которых срок годности истек на момент поступления (в запросе создайте новое вычисляемое поле):

Прошло: ([Дата поступления]-[Дата изготовления])/30

В условие отбора этого поля введите >[Срок хранения]

На основании этой таблицы создайте запросы на изменение:

Уменьшите срок хранения всем товарам на 1 месяц;.

Создайте запрос на удаление записей, с артикулом, меньше 103;

Создайте новую таблицу, содержащую поля: Артикул, Название, Срок хранения. Внесите в таблицу три записи. Добавьте эти записи в исходную таблицу.

Создайте таблицу Поставщики, которая будет содержать поля: Артикул, Поставщик. Установите связь между исходной таблицей и таблицей Поставщики. Обновите артикулы товаров в главной таблице, убедитесь, что они изменились в подчиненной таблице.

Создайте форму – карточку товара.

§23. Access. Проектирование многотабличной базой данных Импорт данных, индексация таблиц, маски ввода

Запустите Access: Пуск / Программы / Microsoft Access. После этого в появившемся диалоговом окне необходимо выбрать создание Новой базы данных и нажать ОК. В следующем диалоговом окне следует указать имя и месторасположения создаваемого вами файла базы данных и нажать клавишу Создать.

Для создания базы данных «Компьютерный магазин» нам понадобятся 5 таблиц: «Клиенты», «Заказы клиентов», «Товары на складе», «Персонал», «Учет работы персонала». Таблицы «Клиенты» и «Товары на складе» импортируем из электронных таблиц.

Для этого в главном окне базы данных зайдите в меню Файл / Внешние данные / Импорт. В появившемся диалоговом окне смените тип файла на Microsoft Excel, найдите в списке нужный вам («Клиенты компьютерного магазина») и нажмите кнопку Импорт. После этого следует указать, что все импортируемые данные расположены на первом листе, и нажать кнопку Далее. Затем пометьте флажком, что в первой строке расположены заголовки полей импортируемой таблицы, нажмите Далее и укажите, что данные нужно сохранить в новой таблице.

Рис. 3.13. Импорт таблицы из Excel в Access

После этого следует проиндексировать каждое поле, указывая возможны ли в нем совпадения (совпадения возможны в любом поле, кроме ключевого. Ключевым полем считается то, которое однозначно определяет каждую запись.

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

Ключевые поля в Access индексируются автоматически и индексные файлы, создаваемые по ключевым полям таблицы называют файлами первичных индексов. Индексы, создаваемые пользователем для неключевых полей называют вторичными (пользовательскими) индексами.

Причина повышения скорости выполнения различных операций в индексированных таблицах состоит в том, что основная часть работы производится с небольшими индексированными файлами, а не с самими таблицами. Наибольший эффект повышения производительности работы достигается для значительных по объему таблиц.

Индексирование требует небольшого дополнительного места на диске и незначительных затрат процессора на изменение индексов в процессе работы. После индексирования всех полей таблицы следует определить ключ (см. рисунок). Для таблицы «Клиенты» это будет № паспорта.

Далее указывается имя таблицы, которая будет создана в результате импортирования данных. При импорте таблицы «Товары магазина» обратите внимание, что ключевых полей нет. Следовательно, при индексации всех полей необходимо указать, что совпадения допускаются, а при определении ключевого поля установить переключатель в положение «не создавать ключ». Соответственно вторая таблица после окончания операции импорта будет требовать некоторых изменений в своей структуре, которые можно внести в режиме конструктора.

С помощью конструктора таблиц добавим два новых поля: № типа Счетчик и Фото типа Поле OLE в структуру таблицы «Товары на складе». Поле № необходимо сделать ключевым. Для этого выделите его в режиме конструктора таблиц и нажмите кнопку с ключом на панели инструментов «Базы данных».

Остальные таблицы будем создавать непосредственно в СУБД Access. Для этого их структуру необходимо описать в конструкторе таблиц (см. рисунок).

При этом обратите внимание, что сначала описывается таблица «Персонал», в таблице «Учет работы» № паспорта рабочего делается средствами мастера подстановки – специального мастера, позволяющего в дальнейшем не вписывать данные в таблицу, а подставлять из созданного списка.

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

Аналогично с помощью мастера подстановок оформляются поля «№ паспорта клиента», «№ товара», «заказ оформил» в таблице «Заказы клиентов». Обратите внимание, что в случае, когда список берется из имеющееся таблицы, между задействованными таблицами необратимо возникает связь. Причем изменения в структуру связанных полей возможно вносить только после разрыва этой связи.

Рис. 3.14. Структура таблиц в базе данных

Кроме этого, для поля «Телефон» в таблице «Персонал» создадим маску ввода, то есть специальным образом подготовленную форму ввода. Для этого в конструкторе выделим поле «Телефон», а в свойствах поля выберем маску ввода. Щелкнув мышью по трем точкам в конце строки получим диалоговое окно создания маски ввода.

Рис. 3.15. Окно создания маски ввода

Здесь выберем любую маску кроме «Пароль» и нажмем кнопку Далее. В результате появится окно, позволяющее изменять маску ввода и создавать новую. В появившемся окне внесем необходимые нам изменения.

Внесем запись телефона с учетом кода города, что позволит записывать в том числе и номера сотовых телефонов.

Рис. 3.16. Изменение маски ввода

Обратите внимание, что знак «_» в нашем случае предусматривает ввод какой-то цифры и следовательно будет знаком заполнителя. После щелчка по строке «Проба:» у вас автоматически появится образец маски и имя заменится на «Определяется пользователем. Теперь можно нажать кнопку Далее. Где в появившемся диалоговом окне указать, что сохранить созданную маску нужно вместе со знаками, после чего нажимается кнопка Готово. Теперь при заполнении таблицы в поле «Телефон» у вас будет появляться соответствующая маска и пользователь не ошибется в каком виде вносить номер телефона. Самостоятельно создайте маску ввода для поля «Контактный телефон» в таблице «Клиенты».

Access VBA – Import / Export Excel – Query, Report, Table, and Forms

This tutorial will cover the ways to import data from Excel into an Access Table and ways to export Access objects (Queries, Reports, Tables, or Forms) to Excel.

Import Excel File Into Access

To import an Excel file to Access, use the acImport option of DoCmd.TransferSpreadsheet :

Or you can use DoCmd.TransferText to import a CSV file:

Import Excel to Access Function

This function can be used to import an Excel file or CSV file into an Access Table:

You can call the function like this:

Access VBA Export to New Excel File

To export an Access object to a new Excel file , use the DoCmd.OutputTo method or the DoCmd.TransferSpreadsheet method:

Export Query to Excel

This line of VBA code will export a Query to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Note: This code exports to XLSX format. Instead you can update the arguments to export to a CSV or XLS file format instead (ex. acFormatXLSX to acFormatXLS).

Export Report to Excel

This line of code will export a Report to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Export Table to Excel

This line of code will export a Table to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Export Form to Excel

This line of code will export a Form to Excel using DoCmd.OutputTo:

Or you can use the DoCmd.TransferSpreadsheet method instead:

Export to Excel Functions

These one line commands work great to export to a new Excel file. However, they will not be able to export into an existing workbook. In the section below we introduce functions that allow you to append your export to an existing Excel file.

Below that, we’ve included some additional functions to export to new Excel files, including error handling and more.

Export to Existing Excel File

The above code examples work great to export Access objects to a new Excel file. However, they will not be able to export into an existing workbook.

To export Access objects to an existing Excel workbook we’ve created the following function:

You can use the function like this:

Notice you are asked to define:

• What to Output? Table, Report, Query, or Form
• Object Name
• Output Sheet Name
• Output File Path and Name.

Export SQL Query to Excel

Instead you can export an SQL query to Excel using a similar function:

Called like this:

Where you are asked to input:

• SQL Query
• Output Sheet Name
• Output File Path and Name.

Function to Export to New Excel File

These functions allow you to export Access objects to a new Excel workbook. You might find them more useful than the simple single lines at the top of the document.

The function can be called like this:

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.