Vba импорт данных из access в access: Метод DoCmd.TransferDatabase (Access) | Microsoft Learn

Макрокоманда «ИмпортЭкспортДанных»

Макрокоманду ИмпортЭкспортДанных можно использовать для импорта или экспорта данных между текущей базой данных Access (MDB или ACCDB) или проектом Access (ADP) и другой базой данных. С классическими базами данных Access можно также связать таблицу из другой базы данных. Связь таблицы дает возможность получать доступ к данным в ней, хотя сама таблица остается в другой базе данных.

Примечание: В Access 2010 макрокоманда ПреобразоватьБазуДанных была переименована в ИмпортЭкспортДанных.

Примечание: Эта макрокоманда доступна только для доверенных баз данных.

Параметры

Макрокоманда ИмпортЭкспортДанных имеет следующие аргументы:

Примечания

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

При импорте связанной таблицы из другой базы данных Access (MDB или ACCDB) эта таблица по-прежнему остается связанной. То есть импортируется связь, а не сама таблица.

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

Макрокоманда ИмпортЭкспортДанных аналогична командам на вкладке Внешние данные в разделе Импорт или Экспорт. Вы можете использовать эти команды, чтобы выбрать источник данных (базу данных Access или другого типа, электронную таблицу или текстовый файл). При выборе базы данных появляется одно или несколько диалоговых окон, в которых можно выбрать тип объекта для импорта или экспорта (для баз данных Access), имя объекта и другие параметры, определяемые базой данных, которая используется для импорта, экспорта или связи. Аргументы макрокоманды ИмпортЭкспортДанных отражают параметры, указанные в этих диалоговых окнах.

Перед добавлением индексной информации в связанную таблицу dBASE ее необходимо связать:

1. На вкладке Внешние данные в группе Импорт щелкните Дополнительно.

2. Выберите Файл dBASE.

3. В диалоговом окне Внешние данные введите путь к файлу dBASE в поле Имя файла.

4. Выберите пункт Создать связанную таблицу для связи с источником данных и нажмите кнопку ОК.

5. Укажите индексы в диалоговых окнах этой команды. Access хранит данные об индексе в специальном INF-файле, который находится в папке Microsoft Office.

6. Теперь можно удалить ссылку на связанную таблицу.

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

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

Чтобы выполнить макрокоманду ИмпортЭкспортДанных в модуле Visual Basic для приложений (VBA), используйте метод TransferDatabase объекта DoCmd.

Импорт Excel таблиц в Access (VBA) Из Access

Вопрос следующий: в Access импортируются таблицы Excel с помощью обычного SELECT’a из листа —

INSERT INTO (Поля Access) SELECT Поля Excel FROM [Excel 12. 0 xml;HDR=Yes;IMEX=1;DATABASE=" & currentFilePath & "]

Проблемы возникают, когда в Excel файле попадаются столбцы с нестандартными именами, содержащими в себе, например, точки.
Каким образом можно перед импортом открыть файл, c помощью REPLACE заменить точки на пробелы, и импортировать из уже отредактированного файла, не сохраняя его, чтобы не испортить исходник? Методы типа DoCmd.TransferSpreadsheet и «поячеечного» чтения не подходят, поскольку таблица содержит несколько десятков столбцов и около полумиллиона строк, что делает эти методы не совсем подходящими из-за времени выполнения.

А как после импорта для всех названий полей в таблице символ # по-быстрому на что-то иное заменить?

CurrentDb.TableDefs(Table).Fields("Field#").Name=Replace(CurrentDb.TableDefs(Table).Fields("Field#").Name, "#", "")

или вот так:

CurrentDb. TableDefs(Table).Fields(I).Name=Replace(CurrentDb.TableDefs(Table).Fields(I).Name, "#", "")

Спс, отлично сработало.
Для вар-тов кучи полей в исходных эксельках самое то.

Sub ReplaceCharInTableName(strTableName As String)

Dim bytTableFieldCount As Byte, i As Byte

bytTableFieldCount = CurrentDb.TableDefs(strTableName).Fields.Count

For i = o To bytTableFieldCount - 1

    CurrentDb.TableDefs(strTableName).Fields(i).Name = Replace(CurrentDb.TableDefs(strTableName).Fields(i).Name, "#", " ")

Next i

'MsgBox CurrentDb.TableDefs(strTableName).Fields.Count

End Sub

или вот так:

Sub ReplaceCharInTableName(strTableName As String)

Dim i As Byte

For i = 0 To CurrentDb.TableDefs(strTableName).Fields.Count - 1

    CurrentDb.TableDefs(strTableName).Fields(i).Name = Replace(CurrentDb.TableDefs(strTableName).Fields(i). Name, "#", " ")

Next

'MsgBox CurrentDb.TableDefs(strTableName).Fields.Count

End Sub

Sub ReplaceCharInFieldsName(strTableName As String) Dim fld As DAO.Field With CurrentDb With .TableDefs(strTableName) For Each fld In .Fields fld.Name = Replace(fld.Name, "#", vbNullString) Next End With End With End Sub 

Если в цифрах, то:
Табличка (пустая) о 10 текстовых полях (в именах нет решеток)
Тестовая процедура:

Public Sub Test()

    Dim i As Integer, j As Integer

    Dim st As Long

    j = 100

    Debug.Print String(10, "-")

    st = apiTimeGetTime

    For i = 1 To j

        ReplaceCharInTableName "tbl1"

    Next i

    Debug.Print "ReplaceCharInTableName", apiTimeGetTime - st

    Debug. Print String(10, "-")

    st = apiTimeGetTime

    For i = 1 To j

        ReplaceCharInFieldsName "tbl1"

    Next i

    Debug.Print "ReplaceCharInFieldsName", apiTimeGetTime - st

End Sub

Как импортировать Excel в Access с помощью VBA, шаг за шагом

Введение

В Microsoft Access можно выполнить большинство действий по крайней мере несколькими способами, и импорт данных Excel в Access ничем не отличается. Итак, в этой статье мы покажем вам, как импортировать Excel в Access с помощью языка VBA. Например, мы будем использовать объектную модель Excel. Изучив этот подход, вы получите представление об интеграции Microsoft Access и объектной модели Microsoft Excel, и в то же время мы представим некоторые идеи, касающиеся операций DML (язык манипулирования данными) с таблицами Access с использованием SQL.

Преимущества импорта Excel в Access с использованием объектной модели Excel

Другими преимуществами использования объектной модели Excel являются:

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

• Доступ ко всем функциям объектной модели Excel, если требуется

Давайте начнем импортировать Excel в Access с помощью VBA

Для создания окончательного результата читателю потребуется

1. Тестовый файл Excel
2. База данных Microsoft Access с одной таблицей и одной формой с кнопкой

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

http://www.sample-videos.com/xls/Sample-Spreadsheet-10000-rows .xls

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

Как импортировать Excel в Access

Имеется десять столбцов без заголовков столбцов.

В этом примере мы импортируем столбцы A, B и G в целевую таблицу базы данных.

Приступим…

После создания пустой базы данных Microsoft Access пользователь должен создать пустую таблицу со структурой, представленной ниже А , поле Описание будет сопоставлено с столбцом B и полем Цена до столбца G . Читатель должен обратить внимание на поле цены, лежащее в основе типа данных. Как только исходные данные могут иметь десятичные числа, поле назначения должно быть способно их вместить (помните, что при работе с SQL десятичным разделителем является точка). Первое поле — это просто целочисленная последовательность, а второе — текстовая строка описания (подробнее об этом позже).

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

How To Import Excel Into Access

Измените заголовок свойства кнопки на Import Excel и имя кнопки на cmdImportExcel (как указано в предыдущий учебник, соглашения об именах всегда являются хорошей практикой). Нажмите кнопку «Сохранить» и назовите форму как 9.0045 фрмимпортExcel .

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

Щелкните правой кнопкой мыши кнопку вставки, выберите событие сборки, а затем построитель кода. Должен открыться редактор Visual Basic

How To Import Excel Into Access

В качестве стороны не давайте форсировать явное объявление переменных в коде, чтобы каждая требуемая переменная объявлялась с использованием соответствующего типа данных, и никакие ресурсы компьютера не выделялись зря. В верхней части кода (ниже Option Compare Database) просто добавьте

Option Explicit

Отныне все требуемые переменные требуют надлежащего объявления.

Перед реализацией кода давайте объясним основную логику последовательности:

1. Пользователь увидит диалоговое окно выбора файла, открывающееся
2. Пользователь сможет выбрать файл Excel

• Если был выбран файл Excel, его путь будет присвоен переменной

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

Реализация диалогового управления файлом

Чтобы использовать диалоговое управление файлом, мы должны предоставить ссылку на библиотеку объектов Microsoft Office XX (будет зависеть от установленной версии офиса). Для этого откройте меню Инструменты -> Ссылки в редакторе VBA.

Как импортировать Excel в Access

Перейдите вниз с помощью полосы прокрутки и выберите установленную библиотеку объектов Office.

Как импортировать Excel в Access

Отныне все объекты и методы, предоставляемые им, доступны для использования.

Давайте добавим базовый код диалогового окна выбора файла…

В редакторе VBA внутри события cmdImportExcel_Click() поместите следующий код. Код тщательно прокомментирован, поэтому читатель понимает, что делает каждая строка.

Private Sub cmdImportExcel_Click()

On Error GoTo cmdImportExcel_Click_err:

Dim fdObj As Office.FileDialog ‘объявляет переменную fdObj

Dim varfile As Variant’ переменная типа варианта, в которой будет храниться выбранный путь к файлу

Set fdObj = Application.FileDialog(msoFileDialogFilePicker) ‘создает экземпляр переменной, создающей объект сборщика файлов с использованием поздней привязки один файл

. allowmultiselect = False

‘очищает диалоговое окно файла существующие фильтры типа файла

.Filters.Clear

‘это диалоговое окно позволяет выбирать только файлы xlsx

.Filters.Add «Excel 2007+», «*.xlsx»

‘необязательно, установите заголовок диалогового окна файла

.Title = «Выберите файл Excel для импорта…»

.Show

If .SelectedItems.Count = 1 Then ‘файл был выбран

Call MsgBox(«Выбранный файл был: » & .SelectedItems(1)) ‘сейчас мы проверим выбор файла, отправив путь к файлу в окно сообщения на экране

Иначе ‘файл не был выбран

Call MsgBox(«Файл не выбран»)

END IF

END с

EXIT SUB

CMDIMPORTEXCEL_CLICK_ERR:

SELECT CASE ERR.Number

Case Else

Call Msgbox (err.number & ” vbOKOnly, «System Error…»)

End Select

End Sub

Теперь давайте протестируем код и проверим, как он работает. Сначала давайте визуализируем представление формы, щелкнув, как показано на следующем рисунке

Как импортировать Excel в Access

Затем будет представлена ​​форма

Теперь читатель должен нажать кнопку «Импорт Excel», и появится диалоговое окно выбора файла. результат должен быть похож на приведенный ниже, только показывает правильный путь

Если это результат, все работает, как ожидалось, и компонент выбора файлов уже работает. Далее мы увидим, как включить объектную модель Excel в решение Microsoft Access. Читатель должен будет включить соответствующую ссылку на объектную модель Excel. Еще раз откройте меню «Инструменты» -> «Ссылки» в редакторе VBA.

Как импортировать Excel в Access

И отметьте флажком ссылку на приложение Excel

Как импортировать Excel в Access

С этого момента все методы и свойства объекта Excel будут доступны после создания переменной Excel в коде Microsoft Access VBA . Этот метод называется ранним связыванием, типы данных известны заранее, но проверка этих ссылок не является обязательной. Если эти ссылки не установлены, используется метод позднего связывания, но это усложнит разработку, поскольку редактор кода intellisense не будет отображать свойства и методы, потребуется подробное чтение документации. Обычный подход заключается в использовании ссылки при разработке и удалении ее по завершении, тогда неизвестные константы должны быть заменены соответствующими целочисленными значениями. Мы не будем вдаваться в подробности, но важно помнить, что эти ссылки не являются обязательными, они просто облегчают жизнь.

После создания ссылки на Excel мы можем объявить определенные там типы переменных. Нам нужно будет объявить три переменные

1. xlApp — будет ссылка на скрытое приложение Excel
2. xlWb — будет ссылка на рабочую книгу, открытую в результате открытия файла для импорта

• xlWs — будет ссылкой на рабочий лист с данными для импорта

Этапы процесса загрузки можно описать следующим образом

1. Удалить существующие данные в целевой таблице
2. Перебирать все строки Excel, выполняя оператор вставки для каждой строки, пока не будет достигнута последняя строка

• Циклический процесс остановится, когда он не найдет больше данных в столбце A

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

Private Sub cmdImportExcel_Click()

При ошибке GoTo cmdImportExcel_Click_err:

Dim fdObj As Office.FileDialog ‘объявляет переменную fdObj

Dim varfile As Variant ‘переменная типа варианта, в которой будет храниться выбранный путь к файлу объект filepicker с использованием ранней привязки

With fdObj ‘используя оператор with мы будем работать с fdObj по умолчанию

‘не позволяет выбрать более одного файла

.allowmultiselect = False

‘очищает диалоговое окно файла существующие фильтры типа файла

.Filters.Clear

‘это диалоговое окно позволяет выбирать только файлы Excel, это достигается обработкой коллекции фильтров

.Filters. Добавить «Excel 2003», «*.xls»

.Filters.Добавить «Excel 2007+», «*.xlsx»

‘необязательно установить заголовок диалогового окна файла

. Title = «Пожалуйста, выберите файл Excel импортировать…»

.Показать

Если .SelectedItems.Count = 1 Then ‘файл был выбран, чтобы данные можно было импортировать из Excel, с этой точки будет запущен процесс циклического импорта

‘объявление переменных

Dim xlApp As Excel.Application ‘приложение excel

Dim xlWb As Excel.Workbook ‘ссылка на книгу Excel, которая будет указывать на открытую книгу

Dim xlWs As Excel.Worksheet ‘лист Excel с данными

Dim intLine As Long ‘счетчик строк

Dim strSqlDml As String ‘строковая переменная, в которой хранятся выполненные операторы SQL

Dim strColumnBcleaned As String ‘строковая переменная, в которой хранятся значения из столбца B после замены одинарных кавычек четырьмя одинарными кавычками

‘помните, что кавычка является разделителем строк в SQL поэтому его нужно экранировать

Dim strColumnGcleaned As String ‘строковая переменная, которая хранит значения из очищенного столбца G, шаг очистки заменяет запятые точками, как

‘десятичный разделитель в SQL — это точка

varfile = . SelectedItems(1) ‘выбор полного пути к выбранному файлу

очистить существующую таблицу

CurrentDb.Execute «DELETE * FROM tblExcelImport», dbFailOnError

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

Set xlApp = New Excel.Application

xlApp.Visible = True

‘открывая выбранный файл, вызывая метод открытия коллекции книг Excel, он получает местоположение файла в качестве параметра и возвращает ссылку на открытый файл

Set xlWb = xlApp.Workbooks.Open(varfile )

‘установить рабочий лист первым из доступных, так как он имеет данные для импорта

Установить xlWs = xlWb.Worksheets(1)

‘начальное значение/строка счетчика по умолчанию, это означает, что мы начинаем итерация в строке один

intLine = 1

Do

‘следующие две строки заменяют одинарные кавычки в значении столбца B и запятые точками в качестве десятичного разделителя в столбце G

strColumnBcleaned = Replace(xlWs. Cells(intLine, 2).Value2, “ ‘», «»»»)

strColumnGcleaned = Replace(xlWs.Cells(intLine, 7).Value2, «‘», «»»»)

‘следующая строка создает оператор вставки SQL, используя предыдущий полученный очищенный переменные и значение для столбца A

Оператор вставки должен иметь последовательность, присутствующую в целевой таблице, и получается путем объединения значений для каждой строки, представленной в файле Excel, при повторении

strSqlDml = «ВСТАВИТЬ В tblExcelImport VALUES(» & xlWs.Cells(intLine, 1).Value2 & «, ‘» & strColumnBcleaned & «’, » & strColumnGcleaned & «)»

‘выполняет оператор вставки в базу данных , dbFailOnError — это необязательное значение, которое заставит процесс Execute вернуть ошибку, если SQL не был выполнен должным образом

CurrentDb.Execute strSqlDml, dbFailOnError

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

xlWs. Cells(intLine, 1).Select

intLine = intLine + 1

Цикл до IsEmpty(xlWs.Cells(intLine, 1)) ‘критерий остановки, когда значения в столбце A stop цикл остановится, пожалуйста обратите внимание, что в коллекции ячеек первый индекс — это строка, а второй — столбец, поэтому мы делаем изменение строки. Как только цикл остановит шаги после закрытия открытой книги, выйдите из Excel и очистите ссылки памяти на созданные объекты

xlWb.Close False

xlApp.Quit

Установить xlApp = Ничего

Установить xlWb = Ничего

Установить xlWs = Ничего

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

Иначе ‘файл не был выбран

Вызов MsgBox(«Файл не выбран»)

End If

End With

Exit Sub

5 9

cmdImportExcel_Click_err:

Select Case Err.Number

Case Else

Call MsgBox(Err. Number & »- » & Err.Description, vbCritical + vbOKOnly, «System Error…»)

End Select

0

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

Некоторые соответствующие примеры, связанные с предложением IN, можно найти здесь

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

Доступ к извлечению данных из файла Excel. Можно использовать решение, аналогичное следующему:

SELECT CustomerID, CompanyName

FROM [Customers$]

IN «c:\documents\xldata.xls» «EXCEL 5.0;»

ГДЕ CustomerID Как «A*»

ЗАКАЗАТЬ ПО CustomerID;

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

Если значение может быть нулевым, переменная должна быть типа Variant, чтобы сохранить ее и затем использовать во встроенном операторе вставки.

Заключение

Спасибо, что прочитали о том, как импортировать Excel в Access. Оставьте комментарий, если у вас есть какие-либо вопросы о том, как импортировать Excel в доступ.

Ищете работу?

Поиск работы через Jooble

Запрос доступа к Excel — Панели управления Excel VBA

В следующей статье объясняется, как импортировать запрос доступа в Excel с помощью VBA. Сначала я настрою запрос в базе данных Access и вызову запрос в Excel. Если вы хотите импортировать часть запроса, возможно, стоит изучить вопрос об импорте запроса параметров                                   , который рассматривается в соответствующем разделе. Процедура имеет ряд практических последствий: данные, которые не требуются, такие как объемные исходные данные, могут храниться в базе данных и вызываться при необходимости. Файл Excel работает с небольшими сводными данными и остается компактным. У вас есть возможность периодически обращаться к обновленным данным для обновления наборов данных Excel.
 
Приступая к работе Самое главное, что нужно сделать, это настроить подключение к MS Access.
 
В Excel нажмите Alt F11.
 
Теперь выберите Инструменты — Ссылки

Прокрутите обширный список, пока не найдете Microsoft Office xx.0 Database Engine Object (где xx означает 14.0 выше)

Если вы используете версию ниже 2007, вам нужно обратить внимание сейчас. Для более ранних версий выберите Microsoft DAO XX Object Library. Не выбирайте, так как будет конфликт, и ваш процесс, вероятно, не будет запущен.

Теперь, когда вы подключены к Access, ниже приведен код, который заставит вашу процедуру работать.

Option Explicit
Sub ImpAccess() ‘Excel VBA для импорта запроса доступа.

Dim MyDatabase As DAO.Database
Dim qryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset

Set MyDatabase=OpenDatabase(«C:\Test\TestDb.accdb») ‘Имя БД
Set qryDef=MyDatabase.QueryDefs(» qryVanilla») ‘Имя запроса
Set MyRecordset=qryDef.OpenRecordset Sheet1.Range(«A11»).CopyFromRecordset MyRecordset

For i=1 To MyRecordset.Fields.Count ‘Заголовки для возврата

Sheet1.Cells(10, i).Value=MyRecordset.Fields(i — 1).Name

Далее я

Конец сабвуфера

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

Я включу файлы Excel и Access.