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

@Ivan_Ivanovich · Регистрация: 01.02.2016

Author24 — интернет-сервис помощи студентам

Добрый день, очень нужна помощь.

Как автоматически с помощью кода VBA (по нажатию кнопки) импортировать данные из определенной ячейки таблицы Excel в определенный атрибут таблицы Access?

Получилось только импортировать несколько ячеек подряд в таблицу (без выбора атрибута, все хаотично), но это не то, что нужно:

Visual Basic

Private Sub Кнопка0_Click()
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, _
 "exportSample", "D:\Sample.xlsx", True, "A1:G5"
 
End Sub

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

Visual Basic

Private Sub Кнопка13_Click()
 
Dim FName As String
Dim result As Integer
With Application.FileDialog(1)
   .Title = "Select file"
   .InitialFileName = "D:\DB" 'default path Путь по умолчанию
   .AllowMultiSelect = False
   .Filters.Clear
   .Filters.Add "MS Excel", "*.xlsx", 1
result = .Show
 
If result = 0 Then Exit Sub
FName = Trim(.SelectedItems.Item(1))
End With
 
 
DoCmd.TransferSpreadsheet aclImport, , "importSample", FName, True
 
End Sub

@corbis · 06.02.2016, 21:46

Нужно делать именно импорт или можно скопировать данные?

@Ivan_Ivanovich · 06.02.2016, 21:57 **[ТС]**

А в чем отличие, поясните пожалуйста? При импорте же данные не исчезают из таблицы Excel.

@texnik-san · 06.02.2016, 23:29

Сообщение от Ivan_Ivanovich

А в чем отличие, поясните пожалуйста? При импорте же данные не исчезают из таблицы Excel.

При импорте в Аксес появляется таблица, которой не было. При вставке данных таблица существует и до вставки, проосто после вставки в ей появляются новые данные.

Добавлено через 3 минуты
Но и то, и другое можно сделать запросом.

SQL

1 2	SELECT * INTO ИмяТаблицы FROM [Лист1$] IN 'C:\irka\Слова.xlsx'[Excel 12.0 XML; HDR=No;];

Добавлено через 39 секунд

SQL

1
2
3

INSERT INTO ИмяТаблицы
SELECT *
FROM [Лист1$] IN 'C:\irka\Слова.xlsx'[Excel 12.0 XML; HDR=No;];

Добавлено через 2 минуты
Правите выражения Into и Select и таким способом определяете, какой й столбец Excel в какое поле Access.

Добавлено через 3 минуты
Скажем,

SQL

1
2
3

INSERT INTO ИмяТаблицы (Поле1, Поле2, Поле3)
SELECT F1, F4, F2
FROM [Лист1$] IN 'C:\irka\Слова.xlsx'[Excel 12.0 XML; HDR=No;];

отправит 1 столбец в Поле1, 2й - в Поле3, 4й - в Поле2.

@Ivan_Ivanovich · 06.02.2016, 23:45 **[ТС]**

Спасибо за отзывчивость и помощь, но это не то.
Я загрузил пример таблицы Excel. Мне нужно, чтобы по нажатию кнопки (условно кнопка "Импорт") в форме, автоматом переносились определенные ячейки в атрибуты определенной таблицы. Например, ячейка R4C2 (2015/2016) в n-ную таблицу в атрибут Учебный год.

@texnik-san · 07.02.2016, 00:13

А что такое n-ная таблица, извините?

@Ivan_Ivanovich · 07.02.2016, 00:17 **[ТС]**

Любая определенная таблица, дабы не уточнять название так написал.

@texnik-san · 07.02.2016, 00:28

Visual Basic

dim strSQL as string
strSQL = "INSERT INTO ИмяТаблицы (Учебный год) SELECT F2 "
strSQL = strSQL & "FROM [Лист1$] IN 'C:\путь\Sample.xlsx'[Excel 12.0 XML; HDR=No;] "
strSQL = strSQL & "WHERE F1='Учебный год'"
currentdb.execute strSQL

Добавлено через 1 минуту
Но вообще импортировать по одной ячейке это.... нерационально )))

mobile · 07.02.2016, 00:47

В запросе можно считывать и определенные ячейки, блоки и даже по нескольку штук единовременно. Но стиль ссылок обычный - не R1C1, А1:А1. Воспользуясь примером texnik-san:

SQL

1 2	SELECT * FROM [Лист1$B4:B4] AS x1, [Лист1$C4:D6] AS x2 IN 'C:\irka\Слова.xlsx'[Excel 12.0 XML; HDR=No;];

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

Visual Basic

Dim app as object, wrk as object
dim rst as dao.recordset
set rst=currentdb.openrecordset("select * from [n-я таблица]")
set app=createobject("excel.application")
set wrk=app.workbooks.open("путь\имяфайла.xls")
rst.addnew
rst![Учебный год]=range("C4")
rst![Другое поле]=range("F16")
...................................
rst.update
app.quit

@Ivan_Ivanovich · 07.02.2016, 01:06 **[ТС]**

Каждый раз одни и те же ячейки будут импортироваться, целью является автоматизировать эти действия.
А данный код где нужно прописывать? В кнопку вставил (где VBA код), вышло сообщение: "Ошибка 3134. Ошибка синтаксиса."

Visual Basic

Private Sub Кнопка3_Click()
 
Dim strSQL As String
strSQL = "INSERT INTO Список образовательных программ (Учебный год) SELECT F2"
strSQL = strSQL & "FROM [Лист1$] IN 'D:\DB\Sample.xlsx'[Excel 12.0 XML; HDR=No;]"
strSQL = strSQL & "WHERE F1='Учебный год'"
CurrentDb.Execute strSQL
 
End Sub

Добавлено через 13 минут
Спасибо! Вот это мне нужно: "Если одиночных ячеек много и расположение их нерегулярно, то гораздо эффективнее воспользоваться средствами автоматизации: открыть ексель, гулять по листу и писать в рекордсет таблицы."
Попробовал, вышла ошибка, выделяя "range": "sub or function not defined".

Добавлено через 1 минуту
На данный код:

Visual Basic

Private Sub Кнопка3_Click()
 
Dim app As Object, wrk As Object
Dim rst As dao.Recordset
Set rst = CurrentDb.OpenRecordset("select * from [Список образовательных программ]")
Set app = CreateObject("excel.application")
Set wrk = app.workbooks.Open("D:\DB\Sample.xlsx")
rst.AddNew
rst![Учебный год] = range("C4")
 
rst.Update
app.Quit
 
 
End Sub

mobile · 07.02.2016, 01:08

Ошибочка вышла. Забыл про родителей. А это всегда чревато

Visual Basic

1 2	rst![Учебный год]=app.range("C4") rst![Другое поле]=app.range("F16")

@Ivan_Ivanovich · 07.02.2016, 01:25 **[ТС]**

А объясните пожалуйста для чего нужна вторая строка в данном случае, или это просто как продолжение Вы написали?

Visual Basic

1	rst![Другое поле]=app.range("F16")

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

@texnik-san · 07.02.2016, 01:59

Сообщение от Ivan_Ivanovich

вышло сообщение: "Ошибка 3134. Ошибка синтаксиса."

Потому что нужно по-человечески отвечать на просьбу уточнить имя таблицы. У вас в имени таблицы пробелы, такие имена нужно брать в квадратные скобки:

SQL

1	INSERT INTO [Список образовательных программ (Учебный год)] SELECT

Добавлено через 1 минуту

Сообщение от Ivan_Ivanovich

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

Дык вы зачем-то С4 копируете, а нужна В4

@Ivan_Ivanovich · 07.02.2016, 14:15 **[ТС]**

Спасибо большое, разобрался!
А не знаете как можно импортировать этим методом (rst![Учебный год]=app.range("B4")) или другим vba кодом определенную часть символов из ячейки? Например, есть ячейка с содержимым "2012/2013", нужно импортировать только первые 4 символа или, наоборот, последние 4 символа.

@texnik-san · 07.02.2016, 15:10

Left(app.range("B4"),4)
Right(app.range("B4"),4)

@Ivan_Ivanovich · 07.02.2016, 15:37 **[ТС]**

Спасибо!

@alvk · 08.02.2016, 07:28

Сообщение от texnik-san

Но вообще импортировать по одной ячейке это.... нерационально )))

Это одинэснуто.

Добавлено через 2 минуты

INSERT INTO [Список учебных обще-образовательных программ на Учебный год средне-технического образовательного учреждения в редакции профессора Петренко Ильи Михайловича, созданный в начале 2015 учебного года и согласованный со всеми необходимыми инстанциями] SELECT

__................

@Ivan_Ivanovich · 11.02.2016, 12:21 **[ТС]**

Добрый день, появился еще схожий вопрос. Возможно ли из ячейки таблицы Excel как-то импортировать информацию, находящуюся между символов?
1. Например, из ячейки с содержимым "бакалавриат, 3 курс" импортировать только цифру "3".
2. Или из ячейки "Студент: Иванов Иван Иванович", брать только "Иванов Иван Иванович", но только каким-то иным способом нежели здесь

PureBasic

1	Right(app.range("B4"),20)

, т.к. ФИО всегда будут разниться в количестве символов, можно сделать, например, чтобы импортировались последующие символы после символов "Студент: "?

@texnik-san · 11.02.2016, 12:41

Replace("app.range("B4")","Студент: ","")

@Kkarn · 11.02.2016, 12:42

Ivan_Ivanovich, Это вам нужно поиграться с текстовыми функциями VBA, такими как InStr, Mid, Replace и т.п. Составить конструкцию, которая бы забирала нужный фрагмент, не так и сложно, главное - четкий алгоритм...

@texnik-san шапоклякистка 8-го дня 3680 / 2240 / 391 Регистрация: 26.06.2015 Сообщений: 4,647 Записей в блоге: 1
	11.02.2016, 12:41	19
	Replace("app.range("B4")","Студент: ","") 1

@corbis 547 / 274 / 50 Регистрация: 03.04.2015 Сообщений: 926
	06.02.2016, 21:46	2
	Нужно делать именно импорт или можно скопировать данные? 0

@Ivan_Ivanovich 0 / 0 / 0 Регистрация: 01.02.2016 Сообщений: 29
	06.02.2016, 21:57 [ТС]	3
	А в чем отличие, поясните пожалуйста? При импорте же данные не исчезают из таблицы Excel. 0

@texnik-san шапоклякистка 8-го дня 3680 / 2240 / 391 Регистрация: 26.06.2015 Сообщений: 4,647 Записей в блоге: 1
	07.02.2016, 00:13	6
	А что такое n-ная таблица, извините? 1

@Ivan_Ivanovich 0 / 0 / 0 Регистрация: 01.02.2016 Сообщений: 29
	07.02.2016, 00:17 [ТС]	7
	Любая определенная таблица, дабы не уточнять название так написал. 0

@Ivan_Ivanovich 0 / 0 / 0 Регистрация: 01.02.2016 Сообщений: 29
	07.02.2016, 14:15 [ТС]	14
	Спасибо большое, разобрался! А не знаете как можно импортировать этим методом (rst![Учебный год]=app.range("B4")) или другим vba кодом определенную часть символов из ячейки? Например, есть ячейка с содержимым "2012/2013", нужно импортировать только первые 4 символа или, наоборот, последние 4 символа. 0

@texnik-san шапоклякистка 8-го дня 3680 / 2240 / 391 Регистрация: 26.06.2015 Сообщений: 4,647 Записей в блоге: 1
	07.02.2016, 15:10	15
	Left(app.range("B4"),4) Right(app.range("B4"),4) 1

@Ivan_Ivanovich 0 / 0 / 0 Регистрация: 01.02.2016 Сообщений: 29
	07.02.2016, 15:37 [ТС]	16
	Спасибо! 0

@alvk 7439 / 4574 / 300 Регистрация: 12.08.2011 Сообщений: 14,229
	08.02.2016, 07:28	17
	Сообщение от texnik-san Но вообще импортировать по одной ячейке это.... нерационально ))) Это одинэснуто. Добавлено через 2 минуты INSERT INTO [Список учебных обще-образовательных программ на Учебный год средне-технического образовательного учреждения в редакции профессора Петренко Ильи Михайловича, созданный в начале 2015 учебного года и согласованный со всеми необходимыми инстанциями] SELECT __................ 0

@Kkarn 902 / 292 / 50 Регистрация: 02.12.2014 Сообщений: 1,229
	11.02.2016, 12:42	20
	Ivan_Ivanovich, Это вам нужно поиграться с текстовыми функциями VBA, такими как InStr, Mid, Replace и т.п. Составить конструкцию, которая бы забирала нужный фрагмент, не так и сложно, главное - четкий алгоритм... 1