импорт excel -access. VBA

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

29.05.2014 13:50:32

Всем здравствуйте
Не работает код
цель макроса: еженедельное добавление к существующим данным новых
подключила библиотеку DAO 3.6 обджект лайбрери. Однако на строке

Код
Set BD = Workspaces(0).OpenDatabase("V:\Reporting\FA SI\макросыSI_Database.accdb")

пишет что такой формат не обнаружен 7-ой и эксель и аксесс
вот весь код

Скрытый текст

и кстати, будет ли он ПОД СУЩЕСТВУЮЩИЕ данные добавлять новые или будет перезатирать ?
Подскажите, как накопительно сделать и что настроить,чтобы понимал форматы

СПАСИБО БОЛЬШОЕ
Маша

Изменено: manyaffkina - 29.05.2014 13:55:15

B.Key

Пользователь

Сообщений: 633 Регистрация: 26.02.2014

29.05.2014 14:30:35

Зачем DAO использовать?
ADO 2.8 побыстрее будет.
примеров на форуме масса

Код

Dim sCon As String
Dim rs As Object, cn  As Object
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
Set cn = New ADODB.Connection
sCon = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source=V:\Reporting\FA SI\макросы\SI_Database.accdb"
cn.Open sCon
sSql=" select * from  sale"
Set rs = cn.Execute(sSql)
' Ну и так далее по вашему коду

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

29.05.2014 15:30:22

я уже пробовала при адо 2.8 формат адодб
он выдает запись тип данных не определен (user-defined type is not defined)
Код выглядел вот так

Код

Public Sub ex2Access()
Dim BD As ADODB.Connection
Set BD = New ADODB.Connection
connStringA = "Data Source=V:\Reporting\FA SI\макросы\SI_Database.accdb"
With BD
.CommandTimeout = 20
.CursorLocation = adUseClient
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open connStringA
End With
Dim commandsA As ADODB.Command
Set commandsA = New ADODB.Command
commandsA.ActiveConnection = connA
i = 2
Do While Cells(i, 1) <> ""
If Cells(i, 9) = "" Then GoTo 99
If Cells(i, 9) = " " Then GoTo 99
SQLTextA = "insert into sales values ('" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "','" & Cells(i, 7) & "','" & Cells(i, 8) & "','" & Cells(i, 9) & "','" & Cells(i, 10) & "')"
commandsA.CommandText = SQLTextA
commandsA.Execute
99
i = i + 1
Loop
End Sub

сейчас попробовала Ваш вариант при адо 2.8 тоже самое-user-defined type is not defined

Что делать?
спасибо

anvg

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

29.05.2014 15:37:16

Доброе время суток
[USER=42269]manyaffkina,
У вас не работает, так как Excel не знает откуда брать коллекцию Workspaces. Через ADO будет действительно быстрее, если использовать Insert Into
Ваш код только добавляет новые записи в таблицу Sale. Операция обновления существующих это другая история.

B.Key,

Цитата
Provider= Microsoft.Jet.OLEDB.4.0;

С файлом accdb работать не будет, так как нового формата. Требуется

Код
Provider=Microsoft.ACE.OLEDB.12.0;

Но в отличии от Jet, установленного в системе по-умолчанию, начиная с WinXP, если не установлен Access 2007 и новее, ACE может и не быть - тогда и работать не будет.

Цитата
sSql=" select * from sale"

А зачем делать выборку данных, если у ТС предполагается только добавление .AddNew? Думаю, лучше

Код
sSQL = "Select * From sale Where MacroUnit Is Null"

Изменено: anvg - 29.05.2014 15:37:31

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

29.05.2014 16:09:53

Посмотрите, пожалуйста, на мой последний код - на 1 сообщение выше
там и инсерт инто есть и 12.0

Код
.Provider = "Microsoft.ACE.OLEDB.12.0"

Код

 SQLTextA = "insert into sale in values ('" & Cells(i, 2) & "','" & Cells(i, 3) & "','" & Cells(i, 4) & "','" & Cells(i, 5) & "','" & Cells(i, 6) & "','" & Cells(i, 7) & "','" & Cells(i, 8) & "','" & Cells(i, 9) & "','" & Cells(i, 10) & "')"

на компе установлена библиотека адо 2.8
аксесс и эксель седьмые

он в самом начале прогноза макроса выдает компайл еррор (user-defined type nor defined) на моменте

Код
ADODB.Connection

прогоняла на компе у коллеги - то же самое

спасибо, что помогаете неумехам таким

anvg

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

29.05.2014 17:03:22

1. В редакторе VBA подключаете через Tools\References библиотеку Microsoft ActiveX Data Object 2.8 Library
2. Введём определения
2.1 Пусть база Access MyDb.accdb лежит в папке d:\path1 и есть таблица MyTable с полями (Имя1, Имя2, Имя3)
2.2 Пусть есть файл Excel MyBook.xlsb, лежащий в папке d:\path2, и на его листе "Лист1" таблица в первой строке которой введены имена столбцов (Название1, Название2, Название3).
3. Тогда вставку значений с "Лист1" в таблицу MyTable можно выполнить следующим кодом

Код

Public Sub InsertInto()
    Dim pCon As New ADODB.Connection
    Dim sSQL As String
    pCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Mode=16;Data Source=d:\path1\myDb.accdb"
    sSQL = "Insert Into MyTable (Имя1,Имя2,Имя3)" & _
                " Select [Название1],[Название2],[Название3] " & _
                " From [Excel 12.0;Database=d:\path2\MyBook.xlsb;HDR=YES].[Лист1$]"
    pCon.Execute sSQL
    pCon.Close
End Sub

Успехов.
P. S. Требуется, чтобы баз Access не была открыта в монопольное использование

Изменено: anvg - 29.05.2014 17:05:12

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

30.05.2014 16:52:28

Привет всем еще раз!
Таки нашла что-то понятное мне, но на строке

Код

 TableRSet.Update 
ВЫДАЕТ
'BOF или EOF имеет значение True, либо текущая запись удалена. Для выполняемой операции требуется текущая запись '.

Что делать?
Вот весь код

Скрытый текст

Код

Public Sub AddNew()
        Const sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;;Mode=Share Deny None;Data Source=V:\Reporting\FA SI\макросы\SI_Database.accdb"
    Dim AddSQL As String
    Dim pConn As Object, AddRSet As Object, TableRSet As Object
    Set pConn = CreateObject("ADODB.Connection"): pConn.Open sConn
       AddSQL = "Select t1.[MacroUnit],t1.[Category_View],t1.[Shipment_Source],t1. [APO], t1.[SAP_Master], t1.[Product_Name],t1.[Week], t1.[Sold], t1.[Frcst] From [Excel 12.0;DATABASE=V:\API Create\SI Export2Access.xlsm;HDR=YES].[Sheet1$] As t1"
    AddSQL = AddSQL & " Left Join AddSQL  As t2 On ((t1.[MacroUnit]=t2.[MacroUnit]) And (t1.[Category_View]=t2.[Category_View]) And (t1.[Shipment_Source]=t2.[Shipment_Source])And (t1.[APO]=t2.[APO])And (t1.[SAP_Master]=t2.[SAP_Master])And (t1.[Product_Name]=t2.[Product_Name])And (t1.[Week]=t2.[Week])And (t1.[Sold]=t2.[Sold])And (t1.[Frcst]=t2.[Frcst]))"
    AddSQL = AddSQL & " Where t2.[SAP_Master] Is Null"
    Set AddRSet = CreateObject("ADODB.Recordset"): AddRSet.CursorLocation = 2
    AddRSet.Open "AddSQL", pConn, 2, 2
    If AddRSet.RecordCount = 0 Then
     MsgBox "------------------"
    Else
     Set TableRSet = CreateObject("ADODB.Recordset"): TableRSet.CursorLocation = 2
          TableRSet.Open "Select [MacroUnit],[Category_View],[Shipment_Source],[APO],[SAP_Master],[Product_Name],[Week],[Sold],[Frcst] From AddSQL Where [MacroUnit] Is Null", pConn, 2, 2
     Do Until AddRSet.EOF
      TableRSet.AddNew
      TableRSet("MacroUnit").Value = AddRSet("MacroUnit").Value
      TableRSet("Category_View").Value = AddRSet("Category_View").Value
      TableRSet("Shipment_Source").Value = AddRSet("Shipment_Source").Value
      TableRSet("APO").Value = AddRSet("APO").Value
      TableRSet("SAP_Master").Value = AddRSet("SAP_Master").Value
      TableRSet("Product_Name").Value = AddRSet("Product_Name").Value
      TableRSet("Week").Value = AddRSet("Week").Value
      TableRSet("Sold").Value = AddRSet("Sold").Value
      TableRSet("Frcst").Value = AddRSet("Frcst").Value
      AddRSet.MoveNext
     Loop
     TableRSet.Update
     TableRSet.Close
    End If
    AddRSet.Close: pConn.Close
End Sub

B.Key

Пользователь

Сообщений: 633 Регистрация: 26.02.2014

30.05.2014 17:03:23

мне кажется надо так

Код
TableRSet.Update AddRSet.MoveNext Loop

Поправил, кажется так

Изменено: B.Key - 30.05.2014 17:05:37

B.Key

Пользователь

Сообщений: 633 Регистрация: 26.02.2014

30.05.2014 17:10:35

да и переписал бы так:

Код
Do Until AddRSet.EOF TableRSet.AddNew For i = 0 To TableRSet.Fields.Count - 1 TableRSet(i).Value = AddRSet(TableRSet.Fields(i).Name).Value Next TableRSet.Update AddRSet.MoveNext Loop

Изменено: B.Key - 30.05.2014 17:11:09

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

#10

30.05.2014 17:33:27

Цитата

B.Key пишет:

Код
TableRSet.Update AddRSet.MoveNext Loop

Блин, этот макрос пошагово мне сложно отследить. в итоге да, он не дебачит, но ничегошеньки не вставил в бд аксесса(
БД как была пустой так и осталась

Почему такое может быть?

anvg

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

#11

30.05.2014 20:35:43

manyaffkina,

Цитата
AddRSet.Open "AddSQL", pConn, 2, 2

И что - на этом месте даже не ругается? Или вы где-то On Error Resume Next непосредственно в своём коде изменили (в приведённом нет такого)? Или же у вас в базе Access есть пустая таблица AddSQL, только вы же не с неё данные хотите добавлять, не так ли?

Цитата
но ничегошеньки не вставил в бд аксесса

И не будет. Зачем переменную, содержащую запрос, заключать в кавычки?

Цитата
AddRSet.CursorLocation = 2

Если вы объявили положение курсора на стороне сервера, то

Цитата
If AddRSet.RecordCount = 0 Then

не будет работать
Да и обновления на каждой вставке ни к чему.

Изменено: anvg - 30.05.2014 20:36:29

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

#12

02.06.2014 10:13:39

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

V:\API Create\SI Export2Access.xlsm

если у Вас есть время, я буду Вам очень признательна поправить в самом коде все,что вы написали.
СПАСИБО

B.Key Пользователь Сообщений: 633 Регистрация: 26.02.2014	#13 02.06.2014 10:50:39 Вы бы приложили файлик excel с данными для импорта, и Вам бы давно уже оптимизировали и код и метод экспорта, желательно конечно было бы и схему данных в access

manyaffkina Пользователь Сообщений: 32 Регистрация: 27.03.2014	#14 02.06.2014 12:03:10 http://files.mail.ru/C41898D09A4C4258AC8E81E414C0D2A9 сюда напряму не получается залить архивом спасибо!!!!

Юрий М

Модератор

Сообщений: 61037 Регистрация: 14.09.2012

Контакты см. в профиле

#15

02.06.2014 12:07:21

Цитата
manyaffkina пишет: сюда напряму не получается залить архивом

И не получится - Правила нужно читать...

anvg

Пользователь

Сообщений: 11927 Регистрация: 22.12.2012

Excel 2016, 365

#16

02.06.2014 15:15:32

manyaffkina, доброе время суток.
Будет так, если таблица листа Excel расположена в прилагаемом файле (в противном случае измените в
Replace$(xlsConn, "$1", ThisWorkbook.FullName) ThisWorkbook.FullName на путь к файлу Excel.
Ну, и отредактировать путь к базе в adbConn не забудьте. Успехов.

Прикрепленные файлы

Book1_копия.xlsb (19.48 КБ)

Изменено: anvg - 02.06.2014 15:17:49

manyaffkina

Пользователь

Сообщений: 32 Регистрация: 27.03.2014

#17

03.06.2014 11:23:43

спасибо!
Путь к БД поменян
Но на строке

Код
AddRSet.Open AddSQL, pConn

пишет 'characters found after end of sql statement'
(((

anvg Пользователь Сообщений: 11927 Регистрация: 22.12.2012 Excel 2016, 365	#18 03.06.2014 11:43:39 manyaffkina, путь к какой базе поменян? К Access или Excel? Можете привести полный путь к файлам Excel и Access? У меня ошибок нет "вставляет". Изменено: anvg - 03.06.2014 11:44:06

anvg Пользователь Сообщений: 11927 Регистрация: 22.12.2012 Excel 2016, 365	#19 03.06.2014 11:47:33 А ещё лучше значение AddSQL в Immediate

manyaffkina Пользователь Сообщений: 32 Регистрация: 27.03.2014	#20 03.06.2014 12:16:09 МНОГОУВАЖАЕМЫЙ anvg, Спаибо Вам огромное! Все работает Надеюсь, в накопительной системе) Сегодня обработаю новые данные и посмотрим, вниз ли он добавляются Спасибо Вам, что помогаете новичкам!