Excel: умный импорт из Excel листа в таблицу Access

[Kokain]

Всем привет, помогите разобраться с нижеизложенным.

Что Дано:
Есть книга Excel "C:\Data\test.xls". В этой книге есть лист "output", с таблицей данных (первая строка - название полей).
Есть база Access "C:\Data\myData.mdb.xls". В этой базе есть таблица "test" с данными.

Что надо сделать:
Необходимо создать макрос в Excel, который бы вставлял данные из листа output, в таблицу "test", причем если в таблице "test" есть уже такие данные (определяется по трем ключевым полям), то их необходимо заменить. Т.е. не должно быть дублированых записей в таблице "test". Этим макросом я буду пополнять ежедневно данные в таблице "test", либо заменять уже существующие (на верные или скорректированные).

В чем проблема:
Я не знаю как создать таблицу (объект?) с дынными из Excel, что бы:
a. проверить есть ли такие же записи в таблице "test" (если есть, то удалить их из "test" и залить заново)
b. Вставить их в access.

Все это я буду делать (уже делаю) через ADO. Сейчас копаюсь в книгах и инете - не нашел примеров создания таблицы (recordset'а) в Excel, копированием/выделением/селектом диапазона ячеек. Помогите пожалуйста, горит.

[Teslenko_EA]

Здравствуйте Kokain.
Решать такую задачу со стороны Excel сложнее чем из Access, в котором достаточно выполнить два запроса, один на обновление второй на добавление.
В Вашем варианте можно сделать следующее:
создать рекордсет ADO в Excel,
заполнить его данными из листа "output",
открыть рекордсет с набором записей таблицы "test"
фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.
Все эти процедуры и библиотека ADO должны присутствовать в проекте VBA файла xls.
Евгений.

[Kokain]

Добрый день,
Получается последовательность такая:
1создать рекордсет ADO в Excel,
2заполнить его данными из листа "output",
22 Подконнектится
3открыть рекордсет с набором записей таблицы "test"
4фильтром и перебором всех записей произвести сравнение, для редактирования или добавления записей.

Как раз проблема с пп. 1 и 2 - я не знаю что прописать.

1.

Код: Выделить всё

Dim xlsRecordSet as ADODB.Recordset
Set xlsRecordSet=New ADOD.Recordset
???

2.
???
22.

Код: Выделить всё

Const ConnectionString As String = _
      "Provider = Microsoft.Jet.OLEDB.4.0;" + _
      "Data Source = C:\Data\myData.mdb;Persist Security Info=False"
      
Dim Connection As ADODB.Connection

Set Connection = New ADODB.Connection
Connection.ConnectionString = ConnectionString

If Connection.State = ObjectStateEnum.adStateOpen Then objConn.Close
    Connection.Open
End If

'....


If Connection.State = ObjectStateEnum.adStateOpen Then objConn.Close
    Connection.Close
End If

3.
...

4.
...

[Teslenko_EA]

Здравствуйте Kokain.
Как рекордсет создается и заполняется Вы можете посмотреть в коде.
Также его можно сортировать и передавать в массив (это для примера).

Код: Выделить всё

Sub SortRecordset()
  Dim rs As Object, i%, V()
  Set rs = CreateObject("ADODB.Recordset")'создание рекордсета
  rs.Fields.Append "Fld", 200, 255 ' добавление поля в рекордсет
  rs.Open 'открываем рекордсет
  rs.AddNew 'добавление записи в реккордсет
  rs(0) = "Text" 'ввод данных
  rs.Update 'сохранение
  rs.Sort = "Fld" 'имя поля сортировки  "Fld desk" - по убыванию
  V = rs.GetRows 'при необходимости передаем все содержимое рекордсета в массив
  rs.Close ' закрываем рекордсет
  Set rs = Nothing ' очищаем переменную для освобождения памяти
End Sub

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

[Kokain]

Я подключаюсь к базе на sql server, и мне необходимо исполнить запрос, который хранится в переменной myQuery (string). Проблема в том, что в запросе должно быть условие: where a.actdate=CurrentDate. А так как myQuery это строка, то CurrentDate не подхватывается (переменной CurrentDate (тип Date) я присваиваю значение с листа, например 11.01.2008). Как сделать вытащить дынные за нужную мне дату? Помогите пожалуйста.

P.S: Более менее разобрался с подключением из vba excel к access базам и ms sql server. Спасибо господину Teslenko_EA

Если написать так: where a.actdate=@CurrentDate, то выдет ошибку 'Must declare the variable @CurrentDate'

[Teslenko_EA]

Здравствуйте Kokain.
В обращении с датами у SQL, при исполльзовании национальных региональных настроек существует проблема, иногда SQL не различает форматы "DD/MM/YYYY" и "MM/DD/YYYY". Так же в разных "диалектах" разная пунктуация при обращения к датам.
Попробуйте в строчную переменную запроса внедрить "#2005/4/17#", это синтаксис Access.
Евгений.

[packer]

Я подключаюсь к базе на sql server, и мне необходимо исполнить запрос, который хранится в переменной myQuery (string). Проблема в том, что в запросе должно быть условие: where a.actdate=CurrentDate. А так как myQuery это строка, то CurrentDate не подхватывается (переменной CurrentDate (тип Date) я присваиваю значение с листа, например 11.01.2008). Как сделать вытащить дынные за нужную мне дату? Помогите пожалуйста.

Вам нужно смотреть документацию по тому диалекту SQL, кот-й исп-ся на сервере.
А по поводу формирования строки SQL в VBA смотрите http://www.sql.ru/faq/faq_topic.aspx?fid=157.
Очень познавательно и полезно.

[Kokain]

Всем доброго времени суток, появился еще один вопрос:
Что необходимо:
1. Я создаю xlsRecordSet c данными из Excel листа >>
2. затем подключаюсь к базе access; и c помощью sql запроса проверяю существуют ли в базе записи такие же как в xlsRecordSet'e >>
3. Если в базе нет такихже данных, то вставляю их в access.
Вопросы:
- Возможно ли обращаться к xlsRecordSet'у как таблице, то есть написать к ней sqlQuery (как это будет выглядеть)? Если невозможно, то Что надо написать, чтобы вставить в access данные из xlsRecordSet'а?


P.S. Проблему c Date победил, спасибо за оперативные комментарии. ))) на sql сервере был тип datetime (YYYY-MM-DD HH.MM.SS).

[Teslenko_EA]

Здравствуйте Kokain.
"Возможно ли обращаться ... ...написать к ней sqlQuery..." - нет, но данные рекордсета можно использовать при построении запроса.
В цикле перебора набора записей, создается строчная переменная содержащая значение поля рекордсета

Код: Выделить всё

...
rs.MoveFirst
Do Until rs.EOF
   sSql = "INSERT INTO Table1 ( Fld1 )  SELECT '" & rs(1) & "'" 'текст момещается в апострофы
   Execute.sSql '
   rs.MoveNext
Loop
...

примерно так.
Рекомендую:
http://ifolder.ru/4464512 - "Справочник программиста (MSA XP) os_Refer" образцы кода
http://ifolder.ru/4108426 - "SQL в примерах и задачах" учебник
Евгений.