SQL

[Lider]

Вопрос в следующем:
Можно ли из программы на VBA выполнить SQL запрос к таблице Excel?

если да, то как?

[alibek]

Можно. Открываешь таблицу Excel как базу данных и используешь .Execute или .OpenRecordset. Если для доступа используется DAO, то можно работать с xls-файлами до версии Excel 97 включительно. Для Excel 2000 и выше надо использовать ADO. Connection String смотреть в мануале или в интернете.

[Lider]

Спасибо Alibek, но я видимо тутпее, чем ты думаешь!
Для примера : есть таблица с полями Артикул, наименование, янв, февр, Уровень1, уровень 2.
Поля янв, февр -целые, остальные текстовые.
таблица расположена в листе Расходы.

Приведи пожалуйста пример

[alibek]

Вот, кусок кода, получающий список всех таблиц:

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

With Connection.OpenSchema(adSchemaTables)
  Do Until (.BOF Or .EOF)
    If .Fields("TABLE_TYPE") = "TABLE" Then
      Debug.Print .Fields("TABLE_NAME")
    End If
    .MoveNext
  Loop
  .Close
End With


[alibek]

Блин, что-то я не туда написал...

[Lider]

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

With Connection.OpenSchema(adSchemaTables)

Object reqired
такая вот ошибочка!
может подключить чего?

[alibek]

Lider, то я не тебе отвечал, топик перепутал.

Для начала идешь в Tools - References и подключаешь ADO.
Затем, объявляешь (глобально или локально, в зависимости от потребностей)

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

Public|Dim cnn As ADODB.Connection

Подключение к БД:

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

Set cnn = New ADODB.Connection
cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""


Отключение:

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

cnn.Close
Set cnn = Nothing


Выборка данных из листа:

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

With cnn.Execute("select * from [Sheet1$]")
  Do Until .EOF
    Debug.Print .Fields(0)
    .MoveNext
  Loop
  .Close
End With


[Lider]

Alibek!, Огромное спасибо !!!

Но небольшое уточнение- заработало Только тогда, когда вместо Sheet1$ - я поставил имя диапазона Данных!

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

Sub MySQLQuery()


'Подключение к БД:
Set cnn = New ADODB.Connection
cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

'Выборка данных:

With cnn.Execute("select * from [Данные]")
  Do Until .EOF
    Debug.Print .Fields(0) & "  |  " & .Fields(1)
    .MoveNext
  Loop
  .Close
End With

'Отключение:

cnn.Close
Set cnn = Nothing

End Sub

И мне нужна была активная книга ......
Выход нашел:

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

cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""


Еще вопросик :
Без именованного диапазона никак?
(проблема конечно решаема и с диапазоном, но хотелось бы без него)

[Lider]

Методом проб и ошибок накопал через справку и Object Brouser
то что было нужно:

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

Private cnn As ADODB.Connection
Private r As ADODB.Recordset



Sub MySQLQuery()



Set cnn = New ADODB.Connection
cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim cmd As New ADODB.Command
cmd.CommandText = "select * from [DAT]"
cmd.ActiveConnection = cnn



Set r = New ADODB.Recordset
Set r = cmd.Execute(0, 0, 0)



ActiveSheet.Cells(20, 1).CopyFromRecordset r



With cnn.Execute("select * from [DAT]")
  Do Until .EOF
    Debug.Print .Fields(0) & "  |  " & .Fields(1)
    .MoveNext
  Loop
  .Close
End With

cnn.Close
Set cnn = Nothing



End Sub

Но одного не понял

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

Set r = cmd.Execute(0, 0, 0)

Что значат эти параметры?
В справке ни ЧЕ не понял !!!!

[Lider]

Ну подскажите как без именованного диапозона?
И ЧТО ЗА ПАРАМЕТРЫ

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

Set r = cmd.Execute(0, 0, 0)

А ВООБЩЕ Я В ПОЛНОМ ВОСТОРГЕ

[Lider]

Да , чтобы уж совсем все было ясно - мож кто-нть объяснит, что же там понаписано в ConnectionString ?

[Lider]

Народ ! ну плиз, ответте на последние вопросы, или скажите , что я совсем ламер !

[Igor_123]

При офисе есть програмка - MSQRY32.EXE, если не поставил, достав.
В ней можеш писать запросы как надо и смотреть SQL-код

[Lider]

И все таки ....
что такое

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

cmd.Execute(0, 0, 0)

И что значит

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

cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

запись в connectionstring

[alibek]

Lider, сходи на http://www.connectionstrings.com , там все подробно расписано.