ADO

Description

Класс для работы с объектами Connection и Recordset; выполнения SQL запросов к данным эксель, текстовым файлам, базам данных и т.п.

ADO (от англ. ActiveX Data Objects — «объекты данных ActiveX») — интерфейс программирования приложений для доступа к данным, разработанный компанией Microsoft (MS Access, MS SQL Server) и основанный на технологии компонентов ActiveX. ADO позволяет представлять данные из разнообразных источников (реляционных баз данных, текстовых файлов и т. д.) в объектно-ориентированном виде.   wiki

API

Methods:

• Create - создает объекты Connection и Recordset. Вызывается при создании экземпляра класса.
• Connect - открывает подключение к источнику данных. Вызывается при запросе с помощью метода Query.
• Destroy - уничтожает объекты Connection и Recordset. Срабатывает по событию Class_Terminate().
• Disconnect - закрывает объект Recordset и соединение с источником данных. Срабатывает в по событию Class_Terminate().
• Query - выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, в которое был выполнен запрос.
• ToArray - возвращает результат запроса в виде массива.

Properties:

• Connection - объект соединения с источником данных.
• Recordset - результат выполнения запроса.
• DataSoure - источник данных. Полное имя книги эксель. По умолчанию текущая книга.
• Header - учитывать заголовки (да/нет). По умолчанию нет. В этом случае заголовки полей назначаются автоматически F1 ... Fn. Если да, первая строка диапазона считается заголовком поля.

В случае передачи параметра ConnectionString в метод Connect, значение свойств DataSoure и Header не учитываются, и формирование строки подключения ложиться полностью на плечи программиста.

FAQ

1. Как начать работу?
Для того, чтобы начать работу с объектом ADO, надо его создать:

Dim ADO As New ADO

2. Как сделать запрос к данным текущей книги?
В данном запросе будут выбраны все данные из столбцов A:B с Листа1 текущей книги.
При этом используются настройки по умолчанию: Header = No, DataSource = ThisWorkbook.FullName.

Sub Example()
    Dim ADO As New ADO
        
    ADO.Query ("SELECT * FROM [Лист1$A:B]")
End Sub

3. Как сделать запрос к данным текущей книги используя имена полей / заголовки столбцов?

Sub Example()
    Dim ADO As New ADO
        
    ADO.Header = True
    ADO.Query ("SELECT FieldName FROM [Лист1$A:B]")
End Sub

4. Как сделать запрос к данным другой книги?

Sub Example()
    Dim ADO As New ADO
        
    ADO.DataSource = Workbook.FullName   ' полный путь к книге
    ADO.Query ("SELECT * FROM [Лист1$A:B]")
End Sub

5. Как сделать запрос к другим источникам данных (базе данных, текстовым файлам и т.п.)?
В данном случае формирование строки подключения ложится целиком на плечи программиста:

Sub Example()
    Dim ADO As New ADO
        
    ADO.Connect ("Your connection string")
    ADO.Query ("SELECT * FROM ...")
End Sub

6. Я сделал запрос. Где результат?
Результат выполнения запроса хранится в объекте Recordset. Достучаться до него можно так:

ADO.Recordset

7. Как поместить результат выполнения запроса на лист?

Sub Example()
    Dim ADO As New ADO
        
    ADO.Query ("SELECT * FROM [Лист1$A:B]")
        
    Range("A1").CopyFromRecordset ADO.Recordset    ' поместить результат выполнения запроса на лист начиная с ячейки A1
End Sub

8. Как записать результат выполнения запроса в массив?
Например, используя родной метод getRows() объекта Recordset:

Sub Example()
    Dim ADO As New ADO
    Dim Arr As Variant
        
    ADO.Query ("SELECT * FROM [Лист1$A:B]")
        
    Arr = ADO.Recordset.getRows()    ' записать результат выполнения запроса в массив
End Sub

Но в этом случае массив будет иметь нестандартный вид. Чтобы получить обычный двумерный массив, можно воспользоваться методом ToArray():

Sub Example()
    Dim ADO As New ADO
    Dim Arr As Variant
        
    ADO.Query ("SELECT * FROM [Лист1$A:B]")
        
    Arr = ADO.ToArray()
End Sub

Сахар
Метод Query принимает ParamArray(), что позволяет писать запросы наглядно и достаточно лаконично (без лишней конкатенации строк):

Sub Example()
    Dim ADO As New ADO
        
    ADO.Query "SELECT F1", _
              "FROM [Sheet1$A:B]", _
              "WHERE F2 > 0"
End Sub

Related links

Использование ADO с данными Excel из Visual Basic или VBA
Использование ADO.NET для извлечения и модификации записей книги Excel с помощью Visual Basic .NET
Передача данных из набора записей ADO в Excel средствами автоматизации
Использование библиотеки ADO (Microsoft ActiveX Data Object)
ADO API Reference
ADO Tutorial