Работа с внешними источниками данных Материалы по работе с внешними источниками данных на примере Excel и SQL. Рассмотрим способы передачи данных между Excel и внешней базой данной на SQL сервере с помощью ADO.

Задача первая. Подключаемся к внешней базе данных.

Для начала надо подключиться к внешней базе данных. Подключение возможно если на компьютере установлен драйвер. Список установленных драйверов для подключения к базам данных на компьютере под управлением Windows:

Панель управления\Все элементы панели управления\Администрирование\Источники данных (ODBC)

Проверить подключение к базе данных можно простым способом. Создаем пустой файл (например, "текстовый документ.txt"), затем изменяем имя и расширение на .udl (например, "connect.udl"). Двойной клик мышкой по новому файлу, далее приступаете к настройке и проверке подключения к базе данных. После того, как удалось настроить корректное подключение к базе данных, сохраняем файл "connect.udl". Открываем файл "connect.udl" обычным текстовым редактором (например, блокнотом), и видим в строке подключения все необходимые параметры. Про подключение к внешним базам данных можно посмотреть на ресурсе ConnectionStrings . Теперь возвращаемся к нашему VBA для Excel. В редакторе VBA подключаем последнюю версию библиотеки:

 Microsoft ActiveX Data Objects Library

Пример кода:

Sub TestConnection()

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.ConnectionString = "" 'Параметры строки подключения

cn.Open 'Открываем подключение

cn.Close 'Закрываем подключение

Set cn = Nothing 'Стираем объект из памяти

End Sub

Задача вторая. Загружаем данные из внешней базы данных на SQL сервере в Excel.

После того, как мы установили подключение к внешней базе данных можно приступать к чтению данных и выводу в Excel. Здесь потребуется знание языка запросов SQL. В результате выполнения SQL запроса к нам возвращается некая таблица с данными в объект RecordSet. Далее из объекта RecordSet можно выгружать данные непосредственно на лист или в сводную таблицу. Пример кода простой процедуры:

Sub LoadData()

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Set cn = New ADODB.Connection

Set rst = New ADODB.Recordset

cn.ConnectionString = "" 'Параметры строки подключения

cn.Open

rst.Open "SELECT TOP 10 * FROM <таблица>", cn 'SQL-запрос, подключение

ActiveSheet.Range("A1").CopyFromRecordset rst 'Извлекаем данные на лист

rst.Close

cn.Close

Set rst = Nothing

Set cn = Nothing

End Sub

Для удобства работы. Предлагаю создать собственный класс "tSQL" для работы с базой данных.  У класса будет одно свойство:

Public ConnectionSring As String

Для чтения данных напишем метод SelectFrom с параметрами TableName и ws. TableName - это имя таблицы, откуда будем считывать данные и ws - лист Excel, куда будем записывать данные.

Public Sub SelectFrom(TableName As String, ws As Worksheet)

Dim cn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim SQLstring As String

Dim i As Long

Set cn = New ADODB.Connection

Set rst = New ADODB.Recordset

SQLstring = "SELECT * FROM " & TableName

ws.Cells.Clear

cn.ConnectionString = ConnectionSring

cn.Open

rst.Open SQLstring, cn

For i = 1 To rst.Fields.Count

ws.Cells(1, i) = rst.Fields(i - 1).Name

Next i

ws.Range("A2").CopyFromRecordset rst

rst.Close

cn.Close

Set rst = Nothing

Set cn = Nothing

SQLstring = Empty

i = Empty

End Sub

Пример использования класса tSQL в процедуре

Sub mySQL()

Dim ts As tSQL

Set ts = New tSQL

ts.ConnectionSring = '<Строка подключения>

ts.SelectFrom "Название таблицы", ActiveSheet

Set ts = Nothing

End Sub

Задача третья. Загружаем данные из Excel во внешнюю базу данных.

Для записи данных напишем метод InsertInto с параметрами TableName. rHead и rData. TableName - это имя таблицы, куда будем добавлять данные;  rHead - диапазон ячеек, с указанием полей; rData - диапазон ячеек с данными, которые будем добавлять.

Public Sub InsertInto(TableName As String, rHead As Range, rData As Range)

Dim cn As ADODB.Connection

Dim SQLstring As String

Dim SQLstringH As String

Dim SQLstringV As String

Dim i As Long

Dim j As Long

Dim arrHead()

Dim arrData()

arrHead = rHead.Value

arrData = rData.Value

Set cn = New ADODB.Connection

cn.ConnectionString = ConnectionSring

cn.Open

SQLstringH = "INSERT INTO " & TableName & "("

For j = LBound(arrHead, 2) To UBound(arrHead, 2)

SQLstringH = SQLstringH & " " & arrHead(1, j)

If j < UBound(arrHead, 2) Then

SQLstringH = SQLstringH & ","

Else

SQLstringH = SQLstringH & ")"

End If

Next j

SQLstringH = SQLstringH & " VALUES("

For i = LBound(arrData, 1) To UBound(arrData, 1)

For j = LBound(arrData, 2) To UBound(arrData, 2)

SQLstringV = SQLstringV & " " & arrData(i, j)

If j < UBound(arrHead, 2) Then

SQLstringV = SQLstringV & ","

Else

SQLstringV = SQLstringV & ") "

End If

Next j

SQLstring = SQLstringH & SQLstringV

SQLstringV = Empty

cn.Execute SQLstring

Next i

cn.Close

Set cn = Nothing

SQLstring = Empty

i = Empty

j = Empty

SQLstring = Empty

SQLstringH = Empty

SQLstringV = Empty

Erase arrHead

Erase arrData

End Sub

Пример использования класса tSQL в процедуре

Sub mySQL()

Dim ts As tSQL

Set ts = New tSQL

ts.ConnectionSring = '<Строка подключения>

ts.InsertInto "Название таблицы", Range("B1:D1"), Range("B8:D300")

Set ts = Nothing

End Sub

 

Задача четвертая. Управляем внешней базой данных из Excel

Рекомендую использовать запросы в основном для чтения данных из внешней БД. Можно записывать данные в таблицы внешней БД. Но крайне не желательно использовать Excel для управления внешней базой данных, лучше использовать стандартные средства разработки.

Полезные ссылки:

Data from Excel to SQL   http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm