MyTetra Share
Делитесь знаниями!
VBA >>
13.12.2018
21:46
Раздел: !Закладки - SQL Server

Работа с внешними источниками данных Материалы по работе с внешними источниками данных на примере 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

Так же в этом разделе:
 
MyTetra Share v.0.52
Яндекс индекс цитирования