SQL запит в Excel VBA

Існує маса способів отримати дані із зовнішніх джерел у аркуш Excel. У цій статті ми розглянемо, як запитувати дані з файлу бази даних MS Acces та із файлу Excel за допомогою SQL у VBA. Так, ми збираємося трохи змішати SQL з VBA! І це краще, ніж ви можете подумати!

SQL розшифровується як Structured Query Language (структурована мова запитів) і є мовою, яка використовується для отримання інформації з баз даних (таких як MS Access, MS SQL Server, Oracle, Sybase, SAP, MySQL та інших).

Ви також можете отримувати дані з інтернету, текстових файлів чи інших Excel або CSV файлів.

1. SQL запит з Excel VBA до файлу MS Access

Отже, нам потрібне з'єднання з базою даних (змінна varConn в макросі нижче) та SQL запит (змінна varSQL), щоб автоматизувати отримання даних з бази для звіту. В прикладі нижче є SQL запит, що отримує дані з малої бази даних в MS Access.

Натисніть завантажити базу даних MS Access. Файл MS Access матиме такий вигляд:

Давайте напишемо свій макрос, який буде здійснювати SQL запит.

Відкрийте меню Сервіс - Макрос - Редактор Visual Basic, вставте новий модуль (меню Insert - Module) и скопіюйте туди текст макросу:

Sub SQLQuery_1()
Dim varConn As String
Dim varSQL  As String

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)}"

    varSQL = "SELECT Month, Product, City FROM Sumproduct"

         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Refresh BackgroundQuery:=False
         End With
End Sub 

Натискаємо зберегти та повертаємось до Excel. Вибираємо в меню Вигляд - Макроси (Alt+F8) назву нашого макросу "SQLQuery_1". З'явиться діалогове вікно, тиснемо База даних..., шукаємо наш файл Access та тиснемо ОК.

Тепер бачимо, що наш макрос-запит підтягнув колонки Month, Product та City з таблиці Sumproduct з бази даних test.mdb у файл Excel.

2. SQL запит з VBA до закритого файлу Excel

Аналогічно ми можемо отримувати дані зі звичайного файлу Excel, як з бази даних. Для цього код нашого макросу потрібно трохи переписати та змінити драйвер підключення з Access на Excel.

Експортуємо таблицю Sumproduct з Access в Excel та зберігаємо файл. Далі, вставляємо наш змінений код в новий модуль та запускаємо макрос:

Sub SQLQuery_2()
Dim varConn As String
Dim varSQL  As String

    Range("A1").CurrentRegion.ClearContents

    varConn = "ODBC;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}"

    varSQL = "SELECT Month, Product, City FROM Sumproduct"

         With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1"))
             .CommandText = varSQL
             .Refresh BackgroundQuery:=False
         End With
End Sub 

З'явиться вікно, в якому ми можемо вибрати наш файл Excel (має називатись Sumproduct.xlsx або з іншим розширенням). Находимо та вибираємо його. Тиснемо ОК, і отримуємо ті самі дані, як і з файлу Аccess.

Статті по темі: