Існує маса способів отримати дані із зовнішніх джерел у аркуш Excel. У цій статті ми розглянемо, як запитувати дані з файлу бази даних MS Acces та із файлу Excel за допомогою SQL у VBA. Так, ми збираємося трохи змішати SQL з VBA! І це краще, ніж ви можете подумати!
SQL розшифровується як Structured Query Language (структурована мова запитів) і є мовою, яка використовується для отримання інформації з баз даних (таких як MS Access, MS SQL Server, Oracle, Sybase, SAP, MySQL та інших).
Ви також можете отримувати дані з інтернету, текстових файлів чи інших Excel або CSV файлів.
Отже, нам потрібне з'єднання з базою даних (змінна 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.
Аналогічно ми можемо отримувати дані зі звичайного файлу 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.