Существует масса способов извлечь данные из внешних источников в лист 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 или с другим расширением). Находим и выбираем его. Жмем ОК, и получаем те же данные, что и из файла Access.