ВВЕРХ

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

Статьи по теме:

  • Импорт данных из Access в Excel