SQL запрос с помощью VBA Excel

Описание

Существует масса способов извлечь данные из внешних источников в лист Excel . В этой статье мы рассмотрим, как запрашивать данные из файла базы данных MS Access и из файла 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 (test.mdb) .

Файл MS Access будет выглядеть так:

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

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

Sub SQLQuery_1()
'moonexcel.com.ua
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()
'moonexcel.com.ua
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
• Что такое SQL? Главные понятия.