Excel запрос к текущей книге

Обновлено: 08.07.2024

Представьте себе ситуацию, Вы получили целевую выборку из одной базы данных, но для полноты картины, как всегда, нужны дополнительные данные. Проблема может быть в том, что нужная информация хранится в другой базе данных и возможности создать на ней свою таблицу нет, подключиться используя link тоже нельзя, да и количество элементов, по которым нужно получить данные, несколько больше, чем допустимое на данном источнике. Вот и получается, что возможность написать SQL запрос и получить нужные данные есть, но написать придется не один запрос, а потом потратить время на объединение полученных данных.

Выйти из подобной ситуации поможет Excel.

Уверен, что ни для кого не секрет, что MS Excel имеет встроенный модуль VBA и надстройки, позволяющие подключаться к внешним источникам данных, то есть по сути является мощным инструментом для аналитики, а значит идеально подходит для решения подобных задач.

Для того чтобы обойти проблему, нам потребуется таблица с целевой выборкой, в которой содержатся идентификаторы, по которым можно достаточно корректно получить недостающую информацию (это может быть уникальный идентификатор, назовем его ID, или набор из данных, находящихся в разных столбцах), ПК с установленным MS Excel, и доступом к БД с недостающей информацией и, конечно, желание получить ту самую информацию.

Создаем в MS Excel книгу, на листе которой размещаем таблицу с идентификаторами, по которым будем в дальнейшем формировать запрос (если у нас есть уникальный идентификатор, для обеспечения максимальной скорости обработки таблицу лучше представить в виде одного столбца), сохраняем книгу в формате *.xlsm, после чего приступаем к созданию макроса.

Через меню «Разработчик» открываем встроенный VBA редактор и начинаем творить.

Sub job_sql() — Пусть наш макрос называется job_sql.

Пропишем переменные для подключения к БД, записи данных и запроса:

Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String

Опишем параметры подключения:

Объявим процедуру свойства, для присвоения значения:

Set cn = New ADODB.Connection cn.Provider = " SQLOLEDB.1" cn.ConnectionString = sql cn.ConnectionTimeout = 0 cn.Open

Вот теперь можно приступать непосредственно к делу.

Как вы уже поняли конечное значение i=1000 здесь только для примера, а в реальности конечное значение соответствует количеству строк в Вашей таблице. В целях унификации можно использовать автоматический способ подсчета количества строк, например, вот такую конструкцию:

Тогда открытие цикла будет выглядеть так:

Как я уже говорил выше MS Excel является мощным инструментом для аналитики, и возможности Excel VBA не заканчиваются на простом переборе значений или комбинаций значений. При наличии известных Вам закономерностей можно ограничить объем выгружаемой из БД информации путем добавления в макрос простых условий, например:

Итак, мы определились с объемом и условиями выборки, организовали подключение к БД и готовы формировать запрос. Предположим, что нам нужно получить информацию о размере ежемесячного платежа [Ежемесячный платеж] из таблицы [payments].[refinans_credit], но только по тем случаям, когда размер ежемесячного платежа больше 0

sql = "select [Ежемесячный платеж] from [PAYMENTS].[refinans_credit] " & _ "where [Ежемесячный платеж]>0 and [Номер заявки] ='" & Cells(i, 1) & "' "

Если значений для формирования запроса несколько, соответственно прописываем их в запросе:

"where [Ежемесячный платеж]>0 and [Номер заявки] = '" & Cells(i, 1) & "' " & _ " and [Дата платежа]='" & Cells(i, 2) & "'"

В целях самоконтроля я обычно записываю сформированный макросом запрос, чтобы иметь возможность проверить его корректность и работоспособность, для этого добавим вот такую строчку:

В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример - одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью всю модель данных со всеми файлами) - то придется для каждого запроса к отдельному файлу менять источник. Это можно сделать либо через Параметры источника данных самого запроса:

Для пользователей Excel 2010-2013:
Перейти на вкладку Power Query -группа Настройки (Options) -Параметры источника данных (Data Source Settings)
для пользователей 2016 и выше:
Перейти на вкладку Данные (Data) -Создать запрос (New Query) -Параметры источника данных (Data Source Settings)

либо изменив текст каждого запроса в редакторе запросов, изменив там строку источника:

И в том и в другом случае после смены каждого источника придется ждать обновления запросов. Как ни странно, но стандартно, без танцев с бубном, сделать некий относительный путь(указав лишь часть пути, как это делается в web-программировании) к источнику данных нельзя.

Как же вообще сделать возможность обновления источника данных при смене расположения файлов?
Есть два варианта:
Вариант 1
Не совсем автоматический, но способный чуть облегчить жизнь - использовать возможность создания параметров для запросов. Параметр - это некая именованная константа, которая может быть как одним значением(число, текст, дата и т.д.), так и целой таблицей(возможно впоследствии добавят возможность создания вычисляемых параметров, но на момент написания статьи это не поддерживалось). В нашем случае это будет одно значение - путь к основной папке. Чтобы создать новый параметр необходимо перейти в редактор запросов(выбрать любой запрос в книге -Правая кнопка мыши -Изменить) и на вкладке Главная выбрать Управление параметрами -Создать параметр.

В появившемся окне задаем необходимые значения:

Обязательно - читать как Обязательный, если флажок установлен и Необязательный, если флажок снят. Отвечает за необходимость указывать значение. Если флажок установлен - то параметр не будет создан/изменен, пока мы не укажем Текущее значение. Если флажок снят - то значение параметра допускается оставить пустым.
Имя параметра - Без комментариев, я указал здесь sPath
по желанию добавляем описание к параметру, ровным счетом это ни на что не влияет
Тип данных параметра - в нашем случае это Текст. Здесь лучше не рисковать, указывая Любой(Any), т.к. в этом случае можем получить ошибку запроса.
Предлагаемые значения - выбираем Любое значение(логичнее было бы назвать этот пункт "Одно значение". Т.к. это больше подходит на мой взгляд).
Текущее значение - это как раз то значение, которое и будет хранить наш параметр. Здесь я указываю путь к основной папке: G:\Готовая модель ПланФактного анализа\ . Указываю со слешем на конце пути, чтобы не добавлять его в каждый источник после.

Теперь останется для каждого запроса добавить ссылку на этот параметр вместо жестко прописанного пути. Для этого в расширенном редакторе запросов(Главная -Расширенный редактор) для каждого запроса необходимо изменить переменную часть пути к файлам на наш параметр sPath:

Обращаю внимание, что после имени параметра(sPath) идет амперсанд(&). Он необходим для объединения двух текстовых строк в одну.
А теперь пара ложек дегтя для этого способа:

Самый очевидный недостаток: при переносе файлов в любом случае необходимо будет заходить в редактор запросов (Главная -Управление параметрами -Изменить параметры) и изменять путь, указанный в параметре на новый. Так же это можно сделать напрямую из редактора запросов, раскрыв в левой части область запросов, выбрав параметр и вписав новое значение:
И не очевидный недостаток: частенько такой подход приводит к ошибке получения данных при слиянии связанных запросов. Что делает этот способ не жизнеспособным в полной мере для большинства распространенных задач

Вариант 2
Создать еще один запрос в основной книге, на основании формул в Excel. Решение основано на возможностях встроенной функции Excel ЯЧЕЙКА (CELL) . С её помощью можно получить полный путь к файлу, имя листа и книги. Чуть более подробно синтаксис этой функции и способы получения имени листа и книги я описывал в этой статье: Как получить имя листа формулой.
Итак, для начала нам необходимо на новом листе создать новую таблицу с двумя столбцами: значение и описание. В качестве значения в первой строке у нас и будет как раз формула с функцией ЯЧЕЙКА:
=ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)
данная формула возвращает только путь к файлу, в котором записана. Что нам в общем-то от неё и надо.

При желании можно дописать подсказку к значению. Я это сделал для демонстрации и чтобы было понятно - что мы сможем потом дополнять эту таблицу другими переменными значениями при необходимости.

Теперь из этой таблицы необходимо сделать динамическую, или как их еще называют - умную: выделяем ячейки с данными( A1:B2 ) -вкладка Вставка (Insert) и выбрать Таблица (Table) . Галочку "Мои данные содержат заголовки" оставляем включенной, даем понятное имя таблице - Parameters:

Далее выделяем любую ячейку внутри созданной таблицы и создаем новый запрос: вкладка Данные -Из таблицы(для пользователей PowerQuery 2013 и 2010 - вкладка PowerQuery -Из таблицы). Имя запроса у нас будет совпадать с именем таблицы - Parameters и этот запрос будет содержать как раз все значения нашей умной таблицы.
И теперь нам надо из этого запроса получить значение только одной конкретной ячейки - первой ячейки столбца "Значение"(в которой у нас формулой возвращается путь к папке). Для этого придется чуть пошаманить. Нам необходимо получить ссылку на таблицу "Parameters" и уже из неё получить значение нужной ячейки. Все это придется проделать на языке M, но звучит страшнее, чем выглядит - это всего одна строка:
Excel.CurrentWorkbook()[Content][Значение]
Теперь разберем по шагам:

Т.е. строка Excel.CurrentWorkbook()[Content][Значение] означает: из книги с запросом обратиться к таблице "Parameters"( ), считать все данные( [Content] ) и отобрать оттуда значение первой строки( ) столбца "Значение"( [Значение] )
И эта строка возвращает исключительно путь к папке - именно тот, который у нас получается в результате вычисления формулы с ЯЧЕЙКА.
Теперь, после того как разобрались что делает чудо-строка - осталось понять как это применить. Надо просто для каждого запроса перейти в редактор и в строке с источником:
Источник = Folder.Files(" C:\Готовая модель ПланФактного анализа \Факт"),
вместо части пути указать созданную строку запроса, добавив амперсанд(&) для объединения разных значений:
Источник = Folder.Files( Excel.CurrentWorkbook()[Content][Значение] & "\Факт"),

Все, теперь при перемещении книги с запросом или всей модели данные будут обновляться без какого-то ручного вмешательства.
В общем-то, такой подход можно использовать для задания даже имен файлов, используемых в модели. Например, можно дополнить таблицу именем файла с данными плана или путем к вспомогательным таблицам.

Иногда вы можете открыть несколько книг одновременно, и вы можете забыть их расположение, и теперь я расскажу о методах получения активного местоположения / пути книги в Excel.

Получить расположение книги с помощью Kutools for Excel

Открыть содержащую папку с помощью Kutools for Excel

Получение местоположения книги с помощью формул

В Excel вы можете получить местоположение книги с помощью формулы.

Выберите ячейку, выберите одну из формул ниже и вставьте ее в ячейку и нажмите клавишу Enter, чтобы получить местоположение.

Чтобы получить местоположение книги

= ЛЕВЫЙ (ЯЧЕЙКА ("имя файла"; A1); НАЙТИ ("["; ЯЧЕЙКА ("имя файла"; A1)) - 1)

Чтобы получить расположение книги и имя книги

= ПОДСТАВИТЬ (ЛЕВЫЙ (ЯЧЕЙКА ("имя файла"; A1); НАЙТИ ("]", ЯЧЕЙКА ("имя файла"; A1)) - 1), "[", "")

Чтобы получить расположение книги, имя книги и имя активного листа

= ЯЧЕЙКА ("имя файла"; A1)

Получить расположение книги с помощью VBA

Если вы хотите попытаться получить местоположение книги с помощью VBA, вы можете сделать следующее:

1. Включите книгу и нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно, затем щелкните Вставить > Модуль для отображения окна модуля.

2. Вставьте ниже код VBA в Модуль окно.

VBA: Показать расположение книги

3. Нажмите Запустите кнопку, затем появится диалоговое окно, в котором будет показан путь к книге.

Примечание: Если вы никогда раньше не сохраняли активную книгу, этот VBA выведет пустое диалоговое окно. Смотрите скриншот:

Если вы хотите показать расположение книги и имя книги, вы можете использовать ниже VBA:

VBA: Показать расположение книги и имя книги

Получить расположение книги с помощью Kutools for Excel

Работы С Нами Kutools for ExcelАвтора Вставить информацию о книге вы можете получить расположение книги и затем вставить его в нужную ячейку.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите ячейку, в которую нужно вставить расположение книги, и нажмите Kutools Plus > Workbook > Вставить информацию о книге. Смотрите скриншот:

2. в Вставить информацию о книге диалоговом окне, отметьте параметр информации, который необходимо вставить в раздел информации, а затем вы также можете указать, чтобы вставить информацию в ячейку, верхний или нижний колонтитул в Вставить в .

3. Нажмите Ok. Теперь выбранная вами информация вставлена.

Примечание: Если активная книга никогда ранее не сохранялась, эта утилита вернет текст «Эта книга никогда не сохранялась».

Вставить информацию о книге

Открыть содержащую папку с помощью Kutools for Excel

Если вы хотите открыть папку, содержащую текущую книгу, вы также можете применить Откройте папку содержащие of Kutools for Excel.

После бесплатная установка Kutools for Excel, сделайте следующее:

Активируйте книгу, в которой хотите открыть папку, и нажмите Kutools Plus > Workbook > Откройте папку содержащие. Смотрите скриншот:

Класс для работы с объектами Connection и Recordset; выполнения SQL запросов к данным эксель, текстовым файлам, базам данных и т.п.

ADO (от англ. ActiveX Data Objects — «объекты данных ActiveX») — интерфейс программирования приложений для доступа к данным, разработанный компанией Microsoft (MS Access, MS SQL Server) и основанный на технологии компонентов ActiveX. ADO позволяет представлять данные из разнообразных источников (реляционных баз данных, текстовых файлов и т. д.) в объектно-ориентированном виде. wiki

Create - создает объекты Connection и Recordset. Вызывается при создании экземпляра класса.
Connect - открывает подключение к источнику данных. Вызывается при запросе с помощью метода Query.
Destroy - уничтожает объекты Connection и Recordset. Срабатывает по событию Class_Terminate() .
Disconnect - закрывает объект Recordset и соединение с источником данных. Срабатывает в по событию Class_Terminate() .
Query - выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, в которое был выполнен запрос.
ToArray - возвращает результат запроса в виде массива.

Connection - объект соединения с источником данных.
Recordset - результат выполнения запроса.
DataSoure - источник данных. Полное имя книги эксель. По умолчанию текущая книга.
Header - учитывать заголовки (да/нет). По умолчанию нет. В этом случае заголовки полей назначаются автоматически F1 . Fn. Если да, первая строка диапазона считается заголовком поля.

В случае передачи параметра ConnectionString в метод Connect, значение свойств DataSoure и Header не учитываются, и формирование строки подключения ложиться полностью на плечи программиста.

1. Как начать работу?
Для того, чтобы начать работу с объектом ADO, надо его создать:

2. Как сделать запрос к данным текущей книги?
В данном запросе будут выбраны все данные из столбцов A:B с Листа1 текущей книги.
При этом используются настройки по умолчанию: Header = No , DataSource = ThisWorkbook.FullName .

3. Как сделать запрос к данным текущей книги используя имена полей / заголовки столбцов?

4. Как сделать запрос к данным другой книги?

5. Как сделать запрос к другим источникам данных (базе данных, текстовым файлам и т.п.)?
В данном случае формирование строки подключения ложится целиком на плечи программиста:

6. Я сделал запрос. Где результат?
Результат выполнения запроса хранится в объекте Recordset. Достучаться до него можно так:

7. Как поместить результат выполнения запроса на лист?

8. Как записать результат выполнения запроса в массив?
Например, используя родной метод getRows() объекта Recordset :

Но в этом случае массив будет иметь нестандартный вид. Чтобы получить обычный двумерный массив, можно воспользоваться методом ToArray() :

Сахар
Метод Query принимает ParamArray() , что позволяет писать запросы наглядно и достаточно лаконично (без лишней конкатенации строк):

Читайте также: