Как передать параметры в запрос в SQL (Excel)

Ответ 1

Это зависит от базы данных, к которой вы пытаетесь подключиться, метода, с помощью которого вы создали соединение, и версии Excel, которую вы используете. (Также, скорее всего, версия соответствующего драйвера ODBC на вашем компьютере.)

В следующих примерах используются SQL Server 2008 и Excel 2007, как на моей локальной машине.

Когда я использовал Мастер подключения к данным (на вкладке "Данные" на ленте в разделе "Получить внешние данные" в разделе "Из других источников" ), я увидел то же самое, что и вы: кнопка "Параметры" была отключена и добавлена параметр, похожий на select field from table where field2 = ?, заставил Excel жаловаться на то, что значение параметра не было указано, а изменения не были сохранены.

Когда я использовал Microsoft Query (то же самое место, что и Мастер подключения к данным), мне удалось создать параметры, указать отображаемое имя для них и ввести значения каждый раз, когда запрос выполнялся. При добавлении свойств подключения для этого соединения кнопка "Параметры..." включена, и параметры могут быть изменены и использованы, как вам кажется.

Я также смог сделать это с помощью базы данных Access. Представляется разумным, что Microsoft Query может использоваться для создания параметризованных запросов, ударяющих по другим типам баз данных, но я не могу легко проверить это прямо сейчас.

Ответ 2

Этот пост достаточно стар, что этот ответ, вероятно, будет мало полезен для OP, но я навсегда пытался ответить на этот же вопрос, поэтому я подумал, что обновляю его своими выводами.

Этот ответ предполагает, что у вас уже есть рабочий SQL-запрос в вашем документе Excel. Существует множество учебных пособий, в которых показано, как это сделать в Интернете, и много, которые объясняют, как добавить параметризованный запрос к одному, за исключением того, что ни один из них не работает для существующего запроса OLE DB.

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

Большинство веб-ответов на этот вопрос, похоже, говорят, что вы должны добавить "?" в запросе, чтобы заставить Excel запросить у вас специальный параметр или поместить подсказку или ссылку на ячейку в [скобки], где должен быть параметр. Это может работать для запроса ODBC, но он не работает для OLE DB, возвращая "Нет значения для одного или нескольких требуемых параметров" в первом экземпляре и "Недопустимое имя столбца" xxxx "или" Неизвестный объект ", xxxx" в последних двух. Точно так же использование мифических кнопок "Параметры..." или "Редактировать запрос..." также не является вариантом, поскольку в этом случае они, как представляется, постоянно серые. (Для справки, я использую Excel 2010, но с Excel 97-2003 Workbook (*.xls))

Однако мы можем добавить ячейку параметра и кнопку с простой подпрограммой для программного обновления текста запроса.

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

Затем выберите ячейку в области внешних данных (синий), затем откройте Data- > Refresh All (выпадающее меню) → Свойства подключения..., чтобы просмотреть ваш запрос. Код в следующем разделе предполагает, что у вас уже есть параметр в вашем запросе (Connection Properties- > Definition- > Command Text) в форме "WHERE (DB_TABLE_NAME.Field_Name =" Параметры запроса по умолчанию ")" (включая круглые скобки). Очевидно, что "DB_TABLE_NAME.Field_Name" и "Default Query Parameter" должны быть разными в вашем коде на основе имени таблицы базы данных, имени поля базы данных (столбца) и некоторого значения по умолчанию для поиска, когда документ открывается (если у вас установлен автообновление). Обратите внимание на значение "DB_TABLE_NAME.Field_Name" , которое вам понадобится в следующем разделе, а также "Имя подключения" вашего запроса, которое можно найти в верхней части диалогового окна.

Закройте свойства подключения и нажмите Alt + F11, чтобы открыть редактор VBA. Если вы еще не на нем, щелкните правой кнопкой мыши на имени листа, содержащего вашу кнопку, в окне "Проект" и выберите "Просмотреть код". Вставьте следующий код в окно кода (рекомендуется копирование, поскольку одиночные/двойные кавычки являются рискованными и необходимыми).

Sub RefreshQuery()
 Dim queryPreText As String
 Dim queryPostText As String
 Dim valueToFilter As String
 Dim paramPosition As Integer
 valueToFilter = "DB_TABLE_NAME.Field_Name ="

 With ActiveWorkbook.Connections("Connection name").OLEDBConnection
     queryPreText = .CommandText
     paramPosition = InStr(queryPreText, valueToFilter) + Len(valueToFilter) - 1
     queryPreText = Left(queryPreText, paramPosition)
     queryPostText = .CommandText
     queryPostText = Right(queryPostText, Len(queryPostText) - paramPosition)
     queryPostText = Right(queryPostText, Len(queryPostText) - InStr(queryPostText, ")") + 1)
     .CommandText = queryPreText & " '" & Range("Cell reference").Value & "'" & queryPostText
 End With
 ActiveWorkbook.Connections("Connection name").Refresh
End Sub

Замените "DB_TABLE_NAME.Field_Name" и "Имя соединения" (в двух местах) с вашими значениями (необходимо включить двойные кавычки, а также пробел и знак равенства).

Замените "Ссылка на ячейку" на ячейку, где будет идти ваш параметр (пустая ячейка с начала) - моя была второй ячейкой в ​​первой строке, поэтому я положил "B1" (опять же, нужны двойные кавычки).

Сохраните и закройте редактор VBA.

Введите свой параметр в соответствующую ячейку.

Щелкните правой кнопкой мыши по вашей кнопке, чтобы назначить опцию RefreshQuery в качестве макроса, затем нажмите кнопку. Запрос должен обновлять и отображать правильные данные!

Примечания: Использование всего имени параметра фильтра ( "DB_TABLE_NAME.Field_Name =" ) необходимо только в том случае, если в вашем запросе есть соединения или другие вхождения знаков равенства, иначе просто знак равенства будет достаточным, и вычисление Len() будет излишним. Если ваш параметр содержится в поле, которое также используется для объединения таблиц, вам нужно будет изменить строку "paramPosition = InStr (queryPreText, valueToFilter) + Len (valueToFilter) - 1" в коде на "paramPosition = InStr ( Right (.CommandText, Len (.CommandText) - InStrRev (.CommandText," WHERE ")), valueToFilter) + Len (valueToFilter) - 1 + InStr (.CommandText," WHERE ")", так что он ищет только значениеToFilter после "ГДЕ".

Этот ответ был создан с помощью datapigs "BaconBits", где я нашел базовый код для обновления запроса.