Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Получение данных из SQL по условию в ячейках
 
Доброе утро всем!
Подскажите пожалуйста, можно ли получить данные из SQL по условию в ячейках Excel? Просто получать данные из SQL легко. Но мне хотелось бы дать возможность пользователю указать в ячейках Excel значения, которые потом будет использоваться в запросе в качестве условий отбора данных. Возможно ли это сделать без использования VBA?
 
можно
нужно знание языка запросов Transact-SQL
общий пример:
Код
SELECT [ALL или DISTINCT] <названия колонок или *> FROM <название таблицы>
  WHERE <условие> ORDER BY <названия колонок> [ASC или DESC]
частный пример:
Код
SELECT id, fio,  comment FROM test_table
      WHERE  summa > 1500
проводит отбор id, fio, comment столбцов из таблицы test_table
- отбирает лишь те строки, в которых в столбце summa - значение >1500
p.s. запрос делается на вкладке Данные-Подключения-Добавить
(нюансы подключения зависят от источника)
Изменено: JeyCi - 7 Апр 2015 20:42:39
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Запрос написать не проблема, проблема в том что в условие WHERE хотелось бы видеть ссылку на ячейку Excel. Что-то типа:
Код
SELECT id, fio,  comment
FROM test_table
WHERE  summa > A1
 
В принципе можно и без VBA  в 2003 это просто:
Скрытый текст
Изменено: B.Key - 9 Апр 2015 17:44:19
 
Да забыл добавить в более молодых версиях, либо запрос с параметром, который будете ручками вбивать, либо VBA
 
Добрый день, я делаю так
где strCon строка подключения
Код
Dim s as string,strCon as string
s=[A1].value
strCon =  .....WHERE (KADRY.CEX='" & s & "').....
 
Цитата
B.Key написал: добавит
В 2013 версии не нашел такого(
 
Сие возможно только поправив в подключении строку с SQL кодом - ручками!! Оно Вам надо?

Есть еще возможность использовать отбор полученного результат по расширенному фильтру.
Изменено: TheBestOfTheBest - 7 Апр 2015 11:42:51 (добавлена строка)
Неизлечимых болезней нет, есть неизлечимые люди.
 
xl 2010 - делаю запрос макросом (пример т к отсутствует пример от ТС)
- подключение не удаляется и его далее можно обновлять вручную
Код
Public Sub CreateConnection()
 Dim sCon As String, sSQL As String
 Dim pTable As QueryTable, pSheet As Worksheet
 
sCon = IIf(Val(Application.Version) < 12, "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';", _
   "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")

sSQL = "SELECT [exp],[strike],[type],[change],[settle],[volume],[openInterest],[oi new],[oi new chg]" _
        & " FROM [sttlLoad]" _
        & " WHERE abs([oi new chg])>1000"
        
 Set pSheet = ThisWorkbook.Worksheets(2)
 Set pTable = pSheet.QueryTables.Add(sCon, pSheet.Range("A1"), sSQL)
 pTable.Refresh BackgroundQuery = False
End Sub
на таблице - результате запроса ПКМ - "Параметры" - почему-то не подсвечена??

может, если запрос настроить вручную - то можно более тонкую настройку сделать? к сожалению не знаю, как вручную??...
если опишите КАК - заранее спасибо - можно будет подумать и дальше...  (чтобы условие WHERE задать в ячейке)
P.S. если по коду указывать переменную - см. пост#6
Изменено: JeyCi - 7 Апр 2015 12:11:25
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
запрос через ODBC, сделанный макросом - тоже не даёт залезть в ПКМ-Параметры... (не подсвечен, как на скрине поста предыдущего)
Скрытый текст
возможно какой-то способ есть для xl2010?
возможно и для 2013 он подойдёт...
Изменено: JeyCi - 7 Апр 2015 19:30:09
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
B.Key написал: Выделите ячейку диапазона внешних данных, полученного при помощи запроса с параметрами.
вот, кстати, наверно, и ответ на вопрос... в самом запросе должны быть (изначально) указаны Параметры... тогда похоже и будет доступ к ним через ПКМ... если не ошибаюсь... ЧТО xl понимает как Параметры запроса?? и как прописать? - Xapa6apga приводил примеры в ветках:
CreateParameter Null  - 14 Фев 2015 17:58:45
Экспорт из Excel в SQL Server - 3 Апр 2015 15:15:03
а дальше, полагаю, по вкусу... - надо ли
p.s. Формирование параметрических запросов
P.P.S здесь почти кратко от Jom (30 Июл 2010 08:40:37  SQL запрос из Excel VBA)
Изменено: JeyCi - 7 Апр 2015 20:20:53
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
MS QUERY, на базе которого осуществляется подключения, такое не допускает. Используйте Дополнительный/Расширенный фильтр.
Неизлечимых болезней нет, есть неизлечимые люди.
 
как вариант http://www.excelworld.ru/forum/2-12367-1 (пример от Gustav  13.08.2014, 12:16 )
- сам запрос прописать в ячейках, но макрос обновления параметров запроса тоже понадобится (он коротенький - там же по линку)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Макросом можно слепить любую SQL команду, но изначально "Возможно ли это сделать без использования VBA?"
Неизлечимых болезней нет, есть неизлечимые люди.
 
Цитата
TheBestOfTheBest но изначально "Возможно ли это сделать без использования VBA?"
----------
Можно все же сделать и в 2007 и в 2010 и наверное 2013(не проверял)
Принцип тот же, что и описывал для 2003.
1. Создаем подключение через ms query (важно !!!)
2. Выбираем нужную таблицу, отбираем необходимые поля.
3. Доходим до условий отбора данных, выбираем нужное поле и значение параметра вводим "?"
4. жмем далее, где запросит вводим значение параметра.
5. Когда данные получены заходим в подключения, выбираем нужное, далее свойства, вкладка "определение"
6. Наблюдаем строку подключения и текст запроса (в условии where видим ?) значит все идет гладко :)
7. В низу три кнопки , жмем среднюю "Параметры..."
8. Вуаля :) наблюдаем заветное меню описанное мною в прошлом посте №4
9. Выбираем нужную нам ячейку, ставим если надо галку автоматического обновления
10. Жмем "ОК" и наслаждаемся результатом.

--------
Если надо то завтра скринами подкреплю.

ps.... кол-во параметров можно изменять, добавляя их ms query и делать ссылки на добавленные параметры как описано выше
Изменено: B.Key - 8 Апр 2015 23:53:04 (добавка)
 
Цитата
3. Доходим до условий отбора данных, выбираем нужное поле и значение параметра вводим "?"
Запнулся на шаге 3. Где это?
Изменено: Андрей Панькин - 9 Апр 2015 09:24:45
 
Доброе время суток
У меня в Microsoft Query ? не прошёл, но получилось так
Но если Connection было получено другим путём (не через Microsoft Query), то да обычное замещение значений полей фильтра на ?
Увы, не работает:cry:
msquery.png (15.31 КБ)
Изменено: Андрей VG - 9 Апр 2015 09:56:49
 
Андрей VG,  
Цитата
B.Key написал: 1. Создаем подключение через ms query (важно !!!)
:) поэтому и написал.
 
Все разобрался, все работает как задумано. Класс! Спасибо огромное всем. Теперь будет юзерам счастье:D
 
Эту тему можно и в копилку добавить, так на самом деле информации в сети нет.
 
Это точно
 
Можно  и программно, только непонятно, почему с ODBC работает, а OLEDB не хочет. В инете ответа не нашёл. Пример для файла Excel
Код
Public Sub test()
    Const sODBC = "ODBC;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=$1;"
    Dim pSheet As Worksheet, pLO As ListObject
    Dim pQTable As QueryTable
    Set pSheet = ThisWorkbook.Worksheets.Add
    Set pLO = pSheet.ListObjects.Add(xlSrcExternal, sODBC, True, xlYes, pSheet.Range("A1"))
    Set pQTable = pLO.QueryTable
    With pQTable.Parameters
        .Add "Имя", XlParameterDataType.xlParamTypeVarChar
        .Item(1).SetParam xlRange, pSheet.Range("E1")
        .Item(1).RefreshOnChange = True
        .Add "Стоимость", XlParameterDataType.xlParamTypeDouble
        .Item(2).SetParam xlRange, pSheet.Range("F1")
        .Item(2).RefreshOnChange = True
    End With
    With pQTable.WorkbookConnection.ODBCConnection
        .CommandType = xlCmdSql
        .Connection = Replace$(sODBC, "$1", "c:\Projects\db.xlsx")
        .CommandText = Array("Select * From [Лист1$]", " Where ([Лист1$].name=?) And ([Лист1$].cost>?)")
        .BackgroundQuery = True
        .EnableRefresh = True
    End With
    pQTable.Refresh
End Sub

Можно потом сохранить в файл подключения и править "ручками" по мере необходимости.
 
Цитата
Если надо то ... скринами подкреплю.
как-то так
Скрытый текст
Изменено: JeyCi - 9 Апр 2015 17:31:57
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Цитата
Андрей VG написал: только непонятно, почему с ODBC работает, а OLEDB не хочет. В инете ответа не нашёл.
можно лишь предполагать: (на основе моего 1-го скрина из #23, в котором видно [по тексту 8)], что через OLEDB можно только подключиться, а через ODBC- можно настроить запрос с помощью MS Query, который сделан для работы через ODBC)... Вобщем, думаю MS в xl2010 просто о бОльшем ещё не подумали - только о возможности "(через ODBC) иметь прямую связь с источником данных, и подключение хранить в кэше"... возможно, в xl2013 они как-нибудь и шагнули вперёд... лишь версия (в смысле гипотеза)

за макрос спасибо! - я вчера тоже думала об этой строке (не смогла найти правильную интерпретацию)... :) значит интуиция не подвела - через .CommandText можно задать, как угодно... жаль, что через OLE DB не срабатывает
Код
.CommandText = Array("Select * From [Лист1$]", " Where ([Лист1$].name=?) And ([Лист1$].cost>?)")
Изменено: JeyCi - 9 Апр 2015 17:48:36
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, спойлер не кусается.
 
у меня вот только один вопрос остался - может быть, кто-нибудь подскажет?
в самом окне MS Query (ещё на этапе импорта БД) - скрин на примере импорта из базы Access -
можно ли в этом окне найти Конструктор запросов (как в Access)??
(помимо простого select*from where  <условие> )... что-нибудь посерьёзнее (union, join)??..
нашла лишь то, что на скрине.......

p.s. vikttur не успела подумать.. (спасибо за исправление)
Изменено: JeyCi - 9 Апр 2015 17:50:46
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
В конструкторе нет, а в запросе, где нашли пожалуйста
 
JeyCi, "не первый день замужем"!
Не в первый раз вклиниваетесь в чужую тему и задаете вопросы НЕ ПО ТЕМЕ!
Кому взбредет в голову искать ответы о конструкторе запросов в теме "Получение данных из SQL"?
Обучение - отлично, но не создавайте бардака.
 
Цитата
vikttur написал: Не в первый раз вклиниваетесь в чужую тему и задаете вопросы НЕ ПО ТЕМЕ!
когда знаете, что в Конструкторе xl - нет никаких возможностей - то легко заявить, что вопрос не по теме... а не зная, как он выглядит (этот конструктор), - нет оснований верить (в своё незнание), что ссылку на ячейку в нём не указать... если есть возможность, тему раскрыть полно согласно заявленному оглавлению - зачем плодить другие, в которых от перестановки мест слагаемых сумма не меняется... если считаете мои посты НЕ ПО ТЕМЕ - будьте добры удалите всё!..
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Не нервничайте. Как можно, пользуясь ПОИСКОМ, определить, что в этой теме есть ответ на "можно ли в этом окне найти Конструктор запросов"?
Ведь просим делить вопросы по темам не для того, чтобы тем было больше.
Страницы: 1 2 След.
Читают тему (гостей: 1)