Vba sql запрос к листу excel

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Как в sql запрос вставить еще один запрос правильно?
Есть длинный запрос, в котором можете даже не разбираться если не хотите, но выдает он следующее.

SQL запрос по фрагменту строки сохраненной в таблице SQL
Прошу помощи. Есть SQL таблица-1, в ней есть столбец по имени Model_Vagona с типом данных ntext.

SQL запрос, работающий в MS SQL Menegment’e не работает в делфи
Требуется выполнить запрос по нажатию кнопки, запрос сначала написал в Microsoft SQL Managment, где.

SQL-запрос в SQL server management studio 2008 (if else)
Здравствуйте! Нужна помощь в правке условия, уже как более 8 часов не могу правильно составить.

Эффективная работа в MS Office

Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели

Excel VBA. SQL-запросы в подключениях

UPDATE 21.10.15 Добавил «обратный» макрос — VBA в SQL и макрос для доступа к строке запроса SQL

Некоторое время назад я прошел несколько курсов по SQL. И мне было очень интересно — какую часть из мощного инструмента под названием T-SQL можно применять без использования SQL-Server (не дают мне сервачек под мои нужды, хнык-хнык).

Итак… Начнем с простого — подключение через Query Table в VBA. Можно записать через макрорекордер — для этого нужно создать подключение через Microsoft Query.

Выбираем Excel Files, указываем путь к файлу (пытаясь при этом не ругать разработчиков за интерфейс из 90х годов), фильтруем как-угодно поля. Нам сейчас это не важно — главное получить код, который дальше можно будет корректировать.

Должно получится что-то вроде этого:

Строчка .CommandText = «SELECT…» — отвечает за SQL запрос. Если хотя бы немного почитать поисковую выдачу google по запросу QueryTable можно упростить код до следующего:

Теперь начинаем копаться глубже — какого уровня запросы можно строить из VBA. Самые-самые базовые, основные конструкции — все работает, все ок.

Заполнение нового столбца одинаковым значением

Переименование столбцов

Фильтрация записей

Сортировка

Агрегация записей

Дату можно впрямую через конструкцию

Но я люблю отталкиваться от текущей даты. За пару текущая дата-время отвечает функция SYSDATETIME() и она может вернуть в том числе текущий день. Для этого нужна еще одна функция — CONVERT(type,value)

С функцией DATEFROMPARTS строка запроса в Excel почему-то не дружит, поэтому придется использовать костыли функцию DATEADD:

Эта строчка в любой день октября 2015 вернет значение — 30.11.15 23:59

А теперь — немного best practice!

Объединение + Агрегация + Join + Подзапросы. И самое интересное — подключение к нескольким источникам:

Одна проблема — если осуществлять такого вида запрос для соединения нескольких Excel-файлов, он будет выполняться достаточно медленно. У меня вышло порядка 2 минут. Но не стоит думать что это бесполезно — если подобные запросы выполнять при подключении к SQL-серверу, то время обработки будет 1-2 секунды (само собой, все зависит от сложности запроса, базы, и прочие прочие факторы).

Бонусы

Формировать более-менее сложный запрос SQL вручную в VBA мягко говоря неудобно. Поэтому я написал мини-макрос, который берет информацию из буфера обмена, и возвращает туда строчки для вставки в VBE.

Сами запросы просто и удобно создавать, например, используя Notepad++. Создали многострочный запрос SQL, копируете его в буфер обмена, запускаете макрос и вуаля — в буфере обмена строчки кода, готовые для вставки в ваши макросы. При желании вы можете настроить название переменной и количество табуляций.

И еще один небольшой бонус. Если у вас есть отчет по менеджерам/руководителям, построенный на запросах, то вам наверняка потребуется получать доступ к строке запроса через VBA. Сделать это можно через замечательную команду .CommandText — работает на чтение и запись. Мне для формирования отчета на 25 человек очень пригодился.

VBA в Excel Объект Excel.QueryTable, импорт значений в Excel из базы данных средствами VBA

10.7 Коллекция QueryTables и объект QueryTable

Объект Excel.QueryTable, программный импорт значений в Excel из базы данных средствами VBA, свойства, методы и события объекта Excel.QueryTable

Для большинства практических задач вполне хватает возможностей объектов Application, Workbook, Worksheet и Range. Например, для вставки информации из базы данных вы можете пройти циклом по объекту ADO.Recordset и вставить все нужные записи в лист Excel, а затем средствами VBA прописать в строки внизу итоги по вставленным данным. Однако в Excel встроено еще несколько важных специальных объектов, которые могут сильно упростить работу в различных ситуациях. Например, ту же операцию с по вставке информации из базы данных удобнее будет провести при помощи специального объекта QueryTable, который рассматривается в этом разделе. Еще два таких специальных объекта — объекты PivotTable и Chart рассматриваются в следующих разделах.

Основное назначение объекта QueryTable — работа с набором значений, возвращаемых из базы данных. Этот объект доступен в Excel и при помощи графического интерфейса через меню Данные -> Импорт внешних данных -> Импортировать данные. При помощи объектов QueryTable вы можете разместить набор записей, полученных с источника данных, на листе Excel для выполнения с ним различных операций (например, анализа). QueryTable удобно использовать для «односторонней» работы с источником данных, когда данные только скачиваются с источника в Excel, но изменять их с сохранением изменений на источнике не нужно. В Excel такую возможность синхронизации изменений реализовать можно (например, при помощи перехвата события Change объекта Worksheet), но намного проще (и правильнее) использовать для этой цели возможности Access. Обычно данные помещаются в Excel для проведения анализа (при помощи богатой библиотеки функций), для построения диаграмм, иногда — отчетов и т.п. В этом разделе мы будем рассматривать только такую «однонаправленную» передачу данных из базы данных в Excel.

Как обычно, для того, чтобы создать объект QueryTable и разместить его на листе, нужно использовать специальную коллекцию QueryTables, которая принадлежит рабочему листу (объекту Worksheet) и доступна через его одноименное свойство. Свойства и методы объекта QueryTables — стандартные, как у большинства рассмотренных нами коллекций. Подробного рассмотрения заслуживает только метод Add(), при помощи которого и создается объект QueryTable (с одновременным добавлением в коллекцию). Этот метод принимает три параметра:

• Connection — источник данных для QueryTable (в виде объекта типа Variant). В качестве источника данных можно использовать:
  • строку подключения OLE DB или ODBC (строка подключения ODBC должна начинаться с » ODBC;», а в остальном — все точно так же, как в главе про ADO);
  • готовый объект Recordset, созданный стандартными средствами ADO или DAO. При этом можно изменять Recordset, на который ссылается QueryTable и обновлять QueryTable. По многим причинам это — самый удобный вариант при работе с QueryTable;
  • другой объект QueryTable (вместе со строкой подключения и текстом запроса);
  • текстовый файл;
  • результаты Web-запроса или запроса Microsoft Query (в виде файла *. dqy или *. iqy). Создать такой файл запроса можно при помощи графических средств Excel: меню Данные ->Импорт внешних данных ->Создать запрос.
• Destination — куда вставлять полученную QueryTable. Передается объект Range, и вставка производится начиная с верхнего левого угла этой ячейки.
• SQL — при помощи этого необязательного параметра можно определить SQL-запрос, который будет выполняться к источнику данных ODBC. Тот же запрос можно определить при помощи одноименного свойства объекта QueryTable.

Конечно, правильнее всего при создании QueryTable использовать готовый объект Recordset. В этом случае у нас — и самые полные возможности настройки подключения и курсора, и возможность очень эффективного промежуточного хранения данных в оперативной памяти (в объекте Recordset), куда можно вносить изменения, и все очень удобные свойства и методы объекта Recordset. Код на создание объекта QueryTable на листе Excel может выглядеть так (мы используем тот же Recordset на основе таблицы Northwind.Customers, что и в модуле про ADO):

Dim cn As ADODB.Connection

Set cn = CreateObject(«ADODB.Connection»)

& «Initial Catalog = Northwind»

Dim rs As ADODB.Recordset

Set rs = CreateObject(«ADODB.Recordset»)

rs.Open «select * from dbo.customers», cn

Dim QT1 As QueryTable

Set QT1 = QueryTables.Add(rs, Range(«A1»))

Непосредственно помещение объекта QueryTable на лист производится при помощи метода QueryTable.Refresh(). Без него объект QueryTable будет создан только в оперативной памяти.

Теперь — о самых важных свойствах и методах объекта QueryTable:

• BackgroundQuery — может ли выполнение запроса производится в фоновом режиме, пока пользователь выполняет в Excel другие действия. По умолчанию true, в false следует переводить только тогда, когда пользователь действиями в Excel может как-то помешать нормальной работе приложения.
• CommandText — текст команды SQL, то есть текст запроса, который передается на источник. Сосуществует совместно с аналогичным свойством SQL (которое оставлено для обратной совместимости) и имеет перед ним приоритет. При передаче QueryTable готового Recordset недоступно.
• CommandType — тип передаваемой в CommandText команды (вся таблица, SQL-запрос, имя куба и т.п.). При работе с готовым Recordset также недоступно.
• Connection — строка подключения, та самая, которую можно передать при вызове метода Add() коллекции QueryTables. Опять-таки при работе с готовым Recordset недоступно.
• Destination — второй параметр, который передавался методу Add(). Возвращает объект Range, представляющий первую (верхнюю левую ячейку) диапазона, занимаемого на листе объектом QueryTable. После создания QueryTable доступен только на чтение.
• EnableEditing — может ли пользователь изменять на графическом экране свойства объекта QueryTable. Если перевести в false (по умолчанию true), то пользователь сможет только обновлять QueryTable.
• EnableRefresh — может ли пользователь обновлять QueryTable, получая заново данные (с источника или Recordset);
• FetchedRowOverflow — это свойство принимает значение true, если записи, полученные с источника, не уместились на листе Excel (было скачано больше, чем 65536 записей). Ошибки в такой ситуации не возникает, поэтому если вы работаете с большими наборами записей, то есть смысл реализовать соответствующие проверки.
• FieldNames — очень полезное свойство. Позволяет отключить вставку полученных с источника названий столбцов в первую строку QueryTable. По умолчанию true (вставлять названия столбцов).
• MaintainConnection — это свойство определяет, будет ли соединение с источником открыто все время до закрытия листа. По умолчанию true — оптимизировано под выполнение частых обновлений. Если переставить в false, можно сэкономить оперативную память на клиенте за счет скорость обновления данных.
• Name — имя объекта QueryTable (на графическом экране его можно просмотреть, если в панели управления Внешние данные нажать на кнопку Свойства диапазона данных). По умолчанию — ExternalData_номер.
• Parameters — возможность получить доступ к коллекции Parameters, набору параметров запроса. Возможности практически такие же, как для работы с параметрами объекта Recordset.
• PreserveColumnInfo и PreserveFormatting — сохранять ли информацию о столбцах (сортировке, фильтрации и т.п.) и форматировании после обновления QueryTable. По умолчанию — все сохранять.
• QueryType — возможность выяснить (свойство доступно только на чтение), что использовалось при создании QueryTable — Recordset, прямой доступ к таблице, SQL-запрос и т.п.
• Recordset — возможность получить ссылку на объект Recordset, который использовался для создания QueryTable или сменить его для объекта QueryTable (изменения вступят в силу только после вызова метода Refresh()).
• Refreshing — это свойство принимает значение true на момент выполнения фонового запроса к источнику. Если выполнение запроса слишком затянулось, его можно прервать при помощи метода CancelRefresh().
• RefreshOnFileOpen — обновлять ли данные автоматически при открытии листа или можно обойтись уже скачанными значениями (по умолчанию).
• RefreshPeriod — через какие интервалы времени автоматически обновлять информацию в QueryTable данными с источника. По умолчанию 0 — то есть автоматическое обновление отключено.
• RefreshStyle — определить, что делать с существующими ячейками, на место которых вставляются ячейки QueryTable при обновлении.
• ResultRange — пожалуй, самое важное свойство объекта QueryTable. Как правило, данные из базы данных перекачиваются в Excel для дальнейшей обработки. Это свойство позволяет получить диапазон, который включает в себя все ячейки, вставленные на лист из объекта QueryTable, чтобы потом применить к ним различные функции (обычно по столбцам или по строкам). Чтобы этот метод сработал, обязательно нужно провести вставку данных QueryTable на лист при помощи метода Refresh. После этого можно использовать то, что возвращает это свойство, как обычный диапазон. Самый простой способ продемонстрировать работу эту метода — воспользоваться кодом

А такой пример генерирует под первым столбцом QueryTable формулу с суммированием значений этого первого столбца:

Set c1 = Sheets(«Лист1»).QueryTables(1).ResultRange.Columns(1)

c1.End(xlDown).Offset(1, 0).Formula = «=SUM(Column1)»

• RowNumbers — свойство, которое может сильно упростить работу с данными, полученными при помощи QueryTable. Позволяет сгенерировать еще один столбец в QueryTable (слева), который будет состоять из номеров записей, полученных через QueryTable.
• SaveData — сохранять ли данные, полученные через QueryTable, вместе с книгой Excel. По умолчанию True. В False есть смысл переводить для того, чтобы изначально гарантировать работу пользователя только с самыми последними данными, полученными из источника.
• SavePassword — сохранять ли пароль вместе со строкой подключения (это свойство можно использовать только для источников ODBC). Если переставить его в False, можно повысить уровень безопасности вашего приложения.
• SourceDataFile — полный путь и имя файла источника (для Access, DBF и прочих настольных СУБД). Для клиент-серверных систем (таких, как SQL Server), возвращает Null.
• многочисленные свойства, которые начинаются на Text…, определяют параметры текстового файла, если этот файл выбран в качестве источника для QueryTable.
• свойства Web… определяют параметры данных, получаемых от запроса к Web-источнику.

Методы объекта QueryTable (Refresh(), CancelRefresh(), Delete()) очевидны и каких-либо комментариев не требуют. Метод ResetTimer() позволяет обнулить таймер автоматического обновления, а метод SaveAsODC() позволяет сохранить определение источника данных в виде файла Microsoft Query (если источником был объект Recordset, то этот метод вернет ошибку).

У объекта QueryTable есть также два события: BeforeRefresh и AfterRefresh. Они срабатывают соответственно перед началом загрузки данных с источника и после окончания загрузки.

Vba sql запрос к листу excel

Не то, чтобы готовое решение, но тем не менее.
Цель — простота и удобство работы с SQL из Excel.
Текущая версия 0.3

Методы:

• Create — создает объект подключения. Автоматически вызывается при инициализации.
• Connect — открывает соединение. Автоматически вызывается при запросе.
• Destroy — уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.
• Disconnect — закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.
• Query — выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.

Свойства:

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

Пример работы
[vba]

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Sub Example()
Dim ADO As New ADO ‘ Создаем экземпляр класса

ADO.Query («SELECT F1 FROM [Лист1$];»)
Range(«E1»).CopyFromRecordset ADO.Recordset

ADO.Query («SELECT F2 FROM [Лист1$];»)
Range(«F1»).CopyFromRecordset ADO.Recordset

‘ Закрываем соединение, чтобы не висело : )
ADO.Disconnect

ADO.Query («SELECT F1 FROM [Лист1$] UNION SELECT F2 FROM [Лист1$];»)
Range(«G1»).CopyFromRecordset ADO.Recordset

‘ Тут автоматически закроется соединение
‘ и уничтожиться объекты Recordset и Connection
End Sub

Не то, чтобы готовое решение, но тем не менее.
Цель — простота и удобство работы с SQL из Excel.
Текущая версия 0.3

Методы:

• Create — создает объект подключения. Автоматически вызывается при инициализации.
• Connect — открывает соединение. Автоматически вызывается при запросе.
• Destroy — уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.
• Disconnect — закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.
• Query — выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.

Свойства:

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

Пример работы
[vba]

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Sub Example()
Dim ADO As New ADO ‘ Создаем экземпляр класса

ADO.Query («SELECT F1 FROM [Лист1$];»)
Range(«E1»).CopyFromRecordset ADO.Recordset

ADO.Query («SELECT F2 FROM [Лист1$];»)
Range(«F1»).CopyFromRecordset ADO.Recordset

‘ Закрываем соединение, чтобы не висело : )
ADO.Disconnect

ADO.Query («SELECT F1 FROM [Лист1$] UNION SELECT F2 FROM [Лист1$];»)
Range(«G1»).CopyFromRecordset ADO.Recordset

‘ Тут автоматически закроется соединение
‘ и уничтожиться объекты Recordset и Connection
End Sub

Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина — самый громкий звук

YM 41001156540584 / WM WMR R21924176233

Сообщение Всем привет!

Не то, чтобы готовое решение, но тем не менее.
Цель — простота и удобство работы с SQL из Excel.
Текущая версия 0.3

Методы:

• Create — создает объект подключения. Автоматически вызывается при инициализации.
• Connect — открывает соединение. Автоматически вызывается при запросе.
• Destroy — уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.
• Disconnect — закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.
• Query — выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.

Свойства:

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

Пример работы
[vba]

200?’200px’:»+(this.scrollHeight+5)+’px’);»> Sub Example()
Dim ADO As New ADO ‘ Создаем экземпляр класса

ADO.Query («SELECT F1 FROM [Лист1$];»)
Range(«E1»).CopyFromRecordset ADO.Recordset

ADO.Query («SELECT F2 FROM [Лист1$];»)
Range(«F1»).CopyFromRecordset ADO.Recordset

‘ Закрываем соединение, чтобы не висело : )
ADO.Disconnect

ADO.Query («SELECT F1 FROM [Лист1$] UNION SELECT F2 FROM [Лист1$];»)
Range(«G1»).CopyFromRecordset ADO.Recordset

‘ Тут автоматически закроется соединение
‘ и уничтожиться объекты Recordset и Connection
End Sub

Оценка статьи:

Загрузка...