Не то, чтобы готовое решение, но тем не менее... Цель - простота и удобство работы с SQL из Excel. Текущая версия 0.3
Методы:
Create - создает объект подключения. Автоматически вызывается при инициализации.
Connect - открывает соединение. Автоматически вызывается при запросе.
Destroy - уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.
Disconnect - закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.
Query - выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.
Свойства:
Connection - объект соединения
Recordset - результат выполнения запроса
DataSoure - источник данных. Полное имя книги эксель.
Header - учитывать заголовки (да/нет). По умолчанию нет. В этом случае имена полей назначаются автоматически F1 ... Fn. Если да, первая строка диапазона считается заголовком поля.
Пример работы [vba]
Код
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
[/vba]
Всем привет!
Не то, чтобы готовое решение, но тем не менее... Цель - простота и удобство работы с SQL из Excel. Текущая версия 0.3
Методы:
Create - создает объект подключения. Автоматически вызывается при инициализации.
Connect - открывает соединение. Автоматически вызывается при запросе.
Destroy - уничтожает объект подключения и объект записей. Вызывается автоматически при выходе из программы.
Disconnect - закрывает открытые записи и подключения. Вызывается автоматически при выходе из программы.
Query - выполняет SQL запрос. Результат запроса помещается в объект Recordset. Возвращает время, когда был выполнен запрос.
Свойства:
Connection - объект соединения
Recordset - результат выполнения запроса
DataSoure - источник данных. Полное имя книги эксель.
Header - учитывать заголовки (да/нет). По умолчанию нет. В этом случае имена полей назначаются автоматически F1 ... Fn. Если да, первая строка диапазона считается заголовком поля.
Пример работы [vba]
Код
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", а "с данными Excel при помощи SQL" - именно в этом, мне кажется, офигенная фишка подобного подхода!
Есть и ограничение у этого подхода в принципе, которое иногда бывает критичным - можно работать только с заранее СОХРАНЕННЫМИ на диске рабочими книгами.
Цитата (nerv)
Цель - простота и удобство работы с SQL из Excel.
не "с SQL из Excel", а "с данными Excel при помощи SQL" - именно в этом, мне кажется, офигенная фишка подобного подхода!
Есть и ограничение у этого подхода в принципе, которое иногда бывает критичным - можно работать только с заранее СОХРАНЕННЫМИ на диске рабочими книгами.Gustav
не "с SQL из Excel", а "с данными Excel при помощи SQL"
спасибо, исправил
Цитата (Gustav)
Есть и ограничение у этого подхода в принципе, которое иногда бывает критичным - можно работать только с заранее СОХРАНЕННЫМИ на диске рабочими книгами.
будем иметь ввиду
Цитата (Gustav)
не "с SQL из Excel", а "с данными Excel при помощи SQL"
спасибо, исправил
Цитата (Gustav)
Есть и ограничение у этого подхода в принципе, которое иногда бывает критичным - можно работать только с заранее СОХРАНЕННЫМИ на диске рабочими книгами.
еще одно ограничение: полная невозможность использовать DELETE для таблиц Excel. я совсем недавно споткнулся об эти грабли и моё огорчение ещё не развеялось.
еще одно ограничение: полная невозможность использовать DELETE для таблиц Excel. я совсем недавно споткнулся об эти грабли и моё огорчение ещё не развеялось.ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Недавно заметил неприятный нюанс: после Execute запроса (например из Экселя в Аксесс), в некоторых ситуациях результат запроса выходит отсортирован. Это неприятно в тех случаях, когда необходим запрос по типу ВПРа, пример:
[/vba] На данный момент страхуюсь дополнительной сортировкой данных в Экселе
Недавно заметил неприятный нюанс: после Execute запроса (например из Экселя в Аксесс), в некоторых ситуациях результат запроса выходит отсортирован. Это неприятно в тех случаях, когда необходим запрос по типу ВПРа, пример:
Решил выложить обновленную версию класса. На данный момент актуальная версия 0.5.
Зачем вообще он нужен? Позволяет писать меньше кода, удобно работать (на мой взгляд).
Что из себя представляет? Объект - ADO, свойства - "стандартные" Connection и Recordset. Т.е. объект ADO своего рода обертка.
Как начать работу? Для того, чтобы начать работу с объектом ADO, надо его создать: [vba]
Код
Dim ADO As New ADO
[/vba]
Как сделать запрос к данным текущей книги? В данном запросе будут выбраны все данные из столбцов A:B с Листа1 текущей книги. [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query ("SELECT * FROM [Лист1$A:B]") End Sub
[/vba] При этом используются настройки по умолчанию: Header = No, DataSource = ThisWorkbook.FullName
Как сделать запрос к данным текущей книги используя имена полей / заголовки столбцов? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Header = True ADO.Query ("SELECT FieldName FROM [Лист1$A:B]") End Sub
[/vba]
Как сделать запрос к данным другой книги? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.DataSource = Workbook.FullName ' полный путь к книге ADO.Query ("SELECT * FROM [Лист1$A:B]") End Sub
[/vba]
Как сделать запрос к "произвольным" данным (базе данных, текстовым файлам и т.п.). [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Connect ("Your connection string") ADO.Query ("SELECT * FROM ...") End Sub
[/vba] Т.е. в данном случае формирование строки подключения ложится целиком на плечи программиста.
Я сделал запрос. Где результат? Результат выполнения запроса зхранится в объекте Recordset. Достучаться до него можно так: [vba]
Код
ADO.Recordset
[/vba]
Как поместить результат выполнения запроса на лист? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Range("D1").CopyFromRecordset ADO.Recordset ' поместить результат запроса на лист начиная с ячейки A1 End Sub
[/vba]
Как записать результат выполнения запроса в массив? например, используя стандартный метод getRows() объекта Recordset [vba]
Код
Sub Example() Dim ADO As New ADO Dim Arr As Variant
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Arr = ADO.Recordset.getRows() ' записать результат выполнения запроса в массив End Sub
[/vba] Но в этом случае массив будет иметь немного нестандартный вид. Еще один способ, кот. позволяет получить "стандартный" двумерный массив: [vba]
Код
Sub Example() Dim ADO As New ADO Dim Arr As Variant
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Arr = ADO.ToArray() End Sub
[/vba]
Сахар Метод Query принимает ParamArray, что позволяет писать запросы наглядно и достаточно лаконично (без лишней конкатенации строк) [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query "SELECT F1", _ "FROM [Sheet1$A:B]", _ "WHERE F2 > 0" End Sub
[/vba]
Файл с новой версией класса во вложении. К сожалению, форум не позволяет редактировать первый пост темы, поэтому обновления доки не будет (на форуме).
Решил выложить обновленную версию класса. На данный момент актуальная версия 0.5.
Зачем вообще он нужен? Позволяет писать меньше кода, удобно работать (на мой взгляд).
Что из себя представляет? Объект - ADO, свойства - "стандартные" Connection и Recordset. Т.е. объект ADO своего рода обертка.
Как начать работу? Для того, чтобы начать работу с объектом ADO, надо его создать: [vba]
Код
Dim ADO As New ADO
[/vba]
Как сделать запрос к данным текущей книги? В данном запросе будут выбраны все данные из столбцов A:B с Листа1 текущей книги. [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query ("SELECT * FROM [Лист1$A:B]") End Sub
[/vba] При этом используются настройки по умолчанию: Header = No, DataSource = ThisWorkbook.FullName
Как сделать запрос к данным текущей книги используя имена полей / заголовки столбцов? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Header = True ADO.Query ("SELECT FieldName FROM [Лист1$A:B]") End Sub
[/vba]
Как сделать запрос к данным другой книги? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.DataSource = Workbook.FullName ' полный путь к книге ADO.Query ("SELECT * FROM [Лист1$A:B]") End Sub
[/vba]
Как сделать запрос к "произвольным" данным (базе данных, текстовым файлам и т.п.). [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Connect ("Your connection string") ADO.Query ("SELECT * FROM ...") End Sub
[/vba] Т.е. в данном случае формирование строки подключения ложится целиком на плечи программиста.
Я сделал запрос. Где результат? Результат выполнения запроса зхранится в объекте Recordset. Достучаться до него можно так: [vba]
Код
ADO.Recordset
[/vba]
Как поместить результат выполнения запроса на лист? [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Range("D1").CopyFromRecordset ADO.Recordset ' поместить результат запроса на лист начиная с ячейки A1 End Sub
[/vba]
Как записать результат выполнения запроса в массив? например, используя стандартный метод getRows() объекта Recordset [vba]
Код
Sub Example() Dim ADO As New ADO Dim Arr As Variant
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Arr = ADO.Recordset.getRows() ' записать результат выполнения запроса в массив End Sub
[/vba] Но в этом случае массив будет иметь немного нестандартный вид. Еще один способ, кот. позволяет получить "стандартный" двумерный массив: [vba]
Код
Sub Example() Dim ADO As New ADO Dim Arr As Variant
ADO.Query ("SELECT * FROM [Лист1$A:B]")
Arr = ADO.ToArray() End Sub
[/vba]
Сахар Метод Query принимает ParamArray, что позволяет писать запросы наглядно и достаточно лаконично (без лишней конкатенации строк) [vba]
Код
Sub Example() Dim ADO As New ADO
ADO.Query "SELECT F1", _ "FROM [Sheet1$A:B]", _ "WHERE F2 > 0" End Sub
[/vba]
Файл с новой версией класса во вложении. К сожалению, форум не позволяет редактировать первый пост темы, поэтому обновления доки не будет (на форуме).nerv
Поскольку ты в последнне время увлёкся UTF-8, позволю себе выложить прямо сюда актуальный модуль класса в ANSI-кодировке. А то юзеры обычно не настолько продвинуты, чтобы ещё и декодированием заниматься...
Цитата (nerv)
актуальные версии и дока здесь
Поскольку ты в последнне время увлёкся UTF-8, позволю себе выложить прямо сюда актуальный модуль класса в ANSI-кодировке. А то юзеры обычно не настолько продвинуты, чтобы ещё и декодированием заниматься...AndreTM
Столкнулся с проблемой связанной с переносом файла на другой компьютер, не осуществляется запрос в макросе. Видимо несоответствие библиотек ? Помогите? В модуле ничего не менял, эксель 2007 на XP
Столкнулся с проблемой связанной с переносом файла на другой компьютер, не осуществляется запрос в макросе. Видимо несоответствие библиотек ? Помогите? В модуле ничего не менял, эксель 2007 на XPmaradona
Приветствую! А как ваш класс будет реагировать на колличество записейц больше 65536 строк. Аналогичная функция RDmitry просто не видит такое кол-во строк. Возможно ли обойти это ограничение?
Приветствую! А как ваш класс будет реагировать на колличество записейц больше 65536 строк. Аналогичная функция RDmitry просто не видит такое кол-во строк. Возможно ли обойти это ограничение?Watcher_1
Дата: Воскресенье, 03.11.2013, 20:39 |
Сообщение № 19
Группа: Гости
Уважаемый AndreTM Прочитав ваш ответ может сложиться впечатление что 2003 позволяет занести больше 65536 строк.... Задав этот вопарос я априори предполагаю офис выше 2003.
Уважаемый AndreTM Прочитав ваш ответ может сложиться впечатление что 2003 позволяет занести больше 65536 строк.... Задав этот вопарос я априори предполагаю офис выше 2003.Watcher_1
При просмотре кода класса вдруг увидел "4.0" в том месте, где обычно вижу "8.0". Подозреваю, что опечатка. Возможно, даже невольно ограничивающая функционал по тому же количеству строк - ведь в Excel 4.0 их было всего 16K. Или это сознательный шаг?
Private Function GetExcelConnectionString() As String Dim Params As String Params = IIf(Val(Application.Version) < 12, _ "Provider='Microsoft.Jet.OLEDB.4.0';Data Source=':1';Extended Properties='Excel 4.0;HDR=:2;IMEX=1';", _ "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=':1';Extended Properties='Excel 12.0;HDR=:2;IMEX=1';")
При просмотре кода класса вдруг увидел "4.0" в том месте, где обычно вижу "8.0". Подозреваю, что опечатка. Возможно, даже невольно ограничивающая функционал по тому же количеству строк - ведь в Excel 4.0 их было всего 16K. Или это сознательный шаг?
Private Function GetExcelConnectionString() As String Dim Params As String Params = IIf(Val(Application.Version) < 12, _ "Provider='Microsoft.Jet.OLEDB.4.0';Data Source=':1';Extended Properties='Excel 4.0;HDR=:2;IMEX=1';", _ "Provider='Microsoft.ACE.OLEDB.12.0';Data Source=':1';Extended Properties='Excel 12.0;HDR=:2;IMEX=1';")Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
