Функция VBA в запросе Excel ADODB
Я открываю соединение ADODB в Excel 2007 для запроса одного из листов текущей книги. При попытке добавить пользовательскую функцию VBA возникает ошибка "Неопределенное имя функции". Связь:
Dim connection As String
Dim records As ADODB.Recordset
Dim query As String
Dim fileName As String
fileName = ThisWorkbook.FullName
connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
query = "select t.[Col1] from [Sheet1$] As t"
Set records = New ADODB.Recordset
records.Open query, connection
Sheets(2).Range("A1").CopyFromRecordset records
Чего я хотел бы добиться, так это иметь еще один столбец в списке выбора, например
query = "select t.[Col1], myFunc() from [Sheet1$] As t"
где myFunc - это функция, определенная в книге.
Я знаю, что что-то подобное возможно в Access (чтобы в запросе были пользовательские функции VBA). Возможно ли это и в Excel?
Какова лучшая практика или обходной путь для этого сценария?
2 ответа
Я думаю, что вам нужны некоторые основные объяснения здесь, и, возможно, ответ на этот вопрос:
Откуда берутся функции в SQL?
Если вы отправляете запросы на любой сервер базы данных, который поддерживает стандарт ANSI SQL, ядро базы данных будет анализировать и запускать встроенные функции, которые являются родными для SQL: LEFT(), SUBSTRING(), SIN(), SQR(), и т. д. Здесь есть короткий список:
http://www.smallsql.de/doc/sql-functions/index.html
Серверы Oracle будут предоставлять дополнительные функции, расширяющие ANSI SQL, как и серверы Microsoft SQL; и PL-SQL, и T-SQL имеют функции, которые недоступны в стандартном SQL. Они оба позволяют владельцу БД создавать свои собственные функции, которые находятся на сервере и также доступны для запросов SQL.
Microsoft Jet SQL, который не совсем совпадает с ANSI SQL, имеет довольно ограниченный набор встроенных функций; но никто не возражает, когда они запускают Jet SQL в среде MS-Access, потому что почти все функции Visual Basic для приложений доступны для механизма SQL с помощью MS-Access.
Кроме того, все глобально объявленные функции VBA, которые они написали и сделали видимыми в локальном проекте MS-Access, также доступны для механизма SQL.
Пока вы выполняете запрос из Microsoft Access.
Это легкая часть...
Когда вы выходите за пределы среды MS-Access, вы не видите VBA.
Вы можете запрашивать данные с помощью Jet SQL (и провайдер Microsoft.ACE.OLEDB.12.0 делает именно это), но, если вы запускаете их из Excel, вам не понравится способность ядра базы данных MS-Access используйте VBA: у вас есть собственный список функций Jet SQL и больше ничего
Функции перечислены здесь, и очень немного других мест:
MS Access: функции - перечислены по категориям
Этот список теперь включает IIF() - встроенная функция 'IF' - это все, что у вас есть в Jet SQL, если вы хотите использовать оператор CASE в предложении SELECT; вы найдете это полезным, если ваш первый урок в родном Jet-SQL состоит в том, что все функции VBA NZ() в вашем запросе перестали работать.
Многие из этих функций похожи на знакомые функции VBA: и это является источником путаницы, потому что вы не используете VBA, вы используете SQL.
Мало кто понимает, что этот список встроенных функций Jet не совпадает со списком встроенных функций VBA, предоставленных SQL для MS-Access, и Microsoft не делает этого явным в своей документации.
Это полный PITA, потому что каждый, кто запрашивает SQL-сервер или БД Oracle, ожидает, что сервер выполнит все и любые функции в своем SQL-запросе, которые объявлены, импортированы или являются "родными" для диалекта SQL, работающего на этом сервере. Вы объявили функции VBA в Access mdb, и вы ожидали, что они будут видны и для SQL!
Как не исправить это:
Я видел сервер Sybase, где блестящий, но ошибочный владелец базы данных импортировал функции из внешней библиотеки, которую вы определенно использовали, не осознавая, что она есть в каждой базе данных MS-Access: vbaen32.dll, перечисление функций VBA dll. Это потребовало довольно много работы и никогда не работало: пожалуйста, не пытайтесь повторить этот гениальный подвиг.
Таким образом, короткий ответ "Нет".
Теперь для полезного ответа:
Recordset.GetRows () вернет ваш набор записей в виде двумерного массива VBA, и вы сможете запускать на нем свои функции VBA после того, как механизм SQL выполнит тяжелую работу по сортировке, фильтрации и агрегированию.
Вы можете сделать это эффективно на большом наборе данных, не занимая слишком много места в памяти, если вы будете последовательно запускать vba частями на курсоре Forward-Only, вызывая Recordset.GetRows(Rows:=1024), пока не достигнете конца данных,
Хотя вы, возможно, захотите спросить: "Зачем я это делаю?", Так как очень сложно придумать процесс, в котором более качественный анализ и дизайн не позволят найти лучшее решение. Я должен был реализовать этот хак для команды, чей Excel-зависимый процесс выполнялся на csv-файлах, которые росли и росли... И выросли до размеров в терабайты, которые могут быть прочитаны только драйвером базы данных. И они работают в среде, где получение подходящего сервера базы данных занимает 2-3 года устойчивых управленческих усилий.
Если вы счастливый сын, который унаследовал этот конкретный процесс после того, как я ушел, я рекомендую попробовать свое "решение" GetRows после взрыва сайта с орбиты.
Сноска. Расширьте этот ответ, если найдете лучший онлайн-список функций Jet SQL.
Между тем, я бы призвал любого другого участника Stack, который читает это, добавить свои собственные ссылки в хороший список встроенных функций Jet SQL - если вы можете найти такую. Большинство из них неверны, и ни одно из них не является исчерпывающим, и очень немногие прямо заявляют, что есть разница между встроенными функциями и импортированными VBA. Насколько я знаю, Jet Engine импортирует и запускает ограниченный набор функций из VBAEN32.dll, но Jet под ADODB определенно не является полнофункциональным хост-приложением VBA, и это ограничение необходимо четко понимать, когда вы используя его вне MS-Access.
Я вижу только 1 вариант здесь:
Поскольку функция "myFunc()" не имеет каких-либо параметров, вы можете ввести функцию на отдельном рабочем листе (например, в Sheet2 в A2, в A1 поместите заголовок как "A") и сослаться на ячейку в запросе SQL, например:
select t.[Col1], myFunc.A from [Sheet1$] As t, [Sheet2$] as myFunc