Выполнение SQL-запросов в таблице Excel в рабочей книге с помощью макроса VBA

Я пытаюсь сделать макрос excel, который даст мне следующую функцию в Excel:

=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'") 

Позволяя мне искать (и, возможно, даже вставлять) данные в таблицы моей книги, используя SQL-запросы.

Это то, что я сделал до сих пор:

 Sub SQL() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset strFile = ThisWorkbook.FullName strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$A1:G3]" rs.Open strSQL, cn Debug.Print rs.GetString End Sub 

Мой скрипт работает как шарм с жестко заданными диапазонами, такими как fragment выше. Он также отлично работает со статическими именованными диапазонами.

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

Самый близкий я нашел ответ, этот парень страдает от такого же несчастья: http://www.ozgrid.com/forum/showthread.php?t=72973

Помогите кому-нибудь?

редактировать

Я подготовил это до сих пор, затем я могу использовать результирующее имя в своих SQL-запросах. Ограничение заключается в том, что мне нужно знать, на каком листе эти таблицы. Можем ли мы что-то сделать?

 Function getAddress() myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "") myAddress = "[Sheet1$" & myAddress & "]" getAddress = myAddress End Function 

Благодаря!

Одна вещь, которую вы можете сделать, это получить адрес динамического именованного диапазона и использовать его в качестве ввода в вашей строке SQL. Что-то вроде:

 Sheets("shtName").range("namedRangeName").Address 

Который выплюнет адресную строку, что-то вроде $A$1:$A$8

Редактировать:

Как я уже сказал в своем комментарии ниже, вы можете динамически получить полный адрес (включая имя листа) и либо использовать его напрямую, либо проанализировать имя листа для последующего использования:

 ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal 

В результате получается строка типа =Sheet1!$C$1:$C$4 . Итак, для вашего примера кода выше, ваш оператор SQL может быть

 strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2) strSQL = "SELECT * FROM [strRangeAddress]" 
 Public Function GetRange(ByVal sListName As String) As String Dim oListObject As ListObject Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook For Each ws In wb.Sheets For Each oListObject In ws.ListObjects If oListObject.Name = sListName Then GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]" Exit Function End If Next oListObject Next ws End Function 

В вашем SQL-использовании это так

 sSQL = "Select * from " & GetRange("NameOfTable") & "" 

Основываясь на рутине Джоан-Диего Родригеса с подходом Хорди и некоторым кодом Яцека Котовского – эта функция преобразует любое имя таблицы для активной книги в полезный адрес для SQL-запросов.

Примечание к MikeL: добавление «[#All]» включает заголовки, избегающие проблем, о которых вы сообщали.

 Function getAddress(byVal sTableName as String) as String With Range(sTableName & "[#All]") getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]" End With End Function 

Я новичок, переделывая чей-то код, поэтому, пожалуйста, будьте снисходительны и исправляйте мои ошибки. Я пробовал ваш код и играл с помощью VBA. Следующие работали со мной:

 Function currAddressTest(dataRangeTest As Range) As String currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False) End Function 

Когда я выбираю аргумент источника данных для своей функции, он преобразуется в формат Sheet1 $ A1: G3. Если excel изменяет его на таблицу1 [#All] в моей формуле, функция по-прежнему работает правильно

Затем я использовал его в вашей функции (попытался воспроизвести и добавить еще один аргумент, который будет введен в WHERE …

 Function SQL(dataRange As Range, CritA As String) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim currAddress As String currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False) strFile = ThisWorkbook.FullName strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [" & currAddress & "]" & _ "WHERE [A] = '" & CritA & "' " & _ "ORDER BY 1 ASC" rs.Open strSQL, cn SQL = rs.GetString End Function 

Надеюсь, что ваша работа развивается дальше, я считаю ее очень полезной. Хорошего дня!

Просто отвечая на вторую часть вашего вопроса о получении имени листа, где находится таблица:

 Dim name as String name = Range("Table1").Worksheet.Name 

Редактировать:

Чтобы сделать более понятным: кто-то предложил использовать Range на объекте Sheet. В этом случае вам не нужно; Диапазон, в котором находится таблица, может быть получен с использованием имени таблицы; это имя доступно во всей книге. Таким образом, Call Range работает хорошо.

Привет, недавно рассмотрел этот вопрос и имел проблемы со ссылкой на названную таблицу (объект списка) в excel

если вы поместите суффикс ‘$’ в имя таблицы, все будет хорошо в мире

 Sub testSQL() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset ' Declare variables strFile = ThisWorkbook.FullName ' construct connection string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";" ' create connection and recordset objects Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") ' open connection cn.Open strCon ' construct SQL query strSQL = "SELECT * FROM [TableName$] where [ColumnHeader] = 'wibble';" ' execute SQL query rs.Open strSQL, cn Debug.Print rs.GetString ' close connection rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub 

нашел это, и это сработало для меня.

 strSQL = "SELECT * FROM DataTable" 

‘Где DataTable – это Именованный диапазон

Как я могу запускать SQL-запросы в именованном диапазоне в листе excel?

  • Как я могу конкатенировать строки в VBA?
  • Как удалить все не буквенно-цифровые символы из строки, кроме периода и пробела в excel?
  • Как программно закодировать функцию «отменить» в Excel-Vba?
  • Значение Autonumber последней вставленной строки - MS Access / VBA
  • Какова продолжительность жизни глобальной переменной в excel vba?
  • Каков наиболее эффективный / быстрый способ циклического преобразования строк в VBA (excel)?
  • Использование VBA для получения расширенных атрибутов файла
  • Как настроить «зубчатый массив» в VBA?
  • Извлечение координат маркера из встроенной карты google
  • Должен ли я использовать ключевое слово Call в VB / VBA?
  • VBA, удалите дубликаты из массива
  • Interesting Posts

    Oracle Искать все таблицы для всех столбцов для строки

    Disambiguate перегруженный указатель функции участника передается как параметр шаблона

    В чем смысл метки «#» на быстром языке?

    Как подключить VPN автоматически после спящего режима / спящего режима в Vista / 7?

    Java SimpleDateFormat для часового пояса с разделителем двоеточия?

    Что такое интерфейс в Java?

    Получение моего публичного IP через API

    Изменение описания типов файлов, связанных с Notepad ++

    Как я могу попросить Selenium-WebDriver ждать несколько секунд в Java?

    Каково ключевое слово ‘function’, используемое в некоторых сценариях bash?

    Монгоский порядок по длине массива

    Установщики приложений Java

    Как найти дублирующий элемент в массиве перетасованных последовательных целых чисел?

    «Наблюдаемое поведение» и свобода компилятора для исключения / преобразования элементов кода c ++

    постоянные переменные, не работающие в заголовке

    Давайте будем гением компьютера.