Выполнение 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 

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

Однако он не будет работать ни с динамическими именованными диапазонами, ни с 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") & "" 

Я новичок, переделывая чей-то код, поэтому, пожалуйста, будьте снисходительны и исправляйте мои ошибки. Я попробовал ваш код и играл с помощью 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 работает хорошо.

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

Привет, недавно рассмотрел этот вопрос и имел проблемы со ссылкой на названную таблицу (объект списка) в 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?

  • Microsoft Access Insert Into Statement Выбор данных из Excel - поле является смешанным Int и Text
  • Как внутреннее соединение в Excel (например, с помощью VLOOKUP)
  • Синтаксис SQL внутри кода VBA
  • Таблица Excel для SQL с VB.net
  • Как я могу выбрать набор строк и столбцов из листа excel в запросе для C #?
  • Обновление базы данных Access с использованием инструкции UPDATE SQL в VBA
  • Excel VBA / SQL Union
  • Запуск сохраненного Proc через соединение Excel - работает в SSMS, но не через макрос VBA
  • Преобразование Excel 'if' в SQL-запрос
  • Excel не распознает переменные в SQL-заявлении
  • Сотни значений excel lookup для MySQL вставляются в Интернете?
  • Interesting Posts
    Давайте будем гением компьютера.