Я пытаюсь сделать макрос для 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
Мой скрипт работает как шарм с жестко диапазонов, таких как в приведенном выше фрагменте. Он также очень хорошо работает с статических именованных диапазонов.
Тем не менее, он выиграл'т работать с динамических именованных диапазонов или имена таблиц, которые наиболее важны для меня.
Ближайший я нашел ответ этот парень страдает от того же недуга: 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
Который будет выплевывать в адресную строку, что-то вроде $А$1:$а$8
Редактировать:
Как я сказал в мой комментарий ниже, вы можете динамически получите полный адрес (включая имя листа) и использовать его непосредственно или разобрать имя листа для последующего использования:
ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal
Какие результаты в строке =Лист1!$С$1:$С$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") & ""
Дом на Хуан-Диего Родригес'ы рутину с Хорди'ы подход и некоторые Яцек Котовского's код - эта функция преобразует любое имя таблицы для активной книги в используемый адрес для запросов SQL.
Обратите внимание на Микель: добавление и запах мяты;[#все]&; включает рубрики избежать проблем, о которых ты писал.
Function getAddress(byVal sTableName as String) as String
With Range(sTableName & "[#All]")
getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
End With
End Function
Я новичок мастерить на кого-то другого's код поэтому, пожалуйста, будьте снисходительны и дальше исправлять мои ошибки. Я попробовал ваш код и играл с ВБА помочь следующее работали со мной:
Function currAddressTest(dataRangeTest As Range) As String
currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)
End Function
Когда я выберите аргумент исходных данных для моей функции, она превратилась в формате Лист1 А1$: Г3. Если Excel изменяет его Таблица1[#все] ссылка в моей формуле, функция все еще работает должным образом
Затем я использовал ее в своем функция (пытался играть и добавить еще один аргумент для инъекций, где...
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
Редактировать:
Чтобы сделать вещи более ясно: кто-то предложил использовать диапазон на объект лист. В этом случае вам не нужен; где таблица жизни может быть получена с помощью таблицы's имя; это имя имеется на протяжении всей книги. Так, один диапазон колла работает хорошо.
Привет недавно смотрели в этом и были проблемы, ссылающийся на имени таблицы (список объектов) в 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 является именованный диапазон