Выполняя запросы 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

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

Тем не менее, он выиграл'т работать с динамических именованных диапазонов или имена таблиц, которые наиболее важны для меня.

Ближайший я нашел ответ этот парень страдает от того же недуга: 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

Спасибо!

Joan-Diego Rodriguez
Редактировал вопрос 26-го февраля 2017 в 6:48
Программирование
vba sql excel excel-vba
3-го ноября 2013 в 4:53
47 просмотров
Комментарии к вопросу (2)
Решение

Одна вещь, вы можете быть в состоянии сделать, это получить адрес динамический именованный диапазон, и использовать его в качестве входа в 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]"
Jake Bathman
Редактировал ответ 4-го ноября 2013 в 3:26
Комментарии (3)
 
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") & ""
Комментарии (2)

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

Обратите внимание на Микель: добавление и запах мяты;[#все]&; включает рубрики избежать проблем, о которых ты писал.

Function getAddress(byVal sTableName as String) as String 

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

End Function
Комментарии (1)

Я новичок мастерить на кого-то другого'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

Надеюсь, что ваша функция развивается дальше, я нахожу его очень полезным. Иметь хороший день!

Комментарии (0)

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

Dim name as String

name = Range("Table1").Worksheet.Name

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

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

Jordi
Редактировал ответ 22-го ноября 2016 в 8:25
Комментарии (0)

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

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

strSQL = "SELECT * FROM DataTable"

'где DataTable является именованный диапазон

https://stackoverflow.com/questions/914980/how-can-i-run-sql-statements-on-a-named-range-within-an-excel-sheet

Community
Редактировал ответ 23-го мая 2017 в 10:31
Комментарии (0)