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

Все, что я пытаюсь сделать, это взять стандартный диапазон на листе excel (например, именованный диапазон или даже A1: F100) и запустить некоторые SQL-запросы на нем и вернуть набор записей, который я могу либо выполнить в VBA, или даже просто вставить другой лист в ту же книгу.

Использование ADODB было одной мыслью, но как мне настроить строку соединения, чтобы указать на какой-то диапазон в текущей книге?

Я знаю, прежде чем использовать мастер запросов Microsoft, который не был идеальным, но будет работать. Я не могу заставить это ссылаться на диапазон внутри листа, только другие файлы excel.

Вот функция, с которой я остаюсь. Когда я запускаю его несколько раз, мой excel падает с обычным сообщением об ошибке с ресурсами. Я удалил эту функцию из своей таблицы, и все работает многократно, поэтому она определенно вызвана кодом здесь.

Я очистил все объекты (правильно?). У кого-нибудь есть идеи, что может пойти не так? Может ли быть что-то в строке соединения, которая может быть изменена, или это может быть связано с вариантом, который возвращается из метода GetRows?

Я использую MS ADO 2.8, а также попробовал 2.5 с тем же поведением.

Function getTimeBuckets() As Collection

Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim dateRows As Variant
Dim i As Integer
Dim today As Date

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set getTimeBuckets = New Collection

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
cn.Open strCon

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] where [Instrument Type] = 'LSTOPT'" 

rs.Open strSQL, cn


dateRows = rs.GetRows
rs.Close

'today = Date
today = "6-may-2009"

For i = 1 To UBound(dateRows, 2)
    If (dateRows(0, i) >= today) Then
        getTimeBuckets.Add (dateRows(0, i))
    End If
Next i

Set dateRows = Nothing
Set cn = Nothing
Set rs = Nothing
End Function

Ответы

Ответ 1

Вы можете просто использовать имя.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset 

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

''Pick one:
strSQL = "SELECT * FROM DataTable" ''Named range
strSQL = "SELECT * FROM [Sheet1$A1:E346]" ''Range

rs.Open strSQL, cn

Debug.Print rs.GetString

В ответ на вопрос часть 2

Я замечаю, что вам нужны только сегодняшние записи, поэтому вы должны иметь возможность модифицировать sql для:

strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _
& "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _
& Format(Date(),"yyyy/mm/dd") & "#"

Вы не закрыли соединение:

cn.Close

И затем

 Set rs=Nothing
 Set cn=Nothing

Ответ 2

Как насчет использования предложения LIKE?

Я попытался использовать:

select * from [PES$] where PID_TAG like '*5400001'

без успеха....

это работает:

select * from [PES$] where PID_TAG = 'PIT5400001'

но этого я не хочу.

ИЗМЕНИТЬ

hummm.... нам нужно изменить подстановочные знаки для работы... не используйте *, используйте%

Ответ 3

Вы найдете все, что вам нужно ниже:

Первая ссылка для VB.NET, ссылки 2 - 6 для VBA.

Один

Два

Три

Четыре

Five

Шесть

Найдено всего в google, около 2 - 4 минут.

Ответ 4

Я не знаю о VBA, но есть код онлайн в Delphi и С#, который использует формат

SELECT * FROM [SheetName$A1:B2]

Вы пробовали?

Ответ 5

Если вы используете powershell, $- внутренний символ. используйте `$

например:

"Select * from [VM`$A3:F30]"