如何在 Excel 工作表中的命名范围上运行 SQL 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/914980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I run SQL statements on a named range within an excel sheet?
提问by cOrOllArY
All I am trying to do is take a standard range on an excel sheet (i.e. a named range, or even A1:F100), and run some sql queries on it, and return a recordset that I can either step through in VBA code, or even just paste into some other sheet in the same workbook.
我想要做的就是在 Excel 工作表上取一个标准范围（即命名范围，甚至 A1:F100），并在其上运行一些 sql 查询，并返回一个我可以在 VBA 代码中逐步执行的记录集，或者甚至只是粘贴到同一工作簿中的其他工作表中。
Using ADODB was one thought, but how could I setup the connectionstring to point to some range within the current workbook?
使用 ADODB 是一种想法，但我如何设置连接字符串以指向当前工作簿中的某个范围？
I know before I have made use of the Microsoft query wizard, which was not ideal, but would work. I can't seem to get this to refer to a range within the sheet, only other excel files.
我在使用 Microsoft 查询向导之前就知道，这并不理想，但可以工作。我似乎无法将其用于指代工作表中的某个范围，只能指代其他 excel 文件。
Here is the function I am left with. When I run it a few times my excel crashes with the usual out of resources error message. I have removed this function from my spreadsheet, and everything runs seamlessly multiple times, thus it is definitely caused by the code here.
这是我剩下的功能。当我运行它几次时，我的 excel 崩溃，并显示通常的资源不足错误消息。我已经从我的电子表格中删除了这个功能，并且一切都无缝运行了多次，因此它肯定是由这里的代码引起的。
I have cleaned up all the objects (correctly?). Does anyone have any ideas what could be going wrong? Could there be something in the connection string that could be tweaked, or could it be something to do with the variant that is returned from the GetRows method?
我已经清理了所有对象（正确吗？）。有没有人有任何想法可能会出错？连接字符串中是否有可以调整的内容，或者是否与从 GetRows 方法返回的变体有关？
I am using MS ADO 2.8, and have also tried 2.5 with the same behaviour.
我正在使用 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
回答by Fionnuala
You can just use the name.
您可以只使用该名称。
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
In response to question part 2
回答问题第 2 部分
I notice that you only want today's records, so you should be able to modify the sql to:
我注意到你只想要今天的记录，所以你应该能够将 sql 修改为：
strSQL = "SELECT DISTINCT(Expiration) FROM [PositionSummaryTable] " _
& "where [Instrument Type] = 'LSTOPT' AND [Expiration]=#" _
& Format(Date(),"yyyy/mm/dd") & "#"
You have not closed the connection:
您尚未关闭连接：
cn.Close
And then
进而
Set rs=Nothing
Set cn=Nothing
回答by Eduardo
How about using of LIKE clause?
LIKE 子句的使用怎么样？
I tried to use:
我尝试使用：
select * from [PES$] where PID_TAG like '*5400001'
without success....
没有成功....
this works:
这有效：
select * from [PES$] where PID_TAG = 'PIT5400001'
but this is not I want.
但这不是我想要的。
EDIT
编辑
hummm.... we need to change wildcards to work... don't use *, use %
嗯……我们需要更改通配符才能工作……不要使用 *，使用 %
回答by tommylux
If you are using powershell, $ is an internal character. use `$
如果您使用的是 powershell，则 $ 是一个内部字符。使用`$
eg:
例如：
"Select * from [VM`$A3:F30]"
回答by Eugene Yokota
I don't know about VBA, but there are code online in Delphi and C# that uses the format
我不知道 VBA，但在 Delphi 和 C# 中有使用该格式的在线代码
SELECT * FROM [SheetName$A1:B2]
Have you tried it?
你试过吗？
