admin

如何在Excel工作表中的命名范围上运行SQL语句?

sql

我要做的就是在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

阅读 154

收藏
2021-05-10

共1个答案

admin

您可以只使用名称。

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
2021-05-10