如何在Excel表单中的命名范围上运行SQL语句？ [英] How can I run SQL statements on a named range within an excel sheet?

问题描述

我所要做的就是在excel表（即一个命名范围，甚至是A1：F100）上采用一个标准的范围，并在其上运行一些sql查询，并返回一个可以通过的记录集VBA代码，甚至只是粘贴到同一个工作簿中的其他工作表。

使用ADODB是一个想法，但是如何设置connectionstring来指向一些范围内目前的工作簿？

在我使用Microsoft查询向导之前，我知道这个向导是不理想的，但是会工作。我似乎无法将其引用到表格中，只有其他excel文件。

---

这里是我留下的功能。当我运行它几次，我的excel崩溃与普通的资源错误信息。我已经从我的电子表格中删除了这个功能，一切都无缝地遍历了多次，所以这绝对是由这里的代码造成的。

我已经清理了所有的对象？）。有人有什么想法可能会出错吗？可以在连接字符串中有可能被调整的东西，还是可以与GetRows方法返回的变体有关？

我正在使用MS ADO 2.8，并且也尝试使用2.5相同的行为。

 函数getTimeBuckets（）作为集合
 
 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）
设置rs = CreateObject（ADODB。 Recordset）
设置getTimeBuckets =新集合
 
 strFile = ThisWorkbook.FullName 
 strCon =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& strFile _ 
& ;扩展属性=Excel 8.0; HDR =是; IMEX = 1; 
 cn.Open strCon 
 
 strSQL =SELECT DISTINCT（Expiration）FROM [PositionSummaryTable]其中[Instrument Type] ='LSTOPT'
 
 rs.Open strSQL ，cn 
 
 
 dateRows = rs.GetRows 
 rs.Close 
 
'today = Date 
 today =6-may-2009 
 
对于i = 1 To UBound（dateRows，2）
 If（dateRows（0，i）> = today）然后
 getTimeBuckets.Add（dateRows i））
 End If 
 Next i 
 
设置dateRows = Nothing 
设置cn = Nothing 
设置rs = Nothing 
结束函数
  

解决方案

你可以使用这个名字。 b
$ b

  Dim cn As ADODB.Connection 
 Dim rs As ADODB.Recordset 
 
 strFile = Workbooks（1）。 FullName 
 strCon =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =& strFile _ 
& ;扩展属性=Excel 8.0; HDR =是; IMEX = 1; 
 
设置cn = CreateObject（ADODB.Connection）
设置rs = CreateObject（ADODB.Recordset）
 
 cn.Open strCon 
 
''选择一个：
 strSQL =SELECT * FROM DataTable''命名范围
 strSQL =SELECT * FROM [Sheet1 $ A1：E346]''范围
 
 rs.Open strSQL，cn 
 
 Debug.Print rs.GetString 
  

对第2部分的回应

我注意到你只想要今天的记录，所以你应该可以修改sql ：

  strSQL =SELECT DISTINCT（Expiration）FROM [PositionSummaryTable]_ 
& 其中[Instrument Type] ='LSTOPT'AND [Expiration] =＃_ 
&格式（Date（），yyyy / mm / dd）& ＃
  

您尚未关闭连接：

  cn.Close 
  

然后

 设置rs =没有
设置cn =没有
  

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.

Using ADODB was one thought, but how could I setup the connectionstring to point to some range within the current workbook?

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.

---

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.

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?

I am using MS ADO 2.8, and have also tried 2.5 with the same behaviour.

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

解决方案

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

I notice that you only want today's records, so you should be able to modify the sql to:

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

这篇关于如何在Excel表单中的命名范围上运行SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！