debug.print getsheetsql(workbookpath,“select columna from[sheet$]”)(1)(“columna”)
< /代码>
-->返回“palim”
如果a是“日期”列,则不起作用
< /代码>
-->不返回任何内容
函数getsheetsql(path as string,sqlstr as string)as collection
管,管,管
'打开ADOB连接并通过SQL查询
'连接字符串是标准的,并且
取自:
'https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-excel/
Dim对象连接为对象
Dim对象记录集为对象
set objconnection=createObject(“adodb.connection”)。
set objrecordset=createObject(“adodb.recordset”)。
常量adopenstatic=3
常量adlockoptimatic=3
const adCmdText=&h1
objConnection.open“provider=microsoft.jet.oledb.4.0;data source=”&path&“;extended properties='excel 8.0;hdr=yes”“”
objrecordset.open sqlstr,objconnection,adopenstatic,adlockoptimatic,adcmdtext
管,管,管
'遍历查询的表
'表是一个集合,表中每行有一行
'行是以表标题为键的字典,返回相应的值
将表变暗为集合
set table=新集合
将行变暗为对象
模糊FLD
'循环访问记录集行
直到objrecordset.eof
带对象记录集
设置row=createObject(“scripting.dictionary”)。
对于每个fld in.字段
行.添加fld.名称,fld.值
下一个FLD
添加行
MOVENEXT
以结束
回路
设置getSheetSQL=表
'关闭连接;将错误处理重置为默认值
对象连接。关闭
关于错误GOTO 0
退出函数
ErrorCloseConn:
对象连接。关闭
关于错误GOTO 0
简历
端函数
< /代码> <工作表看起来像
如果是作品不是日期列:
Debug.Print GetSheetSQL(workbookPath, "Select columna from [sheet$]")(1)("columna")
-->返回“palim”
如果a是a,则不工作日期栏
Debug.Print GetSheetSQL(workbookPath, "Select columnb from [sheet$]")(1)("columnb")
-->不返回任何内容
Function GetSheetSQL(path As String, sqlStr As String) As Collection
'''''''''''''''''''''''''''''''''''''''
'Open ADOB Connection and query via sql
' Connection string is standard and
' taken from:
' https://www.connectionstrings.com/microsoft-jet-ole-db-4-0/standard-excel/
Dim objConnection As Object
Dim objRecordSet As Object
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""Excel 8.0;HDR=Yes"""
objRecordSet.Open sqlStr, objConnection, adOpenStatic, adLockOptimistic, adCmdText
'''''''''''''''''''''''''''''''''''''''
'Iterate through queried table
' table is a collection with a row per row in the table
' row is a dictionary with table headings as key, returning the corresponding value
Dim table As Collection
Set table = New Collection
Dim row As Object
Dim fld
'iterate through recordset rows
Do Until objRecordSet.EOF
With objRecordSet
Set row = CreateObject("Scripting.Dictionary")
For Each fld In .Fields
row.Add fld.Name, fld.Value
Next fld
table.Add row
.MoveNext
End With
Loop
Set GetSheetSQL = table
'Close Connection; reset Error Handling to default
objConnection.Close
On Error GoTo 0
Exit Function
ErrorCloseConn:
objConnection.Close
On Error GoTo 0
Resume
End Function
