VBA ADODB-使用与数据库相同的工作簿的 Excel 表选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40817911/
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
VBA ADODB- Select query using the excel sheet of the same workbook as Database
提问by Naina
I am novice in VBA so please don't mind if the question is of low level.I am trying to run a SQL query where the data has to be extracted from one of the sheets of the same workbook.
我是 VBA 新手，所以请不要介意问题是否低级。我正在尝试运行 SQL 查询，其中必须从同一工作簿的一张工作表中提取数据。
SQL = "Select ProductNumber from [sData$] where ProductSource = " & pSource & "
'pSource is a string that stores Product Source
'sdata is a sheet named as Data in the workbook
dataPath = ThisWorkbook.Fullname
'Not sure if this is the value I shall send as datapath in getData function
Set rst = getData(dataPath,SQL)
rst.Open
The getData function is defines as below
getData 函数定义如下
Public funtion getData(path as String, SQL as string) as ADODB.Recordset
Dim rs as ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ("Provider= Microsoft.Jet.OLEDB.4.0;" & _
"DataSource= " & path & ";"&_
"Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;""")
rs.ActiveConnection =cn
rs.Source= SQL
Set getData =rs
End Function
Now after I get the numbers from Data sheet, I need to find the corresponding ProductCompany from Relation sheet. 9 is for Amul, 5 is for Nestle and so on.
现在，从数据表中获取数字后，我需要从关系表中找到相应的 ProductCompany。9 代表 Amul，5 代表雀巢，依此类推。
Relation:
关系：
I am not sure how to do that. The numbers corresponds to their respective Product company in order.
我不知道该怎么做。数字按顺序对应于各自的产品公司。
回答by omegastripes
Take a look at the below example showing how to create ADODB connection to this workbook, get ADODB recordset from SQL query, retrieve key - value pairs from relation sheet, create and populate a dictionary, and output the values from the recordset and the corresponding values from the dictionary:
看一下下面的示例，显示如何创建与此工作簿的 ADODB 连接、从 SQL 查询获取 ADODB 记录集、从关系表中检索键值对、创建和填充字典，以及从记录集中输出值和相应的值从字典：
Option Explicit
Sub Test()
Dim oCn As Object
Dim oRs As Object
Dim aKeys
Dim aItems
Dim i As Long
Dim oDict As Object
Dim dProdNum
' create ADODB connection to this workbook
Set oCn = CreateObject("ADODB.Connection")
oCn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"DataSource='" & ThisWorkbook.FullName & "';" & _
"Extended Properties=""Excel 8.0;HDR=Yes;FMT=Delimited;IMEX=1;"";"
' get ADODB recordset from SQL query
Set oRs = oCn.Execute("SELECT DISTINCT ProductNumber FROM [Data$] WHERE ProductSource = 'A1'")
' retrieve key - value pairs from relation sheet
With ThisWorkbook.Sheets("Relation")
aKeys = Split(.Range("B1"), ",")
aItems = Split(.Range("B2"), ",")
End With
' create and populate a dictionary
Set oDict = CreateObject("Scripting.Dictionary")
For i = 0 To UBound(aKeys)
oDict(Trim(aKeys(i)) + 0) = Trim(aItems(i))
Next
' output the values from the recordset and the corresponding values from the dictionary
oRs.MoveFirst
Do Until oRs.EOF
dProdNum = oRs.Fields(0).Value
Debug.Print dProdNum & " - " & oDict(dProdNum)
oRs.MoveNext
Loop
End Sub
The output for me is as follows:
我的输出如下：
4 - Britanica
5 - Nestle
9 - Amul
4 - Britanica
5 - 雀巢
9 - Amul
Note, connection string in the above code shown for .xlsfile. In case .xlsmyou should use:
注意，上面代码中的连接字符串是为.xls文件显示的。如果.xlsm您应该使用：
oCn.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source='" & ThisWorkbook.FullName & "';" & _
"Extended Properties=""Excel 12.0 Macro;HDR=Yes;FMT=Delimited;IMEX=1;"";"
