VBA ADODB-使用与数据库相同的工作簿的Excel表选择查询
问题描述:
我是VBA的新手,所以请不要介意问题是否为低级别。我试图运行SQL查询,其中数据必须从相同工作簿的其中一张中提取。VBA ADODB-使用与数据库相同的工作簿的Excel表选择查询
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
的功能的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
现在,经过我从数据表中的数字,我需要从关系表中找到相应的 ProductCompany。 9是阿穆尔,5是雀巢等。
关系:
我不知道该怎么做。这些数字对应于他们各自的产品公司。
答
查看下面的示例,显示如何创建与此工作簿的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
为我的输出如下所示:
4 - Britanica
5 - 雀巢
9 - Amul
请注意,上述代码中的连接字符串显示为.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;"";"
存储查询结果到数组,遍历数组,然后运行基于断阵列中的数据的JOIN语句。 –
嗨道格大衣:)我的查询没有得到结果。我不知道我的代码中有什么问题。你可以检查我的代码并告诉我什么是错的吗?Next将结果集存储在数组中并循环遍历逻辑,你能帮我们看看代码吗?我并不十分清楚这一点。我得到的逻辑,但在vba中做,因为在表单之间翻转是让我感到困惑。 – Naina
是sheetn名称Data还是sData? –