问题描述
我有两个excel中的数据表,我希望在vba代码中加入一个单一的集合。我确定ADO连接器是最好的方法,但是使用下面的查询,我收到以下错误运行时错误-2147217904
没有给出一个或多个必需参数的值
SELECT组件[name ],InputData.Datatype
FROM [Rules $ A5:F30] components
INNER JOIN [Rules $ O5:R17] InputData ON组件[name] = InputData。[name]
WHERE组件.RowId = 0 GROUP BY组件[name],InputData.Datatype
编辑: / strong>完整的代码:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dataRows As Integer
strFile = ThisWorkbook.FullName
strCon =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =& strFile _
& ;扩展属性=Excel 12.0; HDR =是; IMEX = 1;
设置cn = CreateObject(ADODB.Connection)
设置rs = CreateObject(ADODB.Recordset)
cn.Open strCon
strsql =SELECT components。[name],InputData.Datatype_
+FROM [+ GetTableAddress(componentTable)_
+]组件INNER JOIN [+ GetTableAddress (DataLocations)+] InputData_
+ON组件[name] = InputData。[name]_
+WHERE components.RowId =+ CStr(RowId)+ GROUP BY组件[name],InputData.Datatype
rs.Open strsql,cn
如果不是rs.EOF然后
dataRows = rs.GetRows
和GetTableAddress函数
私有函数GetTableAddress(tableName)
Dim oSh As Worksheet
Dim oLo As ListObject
对于每个oSh在ThisWorkbook.Worksheets
对于每个oLo在oSh.ListObjects
如果oLo.Name = tableName然后
GetTableAddress = Replace(oSh.ListObje cts(tableName).Range.AddressLocal,$,)
GetTableAddress = oSh.Name +$+ GetTableAddress
End If
Next
Next
结束功能
如果两个数据集都在Excel中,则应使用 vLookup
创建最终表。这将会更容易,而且您可以使用您已经熟悉的语法来获得好处。
vLookup
本质上是一个表连接。你甚至可以使用它与 Application.WorksheetFunctions
如果你想这样做。
另外,code> RecordSet.GetRows 可以返回一个数组。如果您不希望返回多个值,您应该可以使用 CInt(rs.GetString)
。
I have two data tables in excel that I wish to join into a single set in my vba code. I have identified the ADO connector as the best way to do this, however using the query below, I get the following error
"Run time error -2147217904
No value given for one or more required parameters"
SELECT components.[name], InputData.Datatype
FROM [Rules$A5:F30] components
INNER JOIN [Rules$O5:R17] InputData ON components.[name] = InputData.[name]
WHERE components.RowId = 0 GROUP BY components.[name], InputData.Datatype
EDIT: The full code:
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dataRows As Integer
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strsql = "SELECT components.[name], InputData.Datatype " _
+ " FROM [" + GetTableAddress("componentTable") _
+ "] components INNER JOIN [" + GetTableAddress("DataLocations") + "] InputData" _
+ " ON components.[name] = InputData.[name] " _
+ " WHERE components.RowId = " + CStr(RowId) + " GROUP BY components.[name], InputData.Datatype"
rs.Open strsql, cn
If Not rs.EOF Then
dataRows = rs.GetRows
and the GetTableAddress function
Private Function GetTableAddress(tableName)
Dim oSh As Worksheet
Dim oLo As ListObject
For Each oSh In ThisWorkbook.Worksheets
For Each oLo In oSh.ListObjects
If oLo.Name = tableName Then
GetTableAddress = Replace(oSh.ListObjects(tableName).Range.AddressLocal, "$", "")
GetTableAddress = oSh.Name + "$" + GetTableAddress
End If
Next
Next
End Function
If both data sets are in Excel, you should use vLookup
to create the final table. It'll be easier for you and the benefit is that you can use syntax that you're already familiar with.
vLookup
is essentially a table join. You can even use it with Application.WorksheetFunctions
if you wish to do it that way.
Also, RecordSet.GetRows
can return an array. You should probably use CInt(rs.GetString)
if you're not expecting more than one value to be returned.
这篇关于Excel vba - ADO内连接数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!