本文介绍了Excel vba - ADO内连接数据表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有两个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内连接数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-22 21:06
查看更多