我正在尝试将 2 个不同工作表中的 2 个单独的列连接起来,以创建一个更长的列,然后我可以从中使用 Vlookup。
表1
A、B、C、D、E、F、G
表2
A、B、C、D、E、F、G
我想将sheet1中的B列和sheet2中的C列连接(联合)在一起,并找到新列表的不同值。我已经为此工作了几个星期。
Thanks
您可以将 ADO 与 Excel 结合使用。
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
''A sample query
strSQL = "SELECT Distinct A, B C FROM ( " _
& "SELECT A, B, C " _
& "FROM [Sheet1$] " _
& "UNION ALL " _
& "SELECT A, B, C " _
& "FROM [Sheet2$] ) As J "
''Open the recordset for more processing
''Cursor Type: 3, adOpenStatic
''Lock Type: 3, adLockOptimistic
''Not everything can be done with every cirsor type and
''lock type. See http://www.w3schools.com/ado/met_rs_open.asp
rs.Open strSQL, cn, 3, 3
''Write out the data to an empty sheet (no headers)
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rss
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)