我试图连接两个不同工作表中的两个单独的列来制作更长的列，然后可以使用它们Vlookup。( L9 Z3 a5 X$ l* n) y
工作表1 A，B，C，D，E，F，G9 E& D# q2 c! W% y, z9 M- c7 P; w
工作表2 A，B，C，D，E，F，G % n; r l' `! G3 T1 Q7 `- `' X- l我想把工作表1中的B列和工作表2C连接列(联合)并找到新列表Distinct值。我已经为此工作了好几个星期。2 G& ~0 u' F- o& @4 Y
谢谢 0 W8 d6 S4 \: }% x7 K# I' c' _ ) w2 g0 z4 C" S2 Q$ Q( Z 解决方案: 2 I1 z! g$ y; D- U3 I, k9 S
您可以将ADO与Excel一起使用。 ' @/ U7 v/ ~- B9 q5 S2 jDim cn As ObjectDim rs As ObjectDim strFile As StringDim strCon As StringDim strSQL As StringDim s As StringDim 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/excelstrCon = "rovider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"''Late binding,so no reference is neededSet cn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")cn.Open strCon''A sample querystrSQL = "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.asprs.Open strSQL,cn,3,3''Write out the data to an empty sheet (no headers)Worksheets("Sheet3").Cells(2,1).CopyFromRecordset rss
技术问答
161 人阅读
|
0 人回复
|
2023-09-12
rovider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"''Late binding,so no reference is neededSet cn = CreateObject("ADODB.Connection")Set rs = CreateObject("ADODB.Recordset")cn.Open strCon''A sample querystrSQL = "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.asprs.Open strSQL,cn,3,3''Write out the data to an empty sheet (no headers)Worksheets("Sheet3").Cells(2,1).CopyFromRecordset rss