您可以将ADO与Excel一起使用。
Dim 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 = "Provider=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
