随笔1:
Excel 中 使用SQL 读取Excel内的数据
代码如下:
Sub test() Dim CNN As Object, SQL$, f$, m% Dim rst As Object Sheets("Sheet2").Cells.Clear Set CNN = CreateObject("adodb.connection") Set rst = CreateObject("adodb.recordset") sql1 = "select * from [Sheet1$] WHERE abc = 'Y'" CNN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='excel 12.0;hdr=yes;imex=1';Data Source=" & ThisWorkbook.FullName Set rst = CNN.Execute(sql1) For i = 1 To rst.Fields.Count Cells(1, i) = rst.Fields(i - 1).Name Next Sheets("Sheet2").[a2].CopyFromRecordset rst CNN.Close Set CNN = Nothing End Sub