Excel VBA_ADO+SQL实例集锦.doc

1, 包含空值的记录 f13 is null ‘/dispbbs.asp?boardID=5ID=46032page=1 ‘订单生成系统.xls ‘f6-第6列，f2-第2列 Private Sub Worksheet_Activate() On Error Resume Next Dim x As Object, yy As Object, sql As String Set x = CreateObject(ADODB.Connection) x.Open Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;hdr=no;;Data Source= ActiveWorkbook.FullName sql = select f6,f2,f3,f4,f5,f7,f13,f24 -f25 from [sheet1$] where f24 -f25f17 and (f13C3 or f13 is null) ‘不等于字符串用 ‘C3’ 包含空值用 is null Set yy = x.Execute(sql) Range(a:h).ClearContents Range(a1:h1) = Array(编号, 品名, 规格, 产地, 单位, 件装, 属性, 计划) ‘表头 另外赋值 [a2].CopyFromRecordset yy Set yy = Nothing Set x = Nothing End Sub 2，用ADO Connection对象查询 Option Explicit Public conn As ADODB.Connection Sub Myquery() Dim sConnect$, sql1$ Set conn = CreateObject(adodb.connection) Sheets(sheet1).Cells.ClearContents sConnect = provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0; _ Data Source= ThisWorkbook.Path \ ThisWorkbook.Name sql1 = select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性= 采购 表格名要用[$],条件部分用单引号 ThisWorkbook.Sheets(sheet1).Cells(2, 1).CopyFromRecordset conn.Execute(sql1) copy后面紧接SQL查询执行语句 With Sheets(sheet1) .Range(A1) = 物料代码 建立表头 .Range(B1) = 物料描述 .Range(C1) = 属性 .Range(D1) = 单位 End With conn.Close 可不用每次关闭数据源的连接 End Sub 3，用记录集执行单个查询 Option Explicit Sub Myquery() Dim rd As ADODB.Recordset Dim i%, j%, k%, sConnect$, sql1$, str$ Set rd = New ADODB.Recordset str = 外协 Sheets(sheet1).Cells.ClearContents sConnect = provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0; _ Data Source= ThisWorkbook.Path \ ThisWorkbook.Name conn.Open sConnect 打开数据源 sql1 = select 物料代码,物料描述,属性,单位 from [物料代码表$] where 属性= 采购 表格名要用[$],条件部分用单引号 rd.Open sql1, sConnect, adOpenForwardOnly, adLockReadOnly ThisWorkbook.Sheets(sheet1).Cells(2, 1).CopyFromRecordset rd With Sheets(sheet1)