Excel VBAADOSQL实例集锦.doc

1, 包含空值的记录 f13 is null ‘/dispbbs.asp?boardID=5&ID=46032&page=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 -f25<f17 and (f13<>'C3' or f13 is null)" ‘不等于字符串用 ‘C3’ 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 Thi