VBA练习（1）

oyzjo

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False '关闭屏幕刷新
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strsql As String
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=no';data source=" & ActiveWorkbook.FullName

strsql = "select f1,f2,f3,f4 from [数据表$] Where f1=" & Range("g2") & "and f2=" & Range("h2") & "and f3=" & Range("i2") & ""
rs.Open strsql, cnn, adOpenKeyset, adLockReadOnly

Range("a5:e65536").Clear
Range("b5").CopyFromRecordset rs '将 ADO 或 DAO Recordset
Range("A5").Resize([B5].End(4).Row - 4, 1).FormulaR1C1 = "=ROW()-4"