用vba实现数据的查询功能,类似vb 的adodb 控件,可使用sql语句,很好用。
用到的一个比较好的例子:
On Error Resume Next
Application.ScreenUpdating = False ‘关闭屏幕更新,加快程序运行
Dim i As Integer
Dim kcmc As String
Dim CNN As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mySheet As String
Dim n As Integer
Dim SQL As String
mySheet = Worksheets(“补考名单”).Name ‘工作表名
Dim myWbName As String
myWbName = ThisWorkbook.FullName ‘工作簿名
Dim cnnStr As String
Set ws = Worksheets(“补考科目及学生名单”)
ws.Visible = True
ws.Cells.Clear
cnnStr = “Provider=microsoft.jet.oledb.4.0;” _
& “Extended Properties=Excel 8.0;” _
& “Data Source=” & myWbName
CNN.Open cnnStr
kcmc = ComboBox1.Value
‘kcmc = “单片机接口与技术”
‘查询语句
SQL = “select * from [” & mySheet & “$] where 课程名称='” & kcmc & “‘ order by 学号 ASC”
rs.Open SQL, CNN, adOpenKeyset, adLockOptimistic
n = rs.RecordCount ‘查询该课程的补考人数
rss = n
ws.Activate
Application.ScreenUpdating = False
MyColshu = rs.Fields.Count ‘工作表头列数
For i = 1 To rs.Fields.Count
ws.Cells(1, i) = rs.Fields(i – 1).Name
Next i
ws.Range(“A2”).CopyFromRecordset rs
Columns(“A:IV”).Columns.AutoFit ‘最适合列宽
Range(“A1”).Select
hhh:
rs.Close
CNN.Close
Set rs = Nothing
Set CNN = Nothing
Set ws = Nothing
Application.ScreenUpdating = True