3.1查询方法与示例
实例一:
从工作簿“数据源”工作表中提取所有的数据信息,保存到“查询表“。
工号 |
姓名 |
部门 |
学历 |
工龄 |
A001 |
张1 |
财务部 |
本科 |
20 |
A002 |
张2 |
行政部 |
硕士 |
21 |
A003 |
张3 |
销售部 |
大专 |
22 |
A004 |
张4 |
财务部 |
本科 |
23 |
A005 |
张5 |
行政部 |
硕士 |
24 |
A006 |
张6 |
销售部 |
大专 |
25 |
A007 |
张7 |
财务部 |
本科 |
26 |
A008 |
张8 |
行政部 |
硕士 |
27 |
A009 |
张9 |
销售部 |
大专 |
28 |
A010 |
张10 |
财务部 |
本科 |
29 |
A011 |
张11 |
行政部 |
硕士 |
30 |
A012 |
张12 |
销售部 |
大专 |
31 |
A013 |
张13 |
财务部 |
本科 |
32 |
A014 |
张14 |
行政部 |
硕士 |
33 |
A015 |
张15 |
销售部 |
大专 |
34 |
A016 |
张16 |
财务部 |
本科 |
35 |
A017 |
张17 |
行政部 |
硕士 |
36 |
A018 |
张18 |
销售部 |
大专 |
37 |
A019 |
张19 |
财务部 |
本科 |
38 |
A020 |
张20 |
行政部 |
硕士 |
39 |
A021 |
张21 |
销售部 |
大专 |
40 |
查询代码如下:
Sub 从工作簿查询数据()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim ws As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Worksheets("查询表")
ws.Cells.Clear
'建立与Excel工作表的连接
With cnn
.provider = "microsoft.ace.oledb.12.0"
.connectionstring = "extended properties =excel 12.0;datasource=" & ThisWorkbook.Path & "\实例一.xlsm"
.Open
End With
'设置查找语句
SQL = "select * from [数据源$] "
rs.Open SQL, cnn, adopenkeyset,adlockoptimistic
'复制字段列表,制作报表标题
For i = 1 To rs.Fields.Count
ws.Cells(1, i) = rs.Fields(i - 1).Name
Next i
'用CopyFromRecordset方法复制记录到查询表中
ws.Range("a2").CopyFromRecordsetrs
With ws.UsedRange
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
'关闭连接,释放变量
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
重要语句说明:
1、建立与工作簿的ADO连接:
With cnn
.provider = "microsoft.ace.oledb.12.0"
.connectionstring = "extended properties =excel 12.0;datasource=" & ThisWorkbook.Path & "\实例一.xlsm"
这里的“带完整路径的工作簿名称“需要指定具体的工作簿。
(1)如果是当前运行宏的工作簿,可以写为thisworkbook.fullname.
(2)如果是其他工作簿,就必须写完全路径的文件。
2、获取字段个数和名称
获取字段个数是使用fields集合的count属性。
rs.fields.count
获取字段名称是使用field对象的name属性,注意第1个字段是序号是0,第2个字段序号是1,最后一个字段的序号是rs.fields.count-1
rs.fields(i).name
3、复制查询结果到工作表
对查询到的数据结果(注意,数据结果并不包括标题,而仅仅是满足条件的数据行,也就是数据记录),可以使用range对象的copyfromrecordset方法,即:
ws.range(“a2”).copyfromrecordset rs
如果要循环每个记录来输出数据就比较麻烦,而且速度也很慢,但在某些情况下,必须使用循环方法来输出数据,例如,输出到窗体上的复合框和列表框。此时,就要使用下面的循环语句:
for i= 1 to rs.recordcount
forj = 1 to rs.fields.count
cells(i+1,j)=rs.fields(j-1).value
nextj
rs.movenext
next i
不论输出中个字段,都必须使用movenext方法来将指针往下逐行移动来获