|
模块一
Option Explicit
'例2:查询数据,导出到Excel
Sub test2(x, y)
Dim con, rs, sql$, i%
'1)引用ADO对象,以下是后期绑定的方式
Set con = CreateObject("adodb.connection") '建立ADO连接对象
Set rs = CreateObject("adodb.recordset") '建立ADO记录集对象
'2)创建连接
'已连接
'3)建立连接
con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
'4)编辑SQL
sql = "select top 5 姓名,工资 from (select * from [Sheet1$a:d] where 员工类型='" & y & "' order by 工资 desc)"
'5)执行SQL
Set rs = con.Execute(sql) '存入Recordset对象
' MsgBox IIf(rs.BOF And rs.EOF, "没记录", "有记录")
'6)导入工作簿
Sheets(x).Activate
Sheets(x).Cells.Clear
For i = 0 To rs.Fields.Count - 1 '字段
Cells(2, i + 1) = rs.Fields(i).Name
Next
Range("A3").CopyFromRecordset rs '记录集
Range("A1") = y & "员工前五名" '填写标题
Range("A1:B7").Borders.LineStyle = xlContinuous '添加边框
Range("A1:B1").Merge '合并标题栏
Range("A1").HorizontalAlignment = xlCenter '标题居中
'7)关闭连接,释放对象
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing
End Sub
Sheet 1
Option Explicit
Private Sub CommandButton1_Click()
Call test2(2, "一类")
Call test2(3, "二类")
End Sub
|
评分
-
查看全部评分
|