|
Sub test2()
Dim con, rs, sql$, i%
'1)创建对象
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
' con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\员工.accdb"
'4)编辑SQL
sql = "select a.年级,a.层次,a.专业名称,a.课程名称,a.选课人数,a.班代码,b.学号,b.姓名,b.班代码1,b.备注 from " _
& "[开课计划$] a,[学员名单$] b where a.年级=b.年级 and a.层次=b.层次 and a.专业名称=b.专业名称1"
'5)执行SQL
Set rs = con.Execute(sql) '存入Recordset对象
' MsgBox IIf(rs.BOF And rs.EOF, "没记录", "有记录")
'6)导入工作簿
With Sheets(1)
.Cells.Clear
For i = 0 To rs.Fields.Count - 1 '字段
.Cells(1, i + 1) = rs.Fields(i).Name
Next
.Range("A2").CopyFromRecordset rs '记录集
.Cells.EntireColumn.AutoFit '可选
.ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion, , xlYes '可选,把结果区域改作列表
End With
'7)关闭连接,释放对象
rs.Close: Set rs = Nothing
con.Close: Set con = Nothing
End Sub
求助2.rar
(25.2 KB, 下载次数: 2)
|
|