|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
就当练习sql语句了
文件合并的难题.rar
(31.22 KB, 下载次数: 1)
Option Explicit
Sub a()
Dim cn As New ADODB.Connection
Dim r As New ADODB.Recordset
Dim cnn As New cConnection
Dim rs As New cRecordset
Dim myf$, sql$, s$, m%, i%, rq$, bt
On Error GoTo 100
bt = Array("日期", "学号", "姓名", "得分")
cnn.CreateNewDB ":memory:"
sql = "create table t(日期 text,学号 TEXT,姓名 TEXT,得分 text)"
cnn.Execute sql
myf = Dir(ThisWorkbook.Path & "\*.xls")
cnn.BeginTrans
Do While myf <> ""
If Left(myf, 1) <> "~" And myf <> ThisWorkbook.Name Then
m = m + 1
cn.Open "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=YES'; Data Source=" & myf
sql = "select * from [" & Replace(myf, ".xls", "") & "$a1:d] where 日期 is not null"
r.Open sql, cn, 1, 1
Do While Not r.EOF
s = "'" & r.Fields(0) & "','" & r.Fields(1) & "','" & r.Fields(2) & "'," & r.Fields(3)
sql = "insert into t values(" & s & ")"
cnn.Execute sql
r.MoveNext
Loop
End If
myf = Dir()
If r.State Then r.Close
If cn.State Then cn.Close
Loop
cnn.CommitTrans
sql = "create table tmp(学号,姓名)"
cnn.Execute sql
sql = "INSERT INTO tmp SELECT DISTINCT 学号,姓名 FROM t order by 学号"
cnn.Execute sql
sql = "create table 日期(日期 text)"
cnn.Execute sql
sql = "INSERT INTO 日期 SELECT DISTINCT 日期 FROM t order by 日期"
cnn.Execute sql
Set cn = Nothing
Set r = Nothing
[a8:z99] = ""
Dim ARR
sql = "SELECT 日期 FROM 日期"
rs.OpenRecordset sql, cnn
ARR = rs.GetRows
Cells.Clear
For i = 1 To m
sql = "SELECT A.日期,A.学号,A.姓名,ifnull(B.得分,""NO"") AS 得分 FROM (select 日期,学号,姓名 from 日期,tmp) AS A " _
& "LEFT JOIN (SELECT 学号,得分 from t where 日期='" & ARR(0, i - 1) & "') AS B " _
& "on A.学号=B.学号 WHERE 日期='" & ARR(0, i - 1) & "'"
rs.OpenRecordset sql, cnn
[a1].Offset(0, (i - 1) * 4).Resize(1, 4) = bt
Range("a2").Offset(0, (i - 1) * 4).CopyFromRecordset rs.GetADORsFromContent
Next
Set cnn = Nothing
Set rs = Nothing
Exit Sub
100:
Stop
End Sub
|
|