作者：绿色风
分类：
时间：2022-08-18
浏览：103
楼主
wise |
Q：如何用VBA+ADO+SQL来找出数学成绩进步及退步最多者?
A:ALT+F11→插入模块→输入以下代码:
- Public Sub RunMacro()
- For Each loWindow In Application.Windows
- If LCase(loWindow.Caption) = "result.xls" Then
- loWindow.Close (False)
- End If
- Next
-
- If Dir("c:\result.xls") <> "" Then
- Kill "c:\result.xls"
- End If
-
- ActiveWorkbook.Save
-
- Set loConnection = CreateObject("ADODB.Connection")
- Set loRecordset = CreateObject("ADODB.Recordset")
- loConnection.Open "Driver={Microsoft Excel Driver (*.xls)}; " & _
- "DBQ=" + ActiveWorkbook.FullName + ";" & _
- "ReadOnly=True"
- Dim lcBaseQuery As String
- lcBaseQuery = "(select a.学生, a.考试日期 as 第一次考试日期, b.考试日期 as 第二次考试日期, " & _
- "a.数学成绩 as 第一次数学成绩, b.数学成绩 as 第二次数学成绩, " & _
- "(b.数学成绩-a.数学成绩) as 相差分数, " & _
- "iif (相差分数 > 0, '进步 ', '退步 ')&abs(相差分数)&'分' as 批注 " & _
- "from [score$] a, [score$] b " & _
- "where b.学生 = a.学生 " & _
- "and b.考试日期 > a.考试日期 " & _
- "order by (b.数学成绩-a.数学成绩) desc)"
-
- loRecordset.Open _
- "select * " & _
- "into [Excel 8.0;Database=c:\result.xls].[sheet1] " & _
- "from " & _
- lcBaseQuery & _
- "where 相差分数 = (select max(相差分数) from " & lcBaseQuery & ") " & _
- "or 相差分数 = (select min(相差分数) from " & lcBaseQuery & ")", loConnection
- loConnection.Close
- Workbooks.Open ("c:\result.xls")
- ActiveWorkbook.ActiveSheet.UsedRange.Font.Name = "Tahoma"
- ActiveWorkbook.ActiveSheet.UsedRange.Font.Size = 8
- ActiveWorkbook.ActiveSheet.Cells.EntireRow.AutoFit
- ActiveWorkbook.ActiveSheet.Cells.EntireColumn.AutoFit
- End Sub
 找出数学成绩进步及退步最多者.rar
|
2楼
chury11 |
慢慢研究 |
3楼
lisan |
谢谢！学习中…… |
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，
经与ExcelTip.Net站长Apolloh商议并征得其同意，
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，
供有需要的人士查询使用，也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议，
可与本人沟通提出，或于本站点留言，我们会尽快处理。
在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一