|
该代码能实现所要之功能,在本机上一切OK,随机检查了几个班,没有问题。你可以试一下,希望能帮到你。
Sub kcap()
Dim conn As Object, zfc As String, L As Integer
Set conn = CreateObject("ADODB.Connection")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("安排").Range("e2:g65536").ClearContents
With Sheets("总课表")
.Range(.Cells(7, "d"), .Cells(.Range("d65536").End(3).Row, .Range("cz7").End(xlToLeft).Column)).Copy
End With
Sheets.Add: ActiveSheet.Name = "linshi"
[g1].PasteSpecial Transpose:=True
conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0;HDR=yes';data source=" & ThisWorkbook.FullName
For L = 1 To 20
If Cells(2, 9) = "" Then Exit For
Range("a65536").End(3).Offset(1, 0).CopyFromRecordset conn.Execute("select 名称,学生容量,课程,学生人数,教师,""第" & L & "节"" from [linshi$g1:k65536] where 教师 is not null;")
Columns("i:k").Delete
Next L
For Each dyg In Range(Cells(2, 1), Cells(Range("a65536").End(3).Row, "a"))
If dyg.Offset(0, 2) = "" Or dyg.Offset(0, 3) = "" Or dyg.Offset(0, 4) = "" Or dyg.Offset(0, 5) = "" Then GoTo aaa
zfc = "update [安排$a1:g65536] set 教室=" & dyg.Value & ",教师=""" & dyg.Offset(0, 4) & """,节次=""" & dyg.Offset(0, 5) & """ where 课程=""" & dyg.Offset(0, 2) & """ and 学号 in(select top " & dyg.Offset(0, 3) & " 学号 from [安排$a1:g65536] where 课程=""" & dyg.Offset(0, 2) & """ and 教师 is null)"
conn.Execute (zfc)
aaa: Next dyg
conn.Close: Set conn = Nothing
Sheets("linshi").Delete
Sheet4.Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
|
|