|
Sub 分配()
Application.ScreenUpdating = False
Dim Conn As Object, Rst As Object
Dim strConn As String, strSQL As String
Dim i As Integer, PathStr As String
Set Conn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
PathStr = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is <= 11
strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
Case Is >= 12
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select
Conn.Open strConn
Set reg = CreateObject("VbScript.RegExp")
reg.Pattern = "\W+"
Set d = CreateObject("Scripting.Dictionary")
Set sh = Sheets("数据")
With sh
r = .Cells(.Rows.Count, 1).End(3).Row
For i = 2 To r
d(.Cells(i, 4).Value) = ""
Next
ar = d.keys
End With
For Each st In Sheets
If st.Name <> "数据" Then
sn = st.Name
st.Range("d4:h500").ClearContents
Set mh = reg.Execute(sn)
cn = mh(0)
m = 4
For j = 1 To UBound(ar)
Sql = "Select 影片,上映时间,[挂牌价(元)],[渠道结算价(元)],已出售票数 from [数据$] Where 影院 = '" & cn & "' and 影厅编号 = '" & ar(j - 1) & " '"
Set Rst = Conn.Execute(Sql)
With Sheets(sn)
.Cells(m, 4).CopyFromRecordset Rst
m = m + 10
End With
Next
End If
Next
Application.ScreenUpdating = True
End Sub
|
|