|
Option Explicit
Sub A()
Dim cnn, rs As Object, Sql As String, i%, myf, d, j%, s, tmp
myf = ThisWorkbook.Path & "\在制品.xls"
Set cnn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.Recordset")
Set d = CreateObject("Scripting.Dictionary")
cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & myf
Sql = "SELECT 生产编号,工序名称,重量+0,FORMAT(时间,""YYYY-MM-DD HH:MM:SS"") FROM [Sheet1$A1:AP] WHERE 生产编号 is not null "
rs.Open Sql, cnn, 1, 1
Do While Not rs.EOF
d(rs.Fields(0) & "|" & rs.Fields(1)) = rs.Fields(2) & "|" & rs.Fields(3)
rs.movenext
Loop
Set rs = Nothing
Set cnn = Nothing
Dim arr
arr = Sheet1.[a1].CurrentRegion
For i = 3 To UBound(arr)
For j = 12 To 33 Step 2
s = arr(i, 1) & "|" & arr(2, j)
If d.exists(s) Then
tmp = Split(d(s), "|")
arr(i, j) = tmp(0)
arr(i, j + 1) = tmp(1)
Else
arr(i, j) = ""
arr(i, j + 1) = ""
End If
Next
Next
[a1].Resize(UBound(arr), UBound(arr, 2)) = arr
Set d = Nothing
End Sub
|
评分
-
2
查看全部评分
-
|