|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
SQL+字典+数组:
- Sub Getdata()
- Dim Cn As Object, Sqlstr$, Wb$
- Dim Arr, k%, Dic As Object
- Wb = Replace(ThisWorkbook.FullName, "总表", "到货记录表")
- Set Cn = CreateObject("Adodb.Connection")
- Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Wb
- Wb = Wb & "`.`Sheet1$]"
- Sqlstr = "Select T1.材料采购号,T1.A As A,T2.B As B " _
- & "From (Select 材料采购号,Sum(材料到货数) As A From [" & Wb & " Group By 材料采购号) T1," _
- & "(Select 材料采购号,Max(到货时间) As B From [" & Wb & " Group By 材料采购号) T2 " _
- & "Where T1.材料采购号=T2.材料采购号"
- Arr = Application.Transpose(Cn.Execute(Sqlstr).GetRows): Cn.Close: Set Cn = Nothing
- Set Dic = CreateObject("Scripting.Dictionary")
- For k = 1 To UBound(Arr): Dic(Arr(k, 1)) = Array(Arr(k, 2), Arr(k, 3)): Next
- Arr = Range("E4", [E65536].End(3)(1, 3))
- For k = 1 To UBound(Arr)
- If Dic.exists(Arr(k, 1)) Then Arr(k, 2) = Dic(Arr(k, 1))(0): Arr(k, 3) = Dic(Arr(k, 1))(1)
- Next
-
- [E4].Resize(k - 1, 3) = Arr: Dic.RemoveAll
- End Sub
复制代码 |
|