|
- Private Sub deldeldel()
-
- Dim Sht As Worksheet
- Set Sht = Sheet4
- Dim Rng As Range, oRng As Range, oRng1 As Range, oRng2 As Range
- Set Rng = Sht.Range("A1:Z" & Sht.Cells(65536, 1).End(xlUp).Row + 10)
- 'Set oRng = Sht.Range("B5:B" & Sht.Cells(65536, 1).End(xlUp).Row)
- Set oRng = Sht.Range("F:F")
- 'Debug.Print Rng.Address, oRng.Address
- Dim Rs As Recordset, Str
- ''
- With Sheet2
- .Cells.Clear
- .Cells.Font.Size = 9
- End With
- ''
- Str = "Select 文件名,Count(文件名) From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Group By 文件名 "
- Str = "Select 文件名,Count(文件名),Count(大小) As Abcd,Count(日期) As bc From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Group By 文件名 "
- Str = "Select 文件名 From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] "
- Str = "Select 文件名,Count(文件名),Count(大小) ,Count(日期) From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Group By 文件名 Order By Count(日期) > 1 Desc"
- Str = "Select 文件名,Count(文件名),Count(日期) From [" & Sht.Name & "$" & Rng.Address(0, 0) & "] Group By 文件名 Order By Count(日期) Desc " ' "
- Debug.Print Str
- Set Rs = SqlRetuRs(Str)
- Sheet2.Cells(5, "A").CopyFromRecordset Rs
- Set oRng2 = Sheet1.Cells(5, "A")
- kk = 0
- With Rs
- .MoveFirst
- For ii = 0 To Rs.RecordCount - 1
- If .Fields(1) > 0 And .Fields(2) > 0 Then
- oRng2(kk, 1) = .Fields(0)
- Set oRng1 = oRng.Find(.Fields(0), LookIn:=xlValues)
- oRng2(kk, 2) = "=" & oRng.Parent.Name & "!" & oRng1.Address(0, 0)
- 'Debug.Print oRng1.Address, .Fields(0), oRng1(, 5).Address, oRng1(, 5)
- ''
- For jj = 2 To .Fields(1)
- Set oRng1 = oRng.FindNext(oRng1)
- oRng2(kk, 2 + jj - 1) = "=" & oRng.Parent.Name & "!" & oRng1.Address(0, 0)
- 'Debug.Print oRng1.Address, .Fields(0), oRng1(, 5).Address, oRng1(, 5)
- Next jj
- kk = kk + 1
- End If
- .MoveNext
- Next ii
- End With
- End Sub
复制代码
|
|