|
- Sub test1() '另一法 测试
- Dim ar, Cel As Range, Dict As Object, i As Long, j As Long, k
- Dim Conn As Object, SQL As String, p As String, strKeyword As String
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- p = ThisWorkbook.Path & "\分簿\"
- If Dir(p, vbDirectory) = "" Then MkDir p
- 'p = p & "\"
- Set Dict = CreateObject("Scripting.Dictionary")
- ReDim br(1 To Worksheets.Count) As String
- ReDim cr(1, 1 To Worksheets.Count) As String
- strKeyword = "客户名称"
- For j = 1 To Worksheets.Count
- With Worksheets(j)
- Set Cel = .Cells.Find(strKeyword, , , 1)
- ar = .Range(Cel, .Cells(.Rows.Count, Cel.Column).End(xlUp))
- For i = 2 To UBound(ar)
- k = Trim(ar(i, 1))
- If Len(k) Then If Not Dict.Exists(k) Then Dict.Add k, vbNullString
- Next
- br(j) = .Name
- cr(0, j) = Cel.Row + Cel.MergeArea.Rows.Count
- With .Range("A1").CurrentRegion
- cr(1, j) = "SELECT * FROM [" & br(j) & "$" & Intersect(.Offset(0), .Offset(cr(0, j))).Address(0, 0) & "] WHERE F" & Cel.Column & "="
- End With
- End With
- Next
- Set Cel = Nothing
- Set Conn = CreateObject("ADODB.Connection")
- Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=NO';Data Source=" & ThisWorkbook.FullName
- For Each k In Dict.Keys
- Worksheets(br).Copy
- With ActiveWorkbook
- For j = 1 To UBound(br)
- With .Worksheets(br(j))
- .DrawingObjects.Delete
- .UsedRange.Offset(cr(0, j) - 1).ClearContents
- .Cells(cr(0, j), "A").CopyFromRecordset Conn.Execute(cr(1, j) & "'" & k & "'")
- End With
- Next
- .SaveAs p & k, 51
- .Close
- End With
- Next
- Conn.Close
- Set Conn = Nothing
- Set Dict = Nothing
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- Beep
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|