|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Option Explicit
Sub test()
Dim Cn As Object, Sq$, ar, br(1 To 2), i%, j%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
Sq = "SELECT DISTINCT Replace(供货商名称,'.','') FROM [应付$]"
ar = Cn.Execute(Sq).GetRows
br(1) = "SELECT * FROM [应收$] WHERE Replace(客户名称,'.','')='"
br(2) = "SELECT * FROM [应付$] WHERE Replace(供货商名称,'.','')='"
For j = 0 To UBound(ar, 2)
If Not IsNull(ar(0, j)) Then
Sheets(Array("应收", "应付")).Copy
With ActiveWorkbook
For i = 1 To 2
.Sheets(i).UsedRange.Offset(1).ClearContents
Sq = br(i) & ar(0, j) & "'"
.Sheets(i).[a2].CopyFromRecordset Cn.Execute(Sq)
Next
.SaveAs ThisWorkbook.Path & "\" & ar(0, j), 51
.Close
End With
End If
Next
Cn.Close
Set Cn = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "ok", 64
End Sub
|
评分
-
1
查看全部评分
-
|