|
请高手帮我把以下代码添加个按钮 谢谢 可以拆分附件的表格
Sub Macro1()
Dim cnn As Object, rs As Object
Dim SQL$, arr, i%, desk$, a As Shape, sh As Worksheet
Set sh = ActiveSheet
If Val(Right(Application.OperatingSystem, 4)) >= 6 Then
desk = Environ("USERPROFILE") & "\Desktop\"
Else
desk = Environ("USERPROFILE") & "\桌面\"
End If
Set cnn = CreateObject("ADODB.Connection")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties=excel 12.0;data source=" & ThisWorkbook.FullName
SQL = "select distinct 地区 from [Sheet1$] where 地区 is not null"
arr = cnn.Execute(SQL).GetRows
For i = 0 To UBound(arr, 2)
SQL = "Select * From [Sheet1$] Where 地区='" & arr(0, i) & "'"
Set rs = CreateObject("ADODB.Recordset")
rs.Open SQL, cnn, 1, 3
sh.Copy
With ActiveWorkbook
With .Sheets(1)
.[a1].CurrentRegion.Offset(1 + rs.RecordCount).Clear
.[a2].CopyFromRecordset rs
For Each a In .Shapes
a.Delete
Next
End With
.SaveAs desk & arr(0, i) & ".xlsx"
.Close
End With
Next
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "ok"
End Sub |
|