|
- Private Sub CommandButton1_Click()
- Dim Nowbook As Workbook
- Dim ShName As Variant
- Dim Arr As Variant
- Dim myNewWorkbook As Integer
- Dim intRow As Integer, t As Single
- Dim ARow As Integer
- Dim sWBName
- '关闭刷屏
- Application.ScreenUpdating = False
- '禁止显示提示和警告消息
- Application.DisplayAlerts = False
-
- myNewWorkbook = Application.SheetsInNewWorkbook
- ShName = Array("汇总数据")
- Arr = Array("数据一", "数据二", "数据三", "数据四", "数据五", "数据六", "数据七", "数据八")
- Application.SheetsInNewWorkbook = 1
- sWBName = [a2]
- Set Nowbook = Workbooks.Add
- With Nowbook
- With .Sheets(1)
- ActiveWindow.DisplayGridlines = False '关闭网格线
- .Name = ShName(0)
- .Range("B4").Resize(1, UBound(Arr) + 1) = Arr
- End With
- End With
- Set Nowbook = Nothing
- Application.SheetsInNewWorkbook = myNewWorkbook
-
- t = Timer
- Dim cn As New ADODB.Connection, sql As String
- 'intRow = Sheet1.Range("B65536").End(xlUp).Row
- cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- sql = "select 数据一,数据二,数据三,数据四,数据五,数据六,数据七,sum(数据八) from [原表$B4:I" & intRow & "]" & _
- "GROUP BY 数据一,数据二,数据三,数据四,数据五,数据六,数据七"
-
- Sheets(1).Range("B5").CopyFromRecordset cn.Execute(sql)
-
- cn.Close
- Set cn = Nothing
-
- 'On Error Resume Next
- 'With Range("B5").Resize(K, 8) '绘制表格的问题2
- With Range("B5").CurrentRegion
- With Borders
- LineStyle = xlContinuous
- ColorIndex = 12
- End With
- End With
- Debug.Print ThisWorkbook.Path & Application.PathSeparator & sWBName & ".xls"
- ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & sWBName, FileFormat:=xlExcel8
- '如果在03里,fileformat:=xlexcel7
- ActiveWorkbook.Close True
- End Sub
复制代码 |
|