[已解决]老师，我这代码能不能再优化精简下·！？

excel6699 · 发表于 2012-10-22 13:56

{:26:}

ttach]253449[/attach]

最佳答案

zjdh

2012-10-22 15:29

本帖最后由 zjdh 于 2012-10-22 15:32 编辑

Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection, sql$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 数据一,数据二,数据三,数据四,数据五,数据六,数据七,sum(数据八) from [原表$B4:I65536]" & _
      "GROUP BY 数据一,数据二,数据三,数据四,数据五,数据六,数据七"
With Workbooks.Add
      With .Sheets(1)
         ActiveWindow.DisplayGridlines = False
         .Name = [A2]
         .Range("B4").Resize(1, 8) = Array("数据一", "数据二", "数据三", "数据四", "数据五", "数据六", "数据七", "数据八")
         .Range("B5").CopyFromRecordset cn.Execute(sql)
         .Range("B5").CurrentRegion.Borders.LineStyle = xlContinuous
         .Range("B5").CurrentRegion.Borders.ColorIndex = 12
      End With
End With
cn.Close
Set cn = Nothing
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & [A2], FileFormat:=xlExcel8
End Sub

基础资料数据表.rar (14.48 KB, 下载次数: 1)

zjdh · 发表于 2012-10-22 15:29

本帖最后由 zjdh 于 2012-10-22 15:32 编辑

Private Sub CommandButton1_Click()
Dim cn As New ADODB.Connection, sql$
Application.ScreenUpdating = False
Application.DisplayAlerts = False
cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 数据一,数据二,数据三,数据四,数据五,数据六,数据七,sum(数据八) from [原表$B4:I65536]" & _
      "GROUP BY 数据一,数据二,数据三,数据四,数据五,数据六,数据七"
With Workbooks.Add
      With .Sheets(1)
         ActiveWindow.DisplayGridlines = False
         .Name = [A2]
         .Range("B4").Resize(1, 8) = Array("数据一", "数据二", "数据三", "数据四", "数据五", "数据六", "数据七", "数据八")
         .Range("B5").CopyFromRecordset cn.Execute(sql)
         .Range("B5").CurrentRegion.Borders.LineStyle = xlContinuous
         .Range("B5").CurrentRegion.Borders.ColorIndex = 12
      End With
End With
cn.Close
Set cn = Nothing
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & [A2], FileFormat:=xlExcel8
End Sub

基础资料数据表.rar (14.48 KB, 下载次数: 1)

		自动登录	找回密码
密码			注册

[已解决]老师，我这代码能不能再优化精简下·！？

相关帖子