2014多字段分类汇总类数据透视表格式的汇总加载宏.docVIP

实例21：多字段分类汇总－类数据透视表格式的汇总－加载宏/dispbbs.asp?boardid=2replyid=570898id=195189page=1skin=0Star=3 Private Sub UserForm_Initialize()??? UserForm6.Hide??? With Sheets(UserForm6.ComboBox_DataShName.Value)??????? arr = .Range(.Cells(1, 1), .Cells(1, .[iv1].End(xlToLeft).Column))??? End With??? arr = Application.Index(arr, 1, 0)??? ListBox_Column.List = arr??? ComboBox_Row.List = arr??? ComboBox_field.List = arr??? ComboBox_Mode.AddItem 求和??? ComboBox_Mode.AddItem 计数End Sub Private Sub CommandButton1_Click()??? Dim sql$??? UserForm7.Hide??? Set cnn = CreateObject(ADODB.connection)??? cnn.Open Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;;Data Source= ActiveWorkbook.FullName??? sqlmode = IIf(ComboBox_Mode.Value = 求和, sum, count)??? For j = 0 To ListBox_Column.ListCount - 1??????? If ListBox_Column.Selected(j) Then??????????? columntitle = columntitle [ ListBox_Column.List(j) ],??????? End If??? Next??? columntitle = Left(columntitle, Len(columntitle) - 1)??? sql = transform sqlmode ( [ ComboBox_field ]) select columntitle from [ UserForm6.ComboBox_DataShName.Value $] group by columntitle pivot [ ComboBox_Row ]??? Set temp = cnn.Execute(sql)??? For i = 1 To temp.fields.Count??????? Cells(TextBox_Row.Value, i) = temp.fields(i - 1).Name??? Next??? Range(TextBox_Column.Value TextBox_Row.Value + 1).CopyFromRecordset temp??? cnn.Close: Set cnn = NothingEnd Sub 使用方法： 工具－加载宏－浏览，找到下载的“分表合表及分类汇总.xla”，确定，确定，则在菜单上会增加一项“分表合表及分类汇总”，下面包含六个菜单项： ??????????? 单工作表拆分为多工作表??????????? 单工作表拆分成多工作簿??????????? 多工作表复制为多工作簿??????????? 当前工作簿多工作表合并??????????? 多工作簿工作表合并到当前表??????????? 多字段分类汇总－类数据透视表格式 卸载方法： 工具－加载宏，去掉“分表合表及分类汇总”前的对勾，确定即可。 点击浏览该文件 06-11-14 Public arr Public arr1 Public arr2 Public ar Public ar1 Sub auto_open() 加载宏 ********************************** by 赖效莹 ↓ Dim CtrButton As CommandBarControl Dim Btn(5) As CommandBarControl Set CtrButton = Application.CommandBars(1).Controls.Add