|
发表于 2016-4-7 15:25
|
显示全部楼层
本楼为最佳答案
Sub test2()
Dim p$, f$, fld$, saveAsPath
Application.ScreenUpdating = False
Application.DisplayAlerts = False
p = ThisWorkbook.Path & "\"
fld = "MyFile"
saveAsPath = p & fld & "\"
If Dir(saveAsPath) = "" Then MkDir saveAsPath
f = Dir(p & "*.csv*") '为了用于csv文件,将.xls换成.csv
Do While f <> ""
If f <> ThisWorkbook.Name Then
With Workbooks.Open(p & f)
Call test1
.SaveAs saveAsPath & VBA.Replace(f, ".CSV", ""), 51
.Close False
End With
End If
f = Dir
Loop
Shell "explorer " & saveAsPath, vbNormalFocus
End Sub
Sub test1()
Dim data As Range
Dim pc As PivotCache
Dim pt As PivotTable
Dim A
'指定数据源
Sheets(1).Activate
A = [A1].CurrentRegion
Set data = Range([A2], Cells(UBound(A), UBound(A, 2)))
'创建空白工作表,存放数据透视表。
Sheets.Add after:=Sheets(Sheets.Count)
'创建 数据透视表的缓存(PivotCache 对象)
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, data, xlPivotTableVersion11) 'Excel 2003
'创建一个基于 数据透视表的缓存(PivotCache 对象)的数据透视表。透视表左上角在A1
Set pt = pc.CreatePivotTable([A1])
With pt
'1)行
'透视字段"PRT_NO"的位置在 行
.PivotFields("PRT_NO").Orientation = xlRowField
'透视字段"SULN"的位置在 数值
.PivotFields("SULN").Orientation = xlDataField
End With
End Sub
6.rar
(16.17 KB, 下载次数: 8)
|
|