在VBA中创建数据透视表

Question

我有一个xlsm文件，其中包含一些VBA代码。我基本上有一个文件夹中有很多csv文件。当我在xlsm文件中执行VBA代码时，它需要遍历每个csv文件，选择其中的数据，然后从数据创建数据透视表，并将带有数据透视表的工作表导出到新的工作簿中。

它需要在这个意义上的数据（行）的量不每个csv文件相同的动态的，因此它首先需要选择数据块，然后创建的PivotCaches等

尝试创建数据透视表时，我的代码失败。请参阅下面的相关代码。此代码揭开序幕的工作，遍历每个CSV：

Sub RunBatch() 

Dim Filename, Pathname As String 
Dim wb As Workbook 

Pathname = "\\troy\Anfield\Product & Risk Management\Muhammad\2014\PnL Attribution Reports\20140822\" 
Filename = Dir(Pathname & "*.csv") 

Do While Filename <> "" 
    Set wb = Workbooks.Open(Pathname & Filename) 
    CreatePivotTableSummary wb 
    wb.Close SaveChanges:=True 
    Filename = Dir() 
Loop 

End Sub 

此代码是实际创建支点之一：

Sub CreatePivotTableSummary(wb As Workbook) 

Dim WorkbookName As String 
WorkbookName = wb.Name 

wb.Activate 

Set DataRange = Range(Selection, Selection.End(xlToRight)) 
Set DataRange = Range(Selection, Selection.End(xlDown)) 
Set OutputRange = Sheet2.Range("A3") 

Sheets.Add 

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataRange, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=OutputRange, TableName:=WorkbookName, DefaultVersion:=xlPivotTableVersion14 

Sheet2.Select 

With wb.ActiveSheet.PivotTables(WorkbookName) 
    .PivotFields("Portfolio").Orientation = xlRowField 
    .PivotFields("Portfolio").Position = 1 

    .PivotFields("TradePortfolio").Orientation = xlRowField 
    .PivotFields("TradePortfolio").Position = 2 

    .PivotFields("InstrType").Orientation = xlRowField 
    .PivotFields("InstrType").Position = 3 

    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("TPL"), "Sum of TPL", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("ThTPL Deco"), "Sum of ThTPL Deco", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Corr Attr"), "Sum of Corr Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Cr Attr"), "Sum of Cr Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Cross Effec"), "Sum of Cross Effec", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("FX Attr"), "Sum of FX Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Infl Attr"), "Sum of Infl Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("IR Attr"), "Sum of IR Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Price Attr"), "Sum of Price Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Residual"), "Sum of Residual", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Time Attr"), "Sum of Time Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Trd Attr"), "Sum of Trd Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("Vol Attr"), "Sum of Vol Attr", xlSum 
    .AddDataField ActiveSheet.PivotTables(WorkbookName).PivotFields("YC Attr"), "Sum of YC Attr", xlSum 

    .PivotFields("Sum of TPL").NumberFormat = "#,##0" 
    .PivotFields("Sum of ThTPL Deco").NumberFormat = "#,##0" 
    .PivotFields("Sum of Corr Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Cr Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Cross Effec").NumberFormat = "#,##0" 
    .PivotFields("Sum of FX Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Infl Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of IR Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Price Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Residual").NumberFormat = "#,##0" 
    .PivotFields("Sum of Time Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Trd Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of Vol Attr").NumberFormat = "#,##0" 
    .PivotFields("Sum of YC Attr").NumberFormat = "#,##0" 

    .RowAxisLayout xlTabularRow 
    .TableStyle2 = "PivotStyleMedium2" 

    .PivotFields("InstrType").PivotFilters.Add Type:=xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables(WorkbookName).PivotFields("Sum of TPL"), Value1:=0 

End With 

End Sub 

它未能在这一点上：

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataRange, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=OutputRange, TableName:=WorkbookName, DefaultVersion:=xlPivotTableVersion14 

与错误：

Invalid procedure call or argument

请有人协助？

谢谢

Answer 1

有时候，当任何数据范围并没有列标题，我们得到这个错误。 检查数据范围的第一行是否对所有列都有一些标题。

如果无法找到列标题，则数据透视表创建失败。