PivotCaches.add Error 5 - 2003 to 2010 Macros

问题:

I have an Excel 2003 .xls file that I am trying to run in Excel 2010. I first saved the file as .xlsm and added the directory as trusted in the Trust Center. I am getting an error code (indicated below by the arrows). Note: If I change the PivotTableVersion to 12, it still gives me the same error. Code is below.

 Sub Create_pivot()
        Wbname = ActiveWorkbook.Name
    '   Insert columns to make room for pivot table
        Columns("A:I").Select
        Selection.Insert Shift:=xlToRight
        myData = Sheets(ActiveSheet.Name).[J1].CurrentRegion.Address
        mySheet = ActiveSheet.Name & "!"
        tableDest = "[" & Wbname & "]" & mySheet & "R1C1"
>>>>    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
            mySheet & myData).CreatePivotTable TableDestination:=tableDest, TableName _
            :="RTP_alerts", DefaultVersion:=xlPivotTableVersion10
        With ActiveSheet.PivotTables("RTP_alerts").PivotFields("Application")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("RTP_alerts").PivotFields("Object")
            .Orientation = xlRowField
            .Position = 2
        End With
        ActiveSheet.PivotTables("RTP_alerts").AddDataField ActiveSheet.PivotTables( _
            "RTP_alerts").PivotFields("Alerts"), "Count of Alerts", xlCount
        ActiveWorkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False

        Columns("G:I").Select
        Selection.Delete Shift:=xlToLeft
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "Owner"
        Range("E2").Select
        ActiveCell.FormulaR1C1 = "Problem Ticket"
        Columns("E:E").ColumnWidth = 13
        Range("F2").Select
        ActiveCell.FormulaR1C1 = "Comments"
        Columns("F:F").ColumnWidth = 48
    End Sub

回答1:

They changed the object model for PivotCaches. The method you need in 2007-2010 (that use VBA version 7 instead of version 6) is

PivotCaches.Create

You can use conditional compilation to create code that will work in both, like this:

Dim pc As PivotCache
Dim pt As PivotTable
Dim lVBAVer As Long

lVBAVer = CLng(Application.VBE.Version)

#If lVBAVer <= 6 Then
    Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.UsedRange)
#Else
    Set pc = ActiveWorkbook.PivotCaches.create(xldtatabase, Sheet1.UsedRange)
#End If

Set pt = pc.CreatePivotTable(Sheet2.Range("A3"))

The hashes preceding the If/EndIf keywords means that you wont get compile errors when using methods that don't exist in that version, but that it will still execute.

回答2:

I recently upgraded to Excel 2010 from Excel 2003. I had a similar problem with many .xls file containing macros: a generic error [-2147417848 (80010108)] on instructions as these:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    NomeFoglioDett & "!R1C1:R" & UltimaRiga & "C18").CreatePivotTable TableDestination:="", _
    TableName:="Tabella_pivot1", DefaultVersion:=xlPivotTableVersion10

I tried various actions: saving as .xlsm or .xls (97-2003), modifying from PivotCaches.Add to PivotCaches.Create, changing the DefaultVersion from xlPivotTablesVersion10 to xlPivotTablesVersion12 or 14. Nothing worked. In the end I realized it could be something just ABOVE the creation of the pivot table. Here the instructions that didn’t work:

Dim Anno As String
Dim Percorso As String
Dim NomeFileOut As String
Dim NomeFoglioDett As String
Dim UltimaRiga As String

Anno = "2013"
Percorso = ActiveWorkbook.Path & "\"
NomeFileOut = "MyName1." & Anno & ".0m.1.BIn.xls"
NomeFoglioDett = " MyName2." & Anno & ".0m.1.Tp2"
'.
'.
'.
'================= Creazione Tabella Pivot
'Riapro il Batch Input in formato Excel - foglio dei tipi 2
Workbooks.Open Filename:=Percorso & NomeFileOut
'Cerco l'ultima riga piena
UltimaRiga = Range("A60000").End(xlUp).Row
'Creo la tabella Pivot
'totali Importi segnati per Descrizioni ridotte
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    NomeFoglioDett & "!R1C1:R" & UltimaRiga & "C18").CreatePivotTable TableDestination:="", _
    TableName:="Tabella_pivot1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("Mese")
    .Orientation = xlRowField
    .Position = 1
End With
With . . .

Here the same instructions with a change – Sheets(2).select – that solved the problem for the file saved in .xls and .xlsm:

'================= Creazione Tabella Pivot
'Riapro il Batch Input in formato Excel - foglio dei tipi 2
Workbooks.Open Filename:=Percorso & NomeFileOut
    Sheets(2).Select
'Cerco l'ultima riga piena
UltimaRiga = Range("A60000").End(xlUp).Row
'Creo la tabella Pivot
'totali Importi segnati per Descrizioni ridotte
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    NomeFoglioDett & "!R1C1:R" & UltimaRiga & "C18").CreatePivotTable TableDestination:="", _
    TableName:="Tabella_pivot1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard . . .

It seems that Excel 2010 accepts the creation of the pivot table in the old way but wants more precision in opening a .xls file: it wants to know which sheet has to be read.