Error on auto-update of a pivot table cache

B

Boromyr

New member
Local time
Today, 08:28
Joined
Jul 20, 2020
Messages
15
Hi all.

I created a database that run queries and extracts an excel's file. This code use the generated file, count the lines, copies all data to an existing file that has a pivot table. After the copy, the code should update the pivot cache. It worked for a couple of weeks, but now, gives the same error on every PC i tried it.....

Any suggestions ?

Please help !

P.S.
Comments & error description are in italian.
The traduction of error description is: "Invalid routine or argument call"



Code: 
    Dim appExcel As Excel.Application
    Dim lUltRiga As Long
    Dim xlBook1 As Excel.Workbook
    Dim xlBook2 As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    Dim xlSheet2 As Excel.Worksheet
    Dim xlSheet3 As Excel.Worksheet
    

    Dim PT As PivotTable
    Set appExcel = CreateObject("Excel.Application")
    appExcel.DisplayAlerts = False
    appExcel.Visible = False
    'Con queste righe faccio aprire Excel per poi andare a lavorare sui singoli file
    'non facendo però visualizzare nulla di quello che accade
    
    Set xlBook1 = Workbooks.Open(percorso & "\Output\Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx")
    Set xlBook2 = Workbooks.Open(percorso & "\Output\EstraiSelezioneFatture.xlsx")
    Set xlSheet1 = xlBook1.Sheets("Pivot Riepilogo")
    Set xlSheet2 = xlBook1.Sheets("Base Dati")
    Set xlSheet3 = xlBook2.Sheets("EstraiSelezioneFatture")
    Set PT = xlSheet1.PivotTables("Tabella pivot1")
    xlSheet2.Activate
    xlSheet2.Range("A2:Y5000").ClearContents
    xlSheet3.Activate
    lUltRiga = Cells(Rows.Count, 1).End(xlUp).Row
    With xlSheet3
        .Range("A2" & ":Y" & lUltRiga).Copy
        xlSheet2.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    'Con le righe sopra copio i dati generati da access nella base dati
    xlSheet1.Activate
    With xlSheet1
        .Unprotect
        .Range("E3").ClearContents
        .Range("E3").Locked = False
        .Range("E3").FormulaHidden = False
        .Range("B10").Locked = False
        .Range("B10").FormulaHidden = False
        .Range("B11").Locked = False
        .Range("B11").FormulaHidden = False
    End With
    
    Set NuovaCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=percorso & "\Output\[Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx]Base Dati!R1C1:R" & lUltRiga & "C19")
    
    
    With PT
        .ChangePivotCache (NuovaCache)
        .PivotCache.Refresh
        .PivotFields("Dir. Territoriale").ClearAllFilters
        .PivotFields("Dir. Territoriale").CurrentPage = "(All)"
        .PivotFields("P.to oper.").ClearAllFilters
        .PivotFields("P.to oper.").CurrentPage = "(All)"
        .PivotFields("Desc. P.to op.").ClearAllFilters
        .PivotCache.Refresh
        .Update
        .RefreshTable
        .SaveData = True
    End With
    Set NuovaCache = Nothing
    'Aggiorno la base dati per la Pivot e pulisco tutti i filtri
    
    xlSheet1.Protect DrawingObjects:=True, contents:=True, Scenarios:=True, AllowUsingPivotTables:=True
    'Con le righe sopra sproteggo il foglio, aggiorno la pivot, cancello il campo con la data
    'e poi riproteggo il foglio lasciando libere le 2 celle contenenti il filtro e la cella
    'in cui inserire la data di estrazione.
    
    xlBook1.Save
    xlBook1.Close
    xlBook2.Save
    xlBook2.Close
    Set PT = Nothing
    Set xlSheet1 = Nothing
    Set xlSheet2 = Nothing
    Set xlSheet3 = Nothing
    Set xlBook1 = Nothing
    Set xlBook2 = Nothing
    'Le righe sopra salvano e chiudono tutti i fogli. Pronto per il nuovo file !
 

Attachments

  • Errore.jpg
    Errore.jpg
    68.2 KB · Views: 492
Please try this (you need to fully qualify the objects when you set them):
Code: 
Dim appExcel As Excel.Application
    Dim lUltRiga As Long
    Dim xlBook1 As Excel.Workbook
    Dim xlBook2 As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    Dim xlSheet2 As Excel.Worksheet
    Dim xlSheet3 As Excel.Worksheet
  

    Dim PT As PivotTable
    Set appExcel = CreateObject("Excel.Application")
    appExcel.DisplayAlerts = False
    appExcel.Visible = False
    'Con queste righe faccio aprire Excel per poi andare a lavorare sui singoli file
    'non facendo però visualizzare nulla di quello che accade
  
    Set xlBook1 = appExcel.Workbooks.Open(percorso & "\Output\Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx")
    Set xlBook2 = appExcel.Workbooks.Open(percorso & "\Output\EstraiSelezioneFatture.xlsx")
    Set xlSheet1 = xlBook1.Sheets("Pivot Riepilogo")
    Set xlSheet2 = xlBook1.Sheets("Base Dati")
    Set xlSheet3 = xlBook2.Sheets("EstraiSelezioneFatture")
    Set PT = xlSheet1.PivotTables("Tabella pivot1")
    xlSheet2.Activate
    ...............
Looks like you cannot format code I added appExcel to Set xlBook1 and Set xlBook2 lines......

Cheers,
Vlad
 
arnelgp said:
there is code on Dynamically changing the Data Range of pivot table here:
www.thespreadsheetguru.com

Dynamically Change Every Pivot Table Data Source Range Inside A Workbook With This VBA Macro Code

This VBA code will allow you to instantly update all your Pivot Tables inside a workbook and change the Source Data range dynamically. This macro is perfect for instances where you have an ever-changing data set that is feeding-in a bunch of Pivot Tables.
www.thespreadsheetguru.com www.thespreadsheetguru.com
Click to expand...

It's very similar to my code...maybe I used a version of this to make my 😅
 
bastanu said:
Please try this (you need to fully qualify the objects when you set them):
Code: 
Dim appExcel As Excel.Application
    Dim lUltRiga As Long
    Dim xlBook1 As Excel.Workbook
    Dim xlBook2 As Excel.Workbook
    Dim xlSheet1 As Excel.Worksheet
    Dim xlSheet2 As Excel.Worksheet
    Dim xlSheet3 As Excel.Worksheet
 

    Dim PT As PivotTable
    Set appExcel = CreateObject("Excel.Application")
    appExcel.DisplayAlerts = False
    appExcel.Visible = False
    'Con queste righe faccio aprire Excel per poi andare a lavorare sui singoli file
    'non facendo però visualizzare nulla di quello che accade
 
    Set xlBook1 = appExcel.Workbooks.Open(percorso & "\Output\Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx")
    Set xlBook2 = appExcel.Workbooks.Open(percorso & "\Output\EstraiSelezioneFatture.xlsx")
    Set xlSheet1 = xlBook1.Sheets("Pivot Riepilogo")
    Set xlSheet2 = xlBook1.Sheets("Base Dati")
    Set xlSheet3 = xlBook2.Sheets("EstraiSelezioneFatture")
    Set PT = xlSheet1.PivotTables("Tabella pivot1")
    xlSheet2.Activate
    ...............
Looks like you cannot format code I added appExcel to Set xlBook1 and Set xlBook2 lines......

Cheers,
Vlad
Click to expand...

Hi Vlad.
I used your "add", but it doesn't solved problem.

I tried to record a macro in excel, and I decided to try without the variable "NuovaCache".

Old Code:
Code: 
    Set NuovaCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=percorso & "\Output\[Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx]Base Dati!R1C1:R" & lUltRiga & "C19")
    
    With PT
        .ChangePivotCache (NuovaCache)
        .PivotCache.Refresh

New Code:
Code: 
     With PT
        .ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=percorso _
        & "\Output\[Nuovo_QC_DT-V1.3 - Fatture Scadute.xlsx]Base Dati!R1C1:R" & lUltRiga & "C19", Version:=6)
        .PivotCache.Refresh
For the moment it works.
I sent it to 2 colleagues and I'm waiting for response....
 
You must log in or register to reply here.

Similar threads

B
Pivot table refresh....or not?
Replies
8
Views
1,482
Isaac
Isaac
T
Pivot table vba failing after first success
Replies
0
Views
1,043
TimW
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom