Mise à jour TCD, pause, puis reprise macro

Le 02/11/2020 à 15:04

Bonjour et merci d'avance

voici ma macro qui devrait actualiser des TCD dans différentes feuilles. Mais je mets une pause suffisante pour la MAJ de TCD.

Mais j'ai un message à la 2ème MAJ

Sheets("MENU").Select
 Application.EnableEvents = False
Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable
Application.Wait (Now + TimeValue("00:00:30"))
 Application.EnableEvents = True
 Application.EnableEvents = False
Sheets("X").PivotTables("SUIVI OPERATEUR").RefreshTable ''''ICI'''' <================
Application.Wait (Now + TimeValue("00:00:30"))
 Application.EnableEvents = True
 Application.EnableEvents = False
Sheets("Y").PivotTables("SUIVI OPERATEUR").RefreshTable
Application.Wait (Now + TimeValue("00:00:30"))
 Application.EnableEvents = True
 Application.EnableEvents = False
Sheets("Z").PivotTables("SUIVI OPERATEUR").RefreshTable
 Application.EnableEvents = True
capture

J'ai l'impression que Excel suspend les MAJ TCD le temps de la pause.

Quand je clique Débogage et que je force la macro, c'est au 3ème TCD que j'ai de nouveau le message et idem au 4ème.

J'ai aussi sans :

 Application.EnableEvents = True
 Application.EnableEvents = False

Merci d'avance pour votre aide.

Le 02/11/2020 à 15:21

J'ai supprimer les lignes la feuille X à Z comme suit :

Sheets("MENU").Select
 Application.EnableEvents = False
Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable
Application.Wait (Now + TimeValue("00:00:30"))

car les 3 derniers TCD étaient construits par un copier coller de la feuille X. Donc ça devait créer un conflit.

Le 02/11/2020 à 15:57

Bonjour,

Ce ne serait pas PivotCache.Refresh à la place de pivottable.refresh ?

ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh
Le 03/11/2020 à 10:27

Bonjour Xmenpl,

merci pour ta réponse.

En fait tu peux utiliser cette fonction quand tu veux "à distance" mettre à jour un TCD dans une autre feuille "W" par exemple.

Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable

Ceci dit je pensais avoir trouvé mais non. Mon TDC est lié à une requête connexion. Les données ne se mettent à jour que lorsque ma macro est terminée. La pause dans la macro n'a aucun effet... Je reste toujours bloqué.

même une routine de sauvegarde de classeur après mise à jour, ça ne fonctionne pas.... Je garde toujours mes données non à jour. Si je stoppe ma macro alors seulement les données se mettent à jour....

Dim cellule As Range

Set cellule = Range("A1")

If Range("A1").Value = 0 Then
'Sheets("MECANIQUE").PivotTables("SUIVI OPERATEUR").RefreshTable
On Error Resume Next
ThisWorkbook.RefreshAll
Application.Wait (Now + TimeValue("00:00:20"))
cellule.Value = 1
Workbooks("POURC_HEURES_VENTILEES").Close True
Else
Sheets("MENU").Select
Le 03/11/2020 à 11:00

As tu essayé l'enregistreur de macro pour voir les lignes de codes qu'Excel créé lors de l'acutalisation du tcd ?

Le 03/11/2020 à 11:11

Oui quand je fais clic droit sur le TCD et actualiser, dans l'enregistreur il ne me prend rien... aucune fonction...

Le 03/11/2020 à 12:31

Bonjour à tous,

avec Application.Wait, excel ne fait qu'attendre sans pouvoir faire rien d'autre.

Une tentative faite en aveugle faute de pouvoir tester, peut être des ajustement à faire :

Dim majTerminee As Boolean

Sub test()
    majTerminee = False
    Sheets("MENU").Select
    Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable

    differe "X", "SUIVI OPERATEUR", TimeValue("00:00:30")
    differe "Y", "SUIVI OPERATEUR", TimeValue("00:01:00")
    differe "Z", "SUIVI OPERATEUR", TimeValue("00:01:30")

    ' si besoin attente du dernier, à tester après. Possible que ça bloque ce qui précède
'    differe "zzz", "", TimeValue("00:02:00") ' "zzz" : feuille fictive
'    Do
'        DoEvents
'    Loop Until majTerminee
End Sub

Sub differe(feuille As String, pt As String, h As Date)
    If feuille = "zzz" Then
        Application.OnTime Now + h, "termine"
    Else
        Application.OnTime Now + h, "'majTable ' & feuille &"", "" & pt'"
    End If
End Sub

Sub majTable(feuille As String, pt As String, h As Date)
    Sheets(feuille).PivotTables(pt).RefreshTable
End Sub

Sub termine()
    majTerminee = True
End Sub

eric

Le 03/11/2020 à 14:39

Merci ERiiiC pour ton aide,

J'ai tester à l'ouverture de mon classeur, comme-ci, mais ça ne met pas à jour mes feuilles... .

Private Sub Workbook_Open()
Dim majTerminee As Boolean

    majTerminee = False
    Sheets("MENU").Select
    Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable

    differe "X", "SUIVI OPERATEUR", TimeValue("00:00:30")
    differe "Y", "SUIVI OPERATEUR", TimeValue("00:01:00")
    differe "Z", "SUIVI OPERATEUR", TimeValue("00:01:30")

    ' si besoin attente du dernier, à tester après. Possible que ça bloque ce qui précède
'    differe "zzz", "", TimeValue("00:02:00") ' "zzz" : feuille fictive
'    Do
'        DoEvents
'    Loop Until majTerminee
' Je continue ensuite avec des macros activate dans les feuilles suivantes
    Sheets("W").Select
    Sheets("X").Select
    Sheets("Y").Select
    Sheets("Z").Select
    Sheets("MENU").Select

Workbooks("POURC_HEURES_VENTILEES").Close True

End Sub

Sub differe(feuille As String, pt As String, h As Date)
    If feuille = "zzz" Then
        Application.OnTime Now + h, "termine"
    Else
        Application.OnTime Now + h, "'majTable ' & feuille &"", "" & pt'"
    End If
End Sub

Sub majTable(feuille As String, pt As String, h As Date)
    Sheets(feuille).PivotTables(pt).RefreshTable
End Sub

Sub termine()
    majTerminee = True
End Sub

Tant qu'on est dans la boucle de la macro, la mise à jour semble impossible. Tous les "petits" TCD eux se mettent à jour et celui-ci "SUIVI OPERATEUR" relié directement à une requête connexion ne veut pas se mettre à jour en période de macro. A ne rien y comprendre...

Le 03/11/2020 à 16:07

Supprime les lignes que tu as ajouté pour tester correctement.
Et si ça fonctionne (tes TCD actualisés après 2min) tu actives le 'differe "zzz" et le do loop et tu retestes. Si toujours ok tu pourras ajouter tes autres lignes après.
eric

Le 04/11/2020 à 08:18

Merci Eriiic,

J'ai fait comme tu m'as dit

Private Sub Workbook_Open()
Dim majTerminee As Boolean

    majTerminee = False
'    Sheets("MENU").Select
    Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable
    differe "X", "SUIVI OPERATEUR", TimeValue("00:00:30")
    differe "Y", "SUIVI OPERATEUR", TimeValue("00:01:00")
    differe "Z", "SUIVI OPERATEUR", TimeValue("00:01:30")

    ' si besoin attente du dernier, à tester après. Possible que ça bloque ce qui précède
    differe "zzz", "", TimeValue("00:02:00") ' "zzz" : feuille fictive
    Do
        DoEvents
    Loop Until majTerminee
'    Sheets("W").Select
'    Sheets("X").Select
'    Sheets("Y").Select
'    Sheets("Z").Select
'   Sheets("MENU").Select

'Workbooks("POURC_HEURES_VENTILEES").Close True

End Sub

Sub differe(feuille As String, pt As String, h As Date)
    If feuille = "zzz" Then
        Application.OnTime Now + h, "termine"
    Else
        Application.OnTime Now + h, "'majTable ' & feuille &"", "" & pt'"
    End If
End Sub

Sub majTable(feuille As String, pt As String, h As Date)
    Sheets(feuille).PivotTables(pt).RefreshTable
End Sub

Sub termine()
    majTerminee = True
End Sub

Et voici les résultats

capture

OK x 3

capture1 capture2

Cette dernière capture est toujours visible au bout de 6 min et mes pages W, X, Y, Z ne sont pas mises à jour.

Le 04/11/2020 à 08:45

Bonjour,

non, pas comme je t'ai dit.
A chaque fois je te disais de tester en laissant

'    differe "zzz", "", TimeValue("00:02:00") ' "zzz" : feuille fictive
'    Do
'        DoEvents
'    Loop Until majTerminee

en commentaire et de refaire un test en les activant que si le 1er test était ok
En précisant que cette partie pouvait bloquer le début.

Es-tu sûr que 30s soit suffisant ? Il faut que tu sois sûr à 100% que la précédente est finie avant de lancer la suivante
Et pourquoi masquer une partie du message ?

Sinon que donne :

Private Sub majTCD
    ThisWorkbook.RefreshAll
End Sub

Tu pourrais séparer les opérations et lancer manuellement la suite après la mise à jour totale.

Le 04/11/2020 à 10:08

On est tout près de la solution ! Merci à toi Eriiic pour ta contribution.

Le masquage est juste le chemin du fichier qui me semblait sans intérêt à partager !

Voici la dernière mouture :

Private Sub Workbook_Open()
Dim majTerminee As Boolean

    majTerminee = False
    Sheets("MENU").Select
    Sheets("W").PivotTables("SUIVI OPERATEUR").RefreshTable
    differe "X", "SUIVI OPERATEUR", TimeValue("00:01:00")
    differe "Y", "SUIVI OPERATEUR", TimeValue("00:02:00")
    differe "Z", "SUIVI OPERATEUR", TimeValue("00:03:00")

    ' si besoin attente du dernier, à tester après. Possible que ça bloque ce qui précède
'    differe "zzz", "", TimeValue("00:02:00") ' "zzz" : feuille fictive
'    Do
'        DoEvents
'    Loop Until majTerminee

End Sub

Sub differe(feuille As String, pt As String, h As Date)
    If feuille = "zzz" Then
        Application.OnTime Now + h, "termine"
    Else
        Application.OnTime Now + h, "'majTable ' & feuille &"", "" & pt'"
    End If
End Sub

Sub majTable(feuille As String, pt As String, h As Date)
    Sheets(feuille).PivotTables(pt).RefreshTable
End Sub

Sub termine()
    majTerminee = True
End Sub

Private Sub majTCD()
    ThisWorkbook.RefreshAll

End Sub

Si je me ballade dans les feuilles W,X,Y,Z j'ai bien les mises à jour.et ceci 10 s après l'ouverture du fichier !

Mais au terme d'1 mn j'ai toujours ce message qui apparaît 3 x à intervalle d'1 mn.

capture
Le 04/11/2020 à 11:14

majTCD() était à tester indépendamment du reste, en solution de secours. En l'appelant manuellement depuis un bouton par exemple (enlever le Private)

Teste avec seulement ça dans ThisWorkbook :

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
End Sub

Par ailleurs Private Sub Worksheet_Change n'a rien à faire dans ThisWorkbook, c'est l'événement feuille.
Dans ThisWorkbook il devient Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Il faut te servir des listes déroulantes en haut pour choisir un événement, il se déclarera tout seul correctement.

Si le test au-dessus a été concluant on laisse tomber la suite devenue inutile...

Si je me ballade dans les feuilles W,X,Y,Z j'ai bien les mises à jour.et ceci 10 s après l'ouverture du fichier !

Mais au terme d'1 mn j'ai toujours ce message qui apparaît 3 x à intervalle d'1 mn.

Ceci laisse plutôt penser que la maj se fait toute seule à l'ouverture et non par les macros?!?
Tu devrais retenter sans macro.

Ceci dit les 3 messages d'erreur restent anormaux.
Mets les sub differe(), majTable() et termine() dans un module Standard
eric

Le 04/11/2020 à 12:19

En testant ceci

Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
DoEvents
'Application.Wait (Now + TimeValue("00:01:00"))
ActiveWorkbook.Save
Application.Quit
End Sub

J'ai çà qu'Excel ne sait pas gérer... à la ligne

ActiveWorkbook.Save
capture3

Ca semblerait si simple avec une pause qui n'affecte pas une MAJ complète...

Le 04/11/2020 à 13:43

Pffff, tu ne dis même pas si .RefreshAll a fonctionné correctement...

Et bien diffère la fermeture selon le même principe pour laisser excel travailler tranquille :

Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:02:00"), "fermer"
End Sub

Sub fermer()
    ThisWorkbook.Save
    Application.Quit
End Sub

J'ai mis ta fermeture telle que tu l'as faite.
J'ai quand même remplacé ActiveWorkbook par ThisWorkbook. Ca serait bien que tu prennes le temps d'apprendre les rudiments avant de te lancer dans VBA.
Je n'aime pas trop les suicides commandés tels que Application.Quit. Là tu fermes tout excel même si l'utilisateur travaillait sur d'autres fichiers.
Si pb avec fermer() démarre un nouveau topic avec un titre en adéquation.
eric

Le 04/11/2020 à 16:29

Eriiic,

J'ai essayé

ThisWorkbook.RefreshAll

Mais ça ne marche pas. J'ai dû utiliser

ActiveWorkbook.RefreshAll

La conclusion que j'ai faite, résume bien la grosse lacune d'excel. Tu m'as apporté une aide précieuse pour en arriver à cette conclusion. Bien sûr que cette sortie est un suicide ! Mais c'est en apprenant qu'on s'améliore et ce "suicide" était calculé car il n'y a que moi qui me sers de fichier.

Tant que je fais des étapes simples comme actualiser un TCD, ça fonctionne. Mais si j'enchaîne dans le même code une suite, c'est en sortant de celle-ci que ça se met à jour. Et c'est juste pas possible. Je voulais mettre en place une planification du fichier ➕ MAJ TCD ➕ envoi par mail les feuilles W, X, Y, Z. (les 2 dernières étapes dans le même code). Le fichier devait s'exécuter en mon absence.... Mais pour l'instant ça n'est pas possible car Excel semble ne pas gérer mon besoin.

Le 23/11/2020 à 14:19

Finalement j'ai trouvé !

Comment Fermer un classeur après un délai d'inactivité Excel vba - YouTube (google.fr)

Avec, au lieu de fermer, j'exécute mon code.

Rechercher des sujets similaires à "mise jour tcd pause puis reprise macro"