Formule actualisation tableau simple

Version imprimable

3 pièce(s) jointe(s)

Bonjour à tous,

Je fais face à une problématique depuis une semaine, j'ai une requête vers une liste SharePoint, que j'affiche sous forme d'un tableau sur une feuille de calcul avec Power Query.

J'aimerais pouvoir récupérer la date d'actualisation de celui-ci et l'afficher dans une cellule de la feuille.

J'ai mis en place une actualisation automatique à l'aide des paramètre d'excel (en arrière-plan, toutes les 15 minutes, et à l'ouverture du fichier), je peux actualiser manuellement ici :

Pièce jointe 579687

Pour récupérer la date de dernière actualisation je suis d'abord parti du principe que le tableau généré par la requête...
Pièce jointe 579688

... était un TCD.

Pour cela j'ai écrit ces fonctions sur la feuille où se situe la requête :
Code:

1 2 3 4 5 6 7 8 Private Sub RecupDateActua(ByVal Target As PivotTable) dateString = Format(Target.RefreshDate, "dd/mm/yyyy hh:mm:ss") Range("M1") = dateString End Sub Public Sub test() Call RecupDateActua(Sheets("Feuil2").PivotTables("Rondes")) End Sub
Le problème c'est que j'ai une erreur à l'exécution de la Macro :

Pièce jointe 579689

Cela est sûrement du au fait que cette fonction concerne uniquement les TCD(PivotTable) et que mon tableau n'en est pas un.

J'ai donc essayé de reproduire ce tableau en créant un TCD, sans succès puisque ce n'est pas la fonction première du TCD,

Donc 2 questions :

- Comment adapter la macro pour qu'elle s'applique à un tableau simple ou "range" de données ?

ou alors

- Comment créer un TCD qui s'affiche de la même manière qu'un tableau simple ?

Merci.

20/09/2020, 21h32
mfoxy
Hello,

Perso, je ne ferais pas de mise à jour automatiquement toutes les X min.
Je préfère rester maître de mon fichier et ne pas le laisser se "modifier" sans que je lui demande.

Pour ce faire, en ayant un tableau structuré ( issus d'une requête power query) , nommé t_Table1.
Je mettrais cette ligne de code pour l'actualisation.
Code:

1 2 Range("t_Table1").Listobject. QueryTable.Refresh BackgroundQuery:=False
Pour ce qui est de refléter la date et heure de la dernière Maj, sur une feuille : j'ajouterais une cellule nommée cMaj à laquelle, j'ajouterais la date et heure à la fin de la mise à jour.

Code:

Range("cMaj").Value = Now()

Un petite Sub pour lancer le tout, et un petit bouton pour faire le refresh.

Bat,
MFoxy
20/09/2020, 21h45
js777

Salut mxfoxy,

Merci pour ta réponse le problème avec ta ligne...

Code:

Range("cMaj").Value = Now()

...C'est qu'elle ne donne pas la date d'actualisation des données dans la requêtes mais simplement la date de l'instant T où tu l'exécutes.
20/09/2020, 21h53
mfoxy

Re,

Je dois m'être mal exprimé.

Dans mon code j'écris les deux lignes de code, une en dessous de l'autre.
ainsi 1) refresh de la table, 2) insertion de la date et heure au moment de la fin du refresh

Ne laissant pas la main à Excel pour effectuer des refresh automatique, je ne saurais te dire s'il existe une méthode pour récupérer nativement la date et heure du dernier refresh d'une table ( power query)
20/09/2020, 22h08
js777

1 pièce(s) jointe(s)

Ok, je vois où tu veux en venir,

J'ai essayé d'adapter ton code à mes besoin (j'ai déjà une actualisation automatique en place et souhaite la conserver) en mettant un if,

J'ai malheureusement cette erreur de syntaxe :
Pièce jointe 579706

Update,

Voici la façon de faire que j'ai décidé d'adopter et qui me permet de récupérer la date à chaque actualisation des données de la source,

(Le code ci-dessous permet dans mon cas d'écrire la date de dernière actualisation dans la cellule R4 de la feuille Suivi journalier air)

Merci à Jean-Eric

Dans ThisWorkbook :

Option Explicit

Code:

1
2
3
Private Sub Workbook_Open()
    Call InitializeQueries
End Sub

Dans un module standard :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Option Explicit
 
Dim colQueries As New Collection
 
Sub InitializeQueries()
Dim clsQ As clsQuery
Dim WS As Worksheet
Dim QT As QueryTable
Dim lo As ListObject
    For Each WS In ThisWorkbook.Worksheets
        For Each QT In WS.QueryTables
            Set clsQ = New clsQuery
            Set clsQ.MyQuery = QT
            colQueries.Add clsQ
        Next QT
        On Error Resume Next
        For Each lo In WS.ListObjects
            Set QT = lo.QueryTable
            Set clsQ = New clsQuery
            Set clsQ.MyQuery = QT
            colQueries.Add clsQ
        Next lo
    Next WS
End Sub

Dans un module de classe (clsQuery) :

Code:

1
2
3
4
5
6
7
Option Explicit
 
Public WithEvents MyQuery As QueryTable
 
Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
    If Success Then Sheets("Suivi journalier air").Range("R4").Value = Format(VBA.Now, "yyyy-MM-dd hh:mm")
End Sub