Formule actualisation tableau simple
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 :
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...
... était un TCD.
Pour cela j'ai écrit ces fonctions sur la feuille où se situe la requête :
Code : | Sélectionner tout - Visualiser dans une fenêtre à part |
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 :
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.
Bonjour,
Oui, en effet, la propriété porte sur les TCD.
Alors je suis pas sûr que la proposition suivante marche, ne connaissant pas le fonctionnement des requêtes, mais vous pouvez-essayer (dans le module de la feuille "Feuil2") :
private sub worksheet_change(byval target as range)
if not intersect(target, range("Rondes")) is nothing then
Range("M1").value = Now
end if
end sub
A chaque modification de la range, M1 prend la valeur maintenant. Vous pouvez en changer le format sur excel directement.
Il faut bien nommer la range en question (même nom que le tableau structuré ciblé).
Cdlt,
Bonjour à tous
Multipost ici et ailleurs
https://forum.excel-pratique.com/excel/formule-actualisation-tableau-simple-146567#p901836
Je pense qu'il faut chercher la réponse sur un forum sharepoint
Ni Listobject.QueryTable, ni Workbook.Connections ne semblent disposer de cette propriété
3GB,
J'ai essayé en appelant la private sub avec une sub test :
Public Sub test()
Call worksheet_change(Range("Rondes__2"))
End Sub
ça me renvoie la date de l'instant où j'exécute la sub, et non la date d'actualisation de la requête, ou de modification de celle-ci (actualisation =/= modification par ailleurs)
Bonjour,
Une proposition à étudier.
C'est à dire à tester et à adapter au besoin (pas habitué aux modules de classe !).
Le fichier est un exemple avec des cours de devises et une actualisation toutes les 5 minutes.
Cdlt.
'Module de classe
Option Explicit
Public WithEvents MyQuery As QueryTable
Public lo As ListObject
Dim rCell As Range
Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
If Success Then rCell.Offset(, 1).Value = "Succès"
End Sub
Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
Set lo = ThisWorkbook.Worksheets("Actualisation").ListObjects("Actualisations")
With lo
If .InsertRowRange Is Nothing Then
Set rCell = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
Else
Set rCell = .InsertRowRange.Cells(1)
End If
End With
rCell.Value = Format(VBA.Now, "yyyy-MM-dd hh:mm")
End Sub
'ThisWorkbook
Option Explicit
Private Sub Workbook_Open()
Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets("Actualisation").ListObjects("Actualisations")
If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
Call InitializeQueries
End Sub
'Module standard
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
Bonjour Jean-Eric,
J'ai pu tester ton fichier, c'est à noter qu'il fonctionne bien lors de l'actualisation toutes les 5 minutes, mais si on actualiser manuellement la requête, ce n'est pas pris en compte.
En tout cas merci pour ta réponse, je vais tester avec ma source,
Re,
Pour ma part :
Ruban, Données et Actualiser tout et le tableau Actualisations se met à jour
Sélection d'une cellule dans le tableau des devises et clic-droit, Actualiser, fonctionne également.
Cdlt.
Re,
J'ai essayé avec ma source de données,
Je n'ai rien qui s'écrit dans le tableau,
Voici mon code adapté, mon tableau de données "Rondes__2" est sur la "Feuil1" et le tableau "Actualisations" sur la "Feuil2",
Voici mon code :
'Module de classe'
Option Explicit
Public WithEvents MyQuery As QueryTable
Public lo As ListObject
Dim rCell As Range
Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
If Success Then rCell.Offset(, 1).Value = "Succès"
End Sub
Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
Set lo = ThisWorkbook.Worksheets("Feuil2").ListObjects("Actualisations")
With lo
If .InsertRowRange Is Nothing Then
Set rCell = .HeaderRowRange.Cells(1).Offset(.ListRows.Count + 1)
Else
Set rCell = .InsertRowRange.Cells(1)
End If
End With
rCell.Value = Format(VBA.Now, "yyyy-MM-dd hh:mm")
End Sub
'module'
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
'workbook'
Option Explicit
Private Sub Workbook_Open()
Dim lo As ListObject
Set lo = ThisWorkbook.Worksheets("Feuil2").ListObjects("Actualisations")
If Not lo.DataBodyRange Is Nothing Then lo.DataBodyRange.Delete
Call InitializeQueries
End Sub
Voici mon fichier :
Et sinon ton fichier marche bien, c'est juste que je faisais l'actualisation depuis l'onglet requêtes et connexion, au lieu d'utiliser "Actualiser tout".
C'est bon en reprenant directement à partir de ton fichier, ça fonctionne bien,
Merci pour ton aide précieuse,