Discussion :

[mattgui]

Bonjour à tous,

Dans un premier temps, je tiens à vous remercier chacun de vous pour votre temps que vous accordez à aider les gens dans le besoin. C'est mon premier post ici, mais de nombreux sujets que j'ai consulté précédemment m'ont permis de me sortir de situations bien embêtantes ...

Mais si je poste ici c'est que j'ai un problème persistant auquel je n'ai pas trouvé de solutions, même après des recherches intensives sur le net, en français comme en anglais.

Je m'explique :

Je travaille sur un outil destiné à mon entreprise et qui servira de support de vente pour les commerciaux. Il s'agit en fait d'un outil Excel générant une courbe à partir de données issues de plusieurs essais et qui sont stockées sur une base de données Access.
J'ai crée cette base de données Access ainsi qu'une requête servant à renvoyer les données selon deux paramètres. Là n'est pas la question puisque tout fonctionne bien.

J'ai crée une connexion entre ma base Access et mon fichier Excel me permettant de lancer ma requête SQL sous Access et en prenant des paramètres de mon fichier Excel. Cette connexion me charge un tableau stocké sur une feuille de mon fichier Excel.

J'ai ensuite crée une procédure générale qui actualise la requête avec un ActiveWorkbook.RefreshAll auquel je fais suivre des appels de procédures diverses servant à l'exploitation statistique
Le problème est qu'en utilisant ce moyen pour actualiser mes données, les procédures qui suivent n'attendent pas la fin de la requête et s'exécutent donc avec les mauvaises données.

J'ai essayé plusieurs méthodes pour tenter d'y remédier : utiliser un DoEvents, utiliser une boucle de temporisation, utiliser un Wait Now, ... tout ce que j'ai trouvé sur le net !

La solution consistant à désactiver l'actualisation en arrière plan semble marcher mais lorsque je l'utilise, j'ai un message d'erreur : "Téléchargement non effectué". Les données ne sont pas mises à jour.

Voilà, si quelqu'un a une solution à me conseiller, je suis preneur ...

J'espère avoir été assez précis dans mes explications et n'hésitez pas à me demander si vous avez besoin de plus.

Merci d'avance pour votre aide.

[mattgui]

Bonjour,

Je n'ai toujours pas trouvé de solution à mon problème, même en continuant mes recherches ...

Du coup je tente de contourner le problème en créant une diversion de l'utilisateur type progressbar le temps que l'actualisation se fasse. Mais même en utilisant cette solution, je rencontre des problèmes d'actualisation.

Bref, un coup de pouce ou un aiguillage ne serais pas de refus

[Invité]

Bonjour,

doevens actualise les événements ce qu’apparemment tu ne veux surtout pas!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub test()
Application.EnableEvents = False: Application.Calculation = xlManual: Application.ScreenUpdating = False
'Traitent
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic: Application.ScreenUpdating = True
Application.Calculate
End Sub

[mattgui]

Bonjour,

doevens actualise les événements ce qu’apparemment tu ne veux surtout pas!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub test()
Application.EnableEvents = False: Application.Calculation = xlManual: Application.ScreenUpdating = False
'Traitent
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic: Application.ScreenUpdating = True
Application.Calculate
End Sub

Bonjour,

Tout d'abord merci pour ta réponse. En fait le problème, je pense, c'est mon actualisation en arrière plan. Car lorsque j'utilise ton code, cela ne change absolument rien, ma requête s'effectue en arrière plan et le reste de ma procédure s'effectue, toujours avec les mauvaise données. Or, lorsque je désactive l'actualisation en arrière plan via le panel de configuration de la requête, elle ne veut plus s'exécuter !

J'avoue être un peu perdu et ne pas trouver de solution pour créer une sorte de "temporisation" le temps que ma requête finisse ce qu'elle a à faire.

[mattgui]

C'est encore moi !

Après recherche avec des mots clés plus précis, j'ai trouvé un début de solution !

J'ai en fait ajouter un timer de 10 secondes dans l'exécution de ma macro avant de lancer la procédure de traitement. C'est un peu du bidouillage, étant donné que je ne peux pas savoir à l'avance combien de temps mon actualisation va durer, mais pour le moment, cela marche !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Sub Validation()
 
    ActiveWorkbook.RefreshAll
    Range("B35").Value = Sheets("Paramètres").Range("A2").Value
    Sheets("Editeur").Range("G35").Value = Sheets("Paramètres").Range("B2").Value
 
    Application.OnTime Now + TimeValue("00:00:10"), "Générer"
 
End Sub

Bien que ce soit déjà satisfaisant, j'aimerai tout de même pouvoir attendre le temps exact de mon actualisation avant de lancer le sub "Générer". Quelqu'un aurait une idée ?

[Marc-L]

Bonjour,

et en vérifiant la propriété   CalculationState   ?   (cf aide VBA interne …)

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

[78chris]

Bonjour

A une époque où j'avais rencontré des problèmes de ce type entre plusieurs applis, la solution, un peu bourrin, que j'avais trouvée, était de faire créer un fichier par l’application tierce en fin de traitement et l'appli appelante bouclait tant que le fichier n'avait pas été trouvé, puis l'effacait et poursuivait...

[Phil Free]

Bonjour,

A priori pour excel les requêtes de données lancées vers access doivent être des requêtes de type Querytable.
L'idées serait donc d'abandonner le refreshall et d'utiliser en remplacement une boucle pour mettre à jour toutes tes tables l'une après l'autre ce qui te permettrai d'utiliser l'evenement AfterRefresh de la classe Excel.QueryTable. En effet cet evenement renvois une information de requête terminée.

Dans le code ThisWorkBook :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Private Sub Workbook_Activate()
    Call XlAppli.InitQueryEvent(Me)
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    XlAppli.EmptyQtClass
End Sub
 
Private Sub Workbook_Deactivate()
    XlAppli.EmptyQtClass
End Sub
 
Private Sub Workbook_Open()
    Set XlAppli.Xl = Excel.Application 'on charge la classe
    Call XlAppli.InitQueryEvent(Me) 'on recupere toutes les querytables afin de les associées à leur classe d'evenements
End Sub

En partie haute d'un module tu colles le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Option Explicit
 
 'optionnel permettra par la suite de redonner le focus à excel en cas de demande éventuelle de mot de passe en début de requête 
Declare Function SetForegroundWindow Lib "User32" (ByVal hwnd As Long) As Long
 
Public RefreshISDone As Boolean
Public XlAppli As New XlClass

Tu ajoutes à ton projet un module de classe nommé XlClass
Et tu lui colles le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Option Explicit
 
Public WithEvents Xl As Excel.Application
Dim Tableqt() As ClsModQT
 
Public Sub InitQueryEvent(Wkb As Workbook)
    'ici on associ les querytables à leur classe d'evenements
 
    'on verifi que c'est bien le type d'objet attendu. Ici un workbook
    If TypeOf Wkb Is Workbook Then
        Dim Requete As QueryTable
        Dim Feuille As Worksheet
        Dim i As Integer
        i = 1
        For Each Feuille In Wkb.Worksheets
            If Feuille.QueryTables.Count > 0 Then
                If i = 1 Then
                    ReDim Preserve Tableqt(Feuille.QueryTables.Count)
                Else
                    ReDim Preserve Tableqt(UBound(Tableqt) + Feuille.QueryTables.Count)
                End If
                For Each Requete In Feuille.QueryTables
                    Set Tableqt(i) = New ClsModQT
                    Set Tableqt(i).qtQueryTable = Requete
                    i = i + 1
                Next
            End If
        Next
    End If
End Sub
'Permet de vider la classe
Public Sub EmptyQtClass()
    Dim j As Integer
    On Error Resume Next
    For j = 1 To UBound(Tableqt)
        Set Tableqt(j) = Nothing
    Next
End Sub

Tu ajoutes un 2ieme module de classe que tu nommes ClsModQT
puis tu lui colles le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
Option Explicit
 
Public WithEvents qtQueryTable As QueryTable
 
Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
    If Success = True Then RefreshISDone = Success
    Debug.Print qtQueryTable.Parent.Name & " refresh done ! "
End Sub
 
Private Sub qtQueryTable_BeforeRefresh(Cancel As Boolean)
    Debug.Print "Query tables On " & qtQueryTable.Parent.Name & " refreshing in progress..."
End Sub

Voila la méthode n'est pas simple à mettre en place mais fonctionne de mon coté
Note que je n'ai pas de base access donc pas moyen de créer des requêtes vers une base access donc j'ai fait mes testes avec des requêtes vers des tables dans des pages web

/!\ En générale quand on utilise des modules de classe il est préférable de lancer les macros de test depuis excel et pas depuis l’éditeur VBA
/!\ toutes modifications du code dans un module de class doit être suivi d'un rechargement de la classe !!! cela peut être fait en exécutant "Workbook_Open" du module ThisWorkbook et ce depuis l’éditeur VBA. Exception à la règle précédente
/!\ tout ajout de table de requêtes dans le classeur doit être suivit d'un rechargement de la classe afin d'être pris en compte par la classe d'evenement. Soit comme expliqué précédemment ou par une fermeture du classeur puis une réouverture.

Maintenant un code de demo à mettre dans le module ou ce trouve, entre autre, la déclaration de la variable "Public RefreshISDone As Boolean" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
 
Sub DemoQuerytable()
'Ce code mettra à jour toutes les tables de requêtes de ce classeur
Dim Requete As QueryTable
Dim Feuille As Worksheet
Debug.Print "Debut demo..."
RefreshISDone = False
For Each Feuille In ThisWorkbook.Worksheets
    For Each Requete In Feuille.QueryTables
        RefreshISDone = False
        Requete.Refresh BackgroundQuery:=False
        SetForegroundWindow Application.hwnd 'si demande de mot de passe excel perd le focus donc on lui redonne 
        Do
            DoEvents
            If RefreshISDone = True Then Exit Do 'c'est ici que notre classe d'evenement de fin de mise à jour des tables intervient
        Loop
        Msgbox
    Next
Next
'ici le code qui doit s’exécuter après mise à jour des tables. Pour la demo j'ai utilisé une boite de message.
MsgBox "Toutes les tables ont été mises à jour.", vbInformation + vbOKOnly
End Sub

Voila, bon courage pour la mise en place

Philippe

[mattgui]

Bonjour à tous,

Après un mois d'absence car j'étais sur d'autres projets, je reprend mon travail sur cet outil qui m'en fait voir de toutes les couleurs ^^'.

J'ai essayé vos différentes solutions :

Bonjour,

et en vérifiant la propriété   CalculationState   ?   (cf aide VBA interne …)

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

J'ai essayé de créer une boucle qui vérifie l'état CalculationState mais à priori, il prend en compte que du code VBA est en exécution et donc je me retrouve avec une boucle infinie ...

En ce qui concerne la solution de Philippe, j'ai essayé de la mettre en place mais en vain : la requête ne s'actualise pas. J'ai essayé de chercher par moi même mais comme c'est la première fois que j'ai affaire à des modules de classe, je ne comprend pas tout le code (et donc bidouiller sur ce qu'on ne comprend pas, ce n'est pas évident ).

N'existerait-il pas une autre solution que de passer par l'actualisation individuelle de la Querytable ?

A vous lire,

Matt

[Phil Free]

Bonjour,

Vas voir ici , j'ai posté une solution qui prend en compte toutes les querytables du fichier. Cela fonctionne donc avec refreshall. Du coup ce sera plus simple pour toi car tu n'as plus à associer la classe d’événement à tes querytables.
Si tu as des difficultés à mettre en place la solution que je te propose n’hésite pas tenté d'expliquer ces difficultés.

[mattgui]

Bonjour,

Vas voir ici , j'ai posté une solution qui prend en compte toutes les querytables du fichier. Cela fonctionne donc avec refreshall. Du coup ce sera plus simple pour toi car tu n'as plus à associer la classe d’événement à tes querytables.
Si tu as des difficultés à mettre en place la solution que je te propose n’hésite pas tenté d'expliquer ces difficultés.

Salut Philippe,

Tout d'abord, merci de ton intérêt pour mon problème ! Désolé de ne pas avoir répondu plus tôt à mon post, mais comme je l'ai dit, j'étais sur des projets différents .

J'ai essayé ta solution avec le refreshall : la requête se lance mais l'actualisation en arrière plan est infinie. Je n'ai dans les logs que la ligne "Debut test refresh all...". De mes maigres connaissances, j'ai l'impression que la macro ne détecte pas la fin de la requête.

En fait, je pense que ma requête n'est pas considérée comme une Querytable (ce qui est vérifié par le fait que je n'ai jamais réussi à y faire référence en utilisant Activesheet.Querytable(1) ). Il s'agit pourtant bien d'une requête access que j'ai ajouté à l'aide de l'éditeur de requêtes dans l'onglet "données" (pour être honnête, je ne suis pas un pro en VBA et je ne sais pas si cette méthode est la meilleure). Ma grande question est : est ce que ma requête est bien reconnue comme étant un objet Querytable ? Si oui, pourquoi ta solution ne marche pas ?

Merci d'avance

Matt

[mattgui]

Bonjour à tous,

N'ayant pas réussi à mettre en place la solution de Phil Free, j'ai eu une idée inspirée de la méthode 78chris consistant à effectuer un rebouclage tant qu'un élément propre à la requête n'est pas présent. Ici, j'ai pensé à une cellule que je viderais à chaque fois avec l'exécution de la requête et que je remplirais uniquement à partir de la requête (en dernière étape). Du coup, si je fais un rebouclage de ma macro avec une boucle while je suis censé, en théorie, pouvoir attendre que cette cellule soit remplie par la requête et donc "capter" le moment où elle se termine.

Le problème c'est que j'ai essayé mais bizarrement, j'ai une boucle infinie, ce n'est jamais bouclé et excel plante, évidemment. J'utilise ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Validation()
 
    Sheets("Editeur").Range("B35").Value = Sheets("Paramètres").Range("A2").Value
    Sheets("Editeur").Range("G35").Value = Sheets("Paramètres").Range("B2").Value
    Sheets("Données").Range("A2").Value = 0
    ActiveWorkbook.RefreshAll
    Do While Sheets("Données").Range("A2").Value = 0
    Loop
    Generer
 
End Sub
 
Sub Generer()
    Call Regression
    Call Actualisation_graph
 
End Sub

Quelqu'un aurait-il une idée sur la forme de la boucle à utiliser pour qu'elle ne soit pas infinie ? Est-ce que ma méthode est optimisée, viable ou absolument pas ?

Merci d'avance,

Matt

[mattgui]

Je reviens à la charge pour vous dire que j'ai trouvé une solution fonctionnelle pour mon problème !

J'ai en fait utilisé Worksheet_Change sur la feuille où s'actualise mes données en pointant sur une seule cellule. Comme il est totalement impossible du point de vue de mes données que cette cellule soit identique à chaque exécution de ma requête, cela me permettra de savoir à quel moment m'a requête est finie (correspondant donc au moment où ma cellule change de valeur !).

Merci à ceux qui m'ont aidé, je passe le sujet en résolu.