3 pièce(s) jointe(s)
Problème de format non étendu sur un ListObject (exécution asynchrone?)
Bonjour,
Voici un problème auquel je ne trouve pas de solution.
A partir d'une table contenant des lignes pour tous les fournisseurs, je souhaite générer un fichier par fournisseur en filtrant cette table successivement pour chaque fournisseur (et en enlevant la colonne avec le nom du fournisseur).
Sujet très simple à la base. :roll:
Pour cela, j'ai choisi la structuration suivante:
- Feuille FullTableSheet: Tableau structuré avec les lignes pour tous les fournisseurs
- Feuille TemplateSheet: Feuille servant de modèle pour les fichiers à générer. L'idée est de la remplir successivement pour chaque fournisseur et d'en faire une copie dans un nouveau fichier Excel.
Plutôt qu'un filtre élaboré, j'ai choisi de compléter le tableau structuré de la TemplateSheet via une requête Power Query prenant en paramètre d'entrée le nom du fournisseur courant (stocké dans une case Excel).
Je pilote en VBA le changement de fournisseur dans la case Excel, le refresh de la requête PQ puis la copie de la feuille dans un nouveau classeur, tout cela dans une boucle.
Fonctionnellement, pas de souci, le contenu des fichiers est correct. Mon problème concerne la mise en forme qui n'est pas correctement étendue sur tout le tableau structuré des fichiers générés alors que la mise en forme est bien appliquée à la colonne du tableau structuré de l'onglet copié (celui de la TemplateSheet).
Il semble y avoir un problème d’exécution asynchrone des instructions VBA:
Juste après le Refresh de la requête Power Query, le format n'a pas encore été étendu à toutes les lignes au moment de la copie de l'onglet.
Insérer des instructions DoEvents, Application.Calculate / .CalculationState en boucle avec tempo ou même un Wait avec Now + 5s entre le Refresh de la connexion et la copie de la feuille ne change rien.
NB1: Lors de l'exécution du code VBA en pas à pas, cela fonctionne comme attendu (si j'attends 1 ou 2s; en revanche ça échoue si je passe trop vite du refresh à la copie avec F8).
NB2: L'exécution en arrière plan est désactivée pour la requête PQ pour justement avoir un traitement synchrone:
Pièce jointe 627689
NB3: Les MFC sont systématiquement étendues correctement à toute la table; seule la mise en forme statique échoue systématiquement sur les lignes autres que la 1ère (typ. retour à la ligne automatique, bordure, centrage):
Pièce jointe 627690
NB4: J'ai essayé de jouer sur les 3 options de tableau ci-dessous sans succès:
Pièce jointe 627691
NB5: Problème vu sur Excel 2016 et reproduit sur Excel 2019. J'avais déjà rencontré d'autres problèmes d'exécution asynchrone sur VBA en passant de 2010 à 2016 mais je ne peux pas tester mon code sur 2010 pour comparer à cause du script PQ.
Code:
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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
| Sub GenerateSupplierFiles()
'Fonction de génération des fichiers de prévisions fournisseur
'Génère un fichier par fournisseur en se basant sur l'onglet template
Dim rg As Range
Dim facility As String
Dim supplierDico As New Scripting.Dictionary
Dim folderPath As String
Dim supplierObj
'Récupération du libellé de l'établissement
facility = "505"
'Vérification du chemin d'enregistrement
folderPath = "C:\tmp\"
'Remplissage du dictionnaire des fournisseurs (occurrence unique)
For Each rg In FullTableSheet.Range("Prévisions_Fournisseur[Nom Fournisseur]")
supplierDico(rg.Value2) = ""
Next rg
'Boucle créant un fichier par fournisseur (via copie de l'onglet template)
For Each supplierObj In supplierDico.Keys
'La plage nommée "Fournisseur" est le paramètre d'entrée de la connexion STR_QUERY_SingleSupplierForecasts;
'elle doit être mise à jour avant le rafraîchissement de la connexion
TemplateSheet.Range("Fournisseur") = supplierObj
'Rafraîchissement de la connexion PQ
ThisWorkbook.Connections("Requête - Prévisions Pour Un Fournisseur").Refresh
'***************************************************
'C'est manifestement ici que le problème se passe
'***************************************************
'Copie vers un nouveau fichier
TemplateSheet.Copy
'Suppression de la connexion puis enregistrement du nouveau fichier et fermeture
With ActiveWorkbook
.Connections("Requête - Prévisions Pour Un Fournisseur").Delete
Application.DisplayAlerts = False 'On désactive temporairement les alertes pour écraser sans avertissement utilisateur le cas échéant
.SaveAs Filename:=folderPath & "Prévisions_" & VBA.Format(VBA.Now, "yyyy-mm") & "_" & supplierObj & "_" & facility & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True 'Réactivation des alertes
.Close SaveChanges:=False
End With
Next supplierObj
MsgBox "Traitement terminé: les prévisions ont été générées avec succès pour tous les fournisseurs.", vbOKOnly
End Sub |
Avez-vous une idée de la cause?
(en solution, je pourrais reconfigurer les styles par VBA mais j'aimerais éviter et surtout comprendre le problème)
Merci d'avance pour votre aide.
