Discussion :

[Promethee25]

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.

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:


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):


NB4: J'ai essayé de jouer sur les 3 options de tableau ci-dessous sans succès:


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 :

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
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.