XL 2019 Somme des cellules de plusieurs fichiers Excel

[nanie]

Bonjour,

Je dispose de plus de 60 fichiers excel identiques. Chaque fichier contient 25 feuilles.

Je voudrais créer un fichier global , qui a la même structure que les 60 autres. Ce fichier sera la somme des autres fichiers.

Comment proceder ? Je ne connais pas du tout VBA ou query ou les tableaux croisés dynamiques

En vous remerciant vivement

Merci

 

[chris]

RE

Dans l'onglet feuil5 tu vois les données synthétisées. CE qui peut aider à voir où se situe l'anomalie

Sinon poste, en MP éventuellement, un exemple posant problème

Si le fichier Recap est dans le même dossier, son nom doit contenir Synthese (casse et accents respectés) pour qu'il ne s'auto traite pas

 

[nanie]

OK
Mp envoyé

 

[kiki29]

Salut, n'ayant pas de PQ , j'ai commencé une moulinette pour évaluer le temps nécessaire, eh bien reste avec chris car à priori cette moulinette prendrait dans les 5 minutes minimum.

 

[nanie]

@kiki29
Merci pour cette intervention...
N hesite pas à faire avancer le pb avec des post aussi pertinents ...

 

[kiki29]

Re, eh bien je ne poursuis pas cette moulinette et laisse tomber. Atchao !

 

[job75]

Bonjour ninie, chris, Modeste geedee, kiki29, le forum,

Avec 60 fichiers tests identiques consolidés cette macro s'exécute chez moi en 39 secondes :

Sub Consolider()
Dim t, chemin$, fichier$, feuille$, P As Range, c As Range, n%
t = Timer
chemin = ThisWorkbook.Path & "\" 'dossier à adapter
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
feuille = "ANNEE"
Set P = ActiveSheet.[C12:E13,H12:K13,C19:E20,H19:K20,C26:E27,C33:H34,C41:F42,C48:D49,H48:I49,C61:D62,G61:H62,C68:D69,G68:H69,C75:D76,G75:H76,C82:C83,C89:D90,G89:H90,C96:D97,G96:H97,C103:D104]
Application.ScreenUpdating = False
P = 0
While fichier <> ""
    For Each c In P
        c = "=" & c & "+'" & chemin & "[" & fichier & "]" & feuille & "'!" & c.Address 'formule de liaison
        c = c.Value 'supprime la formule
    Next
    fichier = Dir 'fichier suivant
    n = n + 1
Wend
Application.ScreenUpdating = True
MsgBox n & " fichiers consolidés en " & Format(Timer - t, "0.00 \s")
End Sub

Placez-la dans le VBA du fichier Récap.xlsm qui doit être dans le même dossier que les fichiers tests.

A+

 

[job75]

En utilisant les Areas et une formule matricielle c'est beaucoup plus rapide :

Sub Consolider()
Dim t, chemin$, fichier$, feuille$, P As Range, a As Range, n%
t = Timer
chemin = ThisWorkbook.Path & "\" 'dossier à adapter
fichier = Dir(chemin & "*.xlsx") '1er fichier du dossier
feuille = "ANNEE"
Set P = ActiveSheet.[C12:E13,H12:K13,C19:E20,H19:K20,C26:E27,C33:H34,C41:F42,C48:D49,H48:I49,C61:D62,G61:H62,C68:D69,G68:H69,C75:D76,G75:H76,C82:C83,C89:D90,G89:H90,C96:D97,G96:H97,C103:D104]
Application.ScreenUpdating = False
P = 0
While fichier <> ""
    For Each a In P.Areas
        ThisWorkbook.Names.Add "Matrice", a.Value 'nom défini sur une matrice
        a.FormulaArray = "=Matrice+'" & chemin & "[" & fichier & "]" & feuille & "'!" & a.Address 'formule de liaison matricielle
        a = a.Value 'supprime la formule
    Next
    fichier = Dir 'fichier suivant
    n = n + 1
Wend
Application.ScreenUpdating = True
MsgBox n & " fichiers consolidés en " & Format(Timer - t, "0.00 \s")
End Sub

60 fichiers tests consolidés en 8,5 secondes chez moi avec ce fichier (2).

 

[job75]

Dans le dossier zippé joint j'ai mis le fichier Récap(2).xlsm et les 60 fichiers tests (allégés).

La macro Consolider s'exécute chez moi en 3,4 secondes, qu'en est-il sur Power Query chris ?

 

[chris]

Bonjour job75

Tes fichiers présentent une petite différence avec les fichiers de nanie : la colonne 11 est propre alors qu'elle était détectée dans les 2 fichiers : j'ai corrigé pour l'éliminer si elle est trouvée

Par ailleurs en regardant tes résultats j'ai vu que j'ai pris la ligne de tarifs et non la ligne de quantités ce qui explique les valeurs bizarres relevées par nanie sur les fichiers vides (#15 + MP)

Ton code fait plus de choses car j'ai limité à ce qu'il y avait dans le fichier Test _Recap de nanie qui n'intègre ni la partie basse Salaires, ni FORMATIONS CONTINUES

Je consolide 8 thématiques et toi 13 de plus... donc cela joue sur le temps

Sur mon PC j'ai 4,59 pour le VBA et 0,46 pour PowerQuery

Tu as Powerquery sur 2019 : j'ai ajouté qq lignes de code pour le timing et un bouton Actualiser
Tu peux tester sur ton PC

Je reposte aussi test1 et test2 où j'ai ajouté des valeurs

 

[chris]

Bonjour

Version sans le code de timing pour nanie

 

[job75]

@chris

J'avoue que je suis largué car ton fichier SyntheseCours_PQ n'a pas la même structure que Test1 et Test2.

Dans ce cas ma macro est inopérante.

Bonne nuit.

 

[chris]

RE

Comme je l'ai dit, il est basé sur le fichier fourni par nanie au #5

Mais à part les numéros de lignes et la limitation à la partie haute ta macro fonctionne en la modifiant à peine

Les temps sont de 1,80 pour VBA et 0,46 pour PQ donc l'écart est négligeable

J'ai oubliè de lier mon bouton au code dans mon exemple PQ

 

[nanie]

Bonjour @chris et @job75 !
Je vous remercie sincèrement pour le temps que vous passez à essayer de me trouver une solution.
C'est vraiment super ce que vous faites.
J'avoue que je suis larguée dans vos explications je ne connais pas du tout.

Pour que je puisse tester de mon côté, c'est quel fichier alors que je dois prendre ?
Récap(3) ou SyntheseCours_PQ_V2 ?
je fais comme l'autre jour ?
J'enregistre dans mon dossier et ensuite je fais actualiser tout ?

 

[chris]

Bonjour

SyntheseCours_PQ_V2.xlsx (donc sans code VBA) est la version PowerQuery corrigée : comme je l'ai dit, j'utilisais les tarifs des lignes sources au lieu des quantités, d'où les nombres sur les fichiers vides

Tu enregistres effectivement dans ton dossier et ensuite actualiser tout

Récap(3).xlsm est la version VBA de job75 que j'ai adaptée à ta Recap : il y a un bouton pour déclencher le calcul

Le temps de traitement est légèrement plus rapide avec la version PowerQuery : à voir sur les fichiers réels.

 

[nanie]

Re !

Alors j'ai essayé avec le fichier Récap(3) et j'ai ce message

je ne sais pas quoi faire ....


Et avec le fichier SyntheseCours_PQ_V2 , j'ai celui-ci

mais il a bien pris en compte les heures fictives notées pour un intervenant.
l'opération s'effectue en une bonne trentaine de secondes.