Macro VBA pour compiler les données de plusieurs fichiers dans un fichier
Bonjour,
j'ai à compiler les données de plusieur fichier dans un seul fichier Excel (le nombre réel est de 500 fichiers
Chaque fichier contient plusieurs onglets mais je dois compiler les onglets (Zone 1) et (Zone 2).
pour chaque fichier, chacun des onglets précédantes comporte des tableaux avec des adresses fixes (exemple premier tableau de l'onglet Zone1 commence tjs à la cellule A37 et finit à la cellule E57 et ce pour tous les fichiers à compiler...mm chose pour les autres tableaux. Malhereusement ces tableaux n'ont pas été nommées (donc pour mon niveau débutant avecpower query, je pense pas que ça peut marcher avec une requête). donc j'ai besoin d'un macro pour faire l'affaire
En manuel, la méthode à suivre est la suivante:
- Ouvrir un nouveau fichier Excel (compilation comme nom par exemple) et créer deux onglets nommés: Zone 1 et Zone2
- Dans l'onglet Zone1, on crée une colonne A où on va mettre le nom de fichier qui est la source des données pour les autres colonnes (B, C...etc).
- Ouvrir les fichiers à compiler un par un sur l'onglet Zone 1 et copier les données de chacun de ses tableaux tout en supprimant les entêtes qui se répètent et toutes les lignes qui pr`c`dent le premier tableau. la copie commencera à partir de la colonne B puisque la colonne A contient le nom de fichier d'où viennent ces données.
- Faire la même chose avec l'onglet Zone2 de chacun des fichiers.
A la fin on aura un fichier de compilation qui contient deux onglets:
- Zone 1 est la compilation de tous les données provenant des tableaux de l'onglet Zone 1 de chacun des fichiers ( à partir de la ligne 38..Les lignes précédentes contiennent des dpnnées qui ne seront pas traitées)
- Zone 2 est la compilation de tous les données provenant de données Zone 2 de chacun des fichiers ( à partir de la ligne 44. Les lignes précédentes contiennent des dpnnées qui ne seront pas traitées))
Ah pour ne pas oublier les feuilles et le classeur sont protégé par un mot de passe: 123 donc à considéer une instruction dans le macro VBA pour dévérouiller les classeurs et feuilles. les fichiers sont sauvegardés dans le même dossiers. ainsi le macro VBA doit faire une boucle pour parcourir tous les fichiers qui existent dans ce dossier)
Je ne sais pas si c'est clair mais avec les fichiers exemples ci-joint je pense que ça va être plus compréensible pour vous.il y a 4 fichiers joints:
- 3 fichiers de données: Client_AA001.xlsx, Client_AB1542.xlsx et Client_ACA003.xlsx
- 1 fichier de compilation: Résultat_demandé.xlsx qui comiple les données des trois fichiers.
Je serai vraiment reconnaissant si cette compilation de données se fait par un programme vba puisque le nombre de fichiers réel est de 500 fichiers et s'il en a pas quelque chose qui marche pas je passerai le temps des fêtes avec ce boulot
Je vous remercie pour votre aide!
Bonjour
Une proposition PowerQuery
Modifier le chemin dans la cellule jaune et les paramètres de PowerQuery pour utiliser correctement cette cellule :
Lancer PowerQuery, Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité
Bonjour 78chris,
merci beaucoup pour la solution.
hier j'ai réessayé une chose semblable avec PowerQuery. J'ai fait un filtre pour enlever les lignes vides mais je ne suis pas arrivé à supprimer les entêtes en doubles des tableaux. pourrais-tu me donner l'instruction stp?
Merci!
RE
Comme tu peux le voir dans la requête Transformer l'exemple de fichierZ1, qui alimente la fonction TraiterZ1, il suffit de filtrer la valeur N° Produit après l'étape de passage de la 1ère ligne en en-tête.
Parfait..Merci beaucoup 78Chris..
la solution VBA m'intéresse. Je ne sais pas si quelqu'un peux me donner un coup de main.
Merci!
re,
vous avez 2019, donc avec PQ, on peut faire cela en VBA, mais pour quoi ?
500 dossiers, 500 ouvrir et fermer (virtuellement) un fichier, durée ?
Oui t'as raison BsAlv. Avec VBA Excel va sûrement se planter.
Merci
re,
un estimat après un test avec vos 3 fichiers = 0.6 sec / fichier
500 fichier = 300 sec = 5 minutes. Et PQ ?
RE
J'ai dupliqué les fichiers : j'ai en plus de 500 et cela prend quelques secondes
pfffffffffffffffffffffffffffffffffffffff ...
bravo a vous
Merci bcp
re,
nouvelle méthode et j'ai volé l'idée ici https://forum.excel-pratique.com/excel/copier-coller-des-donnees-de-classeurs-fermes-vers-un-classeu...
nouvelle estimation 45-60 secondes pour 500 fichiers. (Sans tableaux structurés peut-être encore plus vite ???)
re,
sans TS, estimation = 20-30 sec
Et avec un PQ, quelque secondes, c'est combien (<10-15 ???) ?
EDIT : petite faute
ThisWorkbook.Sheets("zone2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(200, 8).Value = .Range("AA1").Resize(200, 8).Value2
BsAlv,
il y a quelque chose qui marche pas dans ton code. En fait les sous-totaux sot disparus de l'onglet Zone1 et 2 pour le fichier avec TS. Pour le fichier sans TS, même remarque pour Zone 1 et 2 en plus pour zone 2, deux colonnes sont sans valeurs et la colonne sous-totaux semestre 2 est disparu!
avec ces colonnes
Super! il te reste juste l'étiquette de la dernière colonne des deux onglets
re,
la ligne 1, la macro ne la change pas, donc vous pouvez l'ajouter manuellement.
Si vous comparez PQ et VBA pour 500 fichiers, quel est l'écart en temps ?
Avec les fichiers réels:
En VBA, ça fait 2min 44 sec avec un i7 8ieme gen 8 g ram et 2 mn avec PQ .
Ta solution debvient avantageux pour 100 fichiers et moins puisque la durée est presque égale (45 sec) pour 100 fichiers.
bizarre, ça veut dire avec ces chiffres que le temps est 15 sec (fixe) + 0.3 sec/fichier (chiffres arrondi) oubien que le temps n'est pas linéaire avec le nombre de fichiers.
C'est quoi le temps de 1 et 10 fichiers ?
Oui ce n.est pas linéaire puisque en VBA, c'est au fur et à mesure que le nombre augmente la mémoire vive de l'ordi se remplit et donc prend plus de temps avec 500 fichiers.
Avec power query je pense que c'est la mémoire cache qui se met en jeu et la requête s'effectue rapidement puisqu'il chargent pas les fichiers sources mais plutôt fait les liaisons.
Pour ta question 1 fichier avec chrono 33 sec pour 10 fichiers :37,5..un peu bizarre non..mais c'est la mémoire qui joue un rôle lors de la compilation du code.
Bon ça me fait tourner la tête tous ces chiffres
Merci!