[VBA] Importer le nombre de lignes dans un classeur repertoire

[Beloteur]

Bonjour à tous,

J'ai un dossier rempli de fichiers .xlsm et je souhaite créer un fichier dans lequel j'importe certaines données relatives aux classeurs du repertoire donné. Notamment :

• une colonne avec le nom des fichiers
• une colonne avec le nombre de lignes pleines de la colonne A de chaque fichier.

Ca donnerai un truc du genre :

Nom du fichier	Nombre de ligne en colonne A
Fichier 1	990
Fichier 2	1011
Fichier 3	1115

Je ne suis pas très calé VBA mais j'ai trouvé cette macro qui me permet déjà d'importer le nom des classeurs :

Sub ListeFichiers()
Application.ScreenUpdating = False
Range("A265000").ClearContents
repertoire = "W:\chemin\chemin\chemin\chemin\"

[H2] = repertoire
ligne = 2
nf = Dir(repertoire & "*.xlsm")
Do While nf <> ""
Cells(ligne, 1) = nf
ligne = ligne + 1
nf = Dir

Loop
End Sub

J'imagine que je dois insérer une ligne du genre "Cells(ligne, 2) = Nombre de lignes de la colonne A du fichier correspondant" mais je ne sais pas comment faire.
Ou bien est-ce que je dois faire complètement autre chose ?
Pour l'instant j'épluche chaque fichier un par un pour aller voir la dernière ligne de la colonne A mais je perds un temps fou (!) et si je pouvais trouver une solution pour automatiser tout ça, j'aurai plus vite fait d'avoir les infos de mes 503 fichiers excel...

N'hésitez pas à poser des questions si ce n'est pas assez clair.

Merci d'avance pour votre aide !

 

[Beloteur]

Bonjour Pierre,

Merci beaucoup pour ces indications.
Petit souci : j'utilise votre fichier pour importation des données, le bouton ouvre une fenetre comme pour slectionner un repertoire mais ne me permet pas de selectionner des .xlsm
Dois-je ajuster quelque chose dans la macro ?
Dernière petite question : le nombre de ligne censé s'afficher est bien celui de la colonne A du classeur source ?

Merci encore !

 

[Beloteur]

Merci Pierre, cette fois-ci j'ai réussi à la faire fonctionner.

Seulement petit hic, ce que la macro me donne (cf en pj)

• en colonne A = le chemin d'accès à chaque fichier --> au lieu du nom du fichier seul
• en colonne B = le nom de chaque onglet de chaque fichier --> la ça se complique un peu **
• en colonne C = le nombre de lignes du document en entier --> et non le nombre de lignes de la colonne A, ou autrement dit le numéro de ligne de la dernière cellule pleine en colonne A (voyez en pj, la macro affiche 21048 alors que je devrai obtenir 990, 1011 ou 1115)

** Tous les fichiers ont au minimum 2 onglets :

1. Un onglet Feuil1 - qu'il n'est pas necessaire d'afficher car c'est toujours le meme, il contient les formules
2. Un onglet Formulaire - qui varie selon le fichier, c'est de cet onglet qu'il s'agit pour le nombre de lignes
3. Certains fichiers - une grande minorité - ont plusieurs onglets de Formulaires (Basic, Premium...)

Pour donner un ordre d'idée, voici le fichier avec lequel j'ai commencé à travailler avec la première formule qui rapatriait les noms des fichiers...

 

[Beloteur]

Bonjour Pierre,

C'est génial ça fonctionne à merveille ! Merci beaucoup !

Dernière petite coquille, mais votre fichier me permet dejà de faire un GRAND pas en avant :
Lorsqu'un fichier a plusieurs onglets (hors Feuil1), la colonne C ne rapatrie plus que le nombre d'onglets et non le nombre de lignes de la colonne A de chaque onglet. Cf screen ci-dessous.

Merci encore pour votre aide si précieuse et bon début de semaine à vous !

 

[merinos-BernardEtang]

Salut Beloteur,

Toutes ces méthodes sont compliquées.

Excel dispose de fonctions Power Query... La réponse est là.

j'ai crée un query qui recherche tous les fichiers qui sont dans le directory ou il est enregistré et regarde les feuilles " sheet1" et "sheet2"



Merinos

La même méthode va permettre de travailler tous ces fichiers ensembles.

 

[Beloteur]

Merci Pierre pour le document, tout fonctionne très bien !

@merinos merci aussi pour ton aide !
Par contre je n'ai pas très bien compris les fonctions Power Query...
J'ai beau mettre le fichier dans le repertoire où sont stockés tous mes doc que je veux inventorier mais rien en se passe

 

[merinos-BernardEtang]

Merci Pierre pour le document, tout fonctionne très bien !

@merinos merci aussi pour ton aide !
Par contre je n'ai pas très bien compris les fonctions Power Query...
J'ai beau mettre le fichier dans le repertoire où sont stockés tous mes doc que je veux inventorier mais rien en se passe

@Beloteur
Tu peux mettre un document de data où tu laisses au moins 10 ligne ici?

 

[Beloteur]

Salut Merinos,

Voila le type de fichier que je traite.
Lignes supprimées et contenu standardisé pour des questions de confidentialité bien sûr
La macro n'est donc plus valable.

Beloteur

 

[merinos-BernardEtang]

@Beloteur : Desolé du retard... j'essaie de regarder cela cet aprem

 

[merinos-BernardEtang]

@Beloteur ,

C'est fait ...

Tu poses ce fichier dans ton directory, Il va regarder les va regarder les valeurs de la dernière colonne et les compter.
Tu peux indiquer une chaîne de caractères qui va sélectionner les fichiers a analyser.

tu dois bien-sur rafraîchir le query.

Cela fonctionne même pour les sous-directory


Merinos