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

Beloteur

XLDnaute Nouveau
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 fichierNombre de ligne en colonne A
Fichier 1990
Fichier 21011
Fichier 31115

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("A2:D65000").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... :p

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

Merci d'avance pour votre aide !
 

Beloteur

XLDnaute Nouveau
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

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

Pièces jointes

  • Capture 1.PNG
    Capture 1.PNG
    65.1 KB · Affichages: 29
  • Models Ex - Liste PACIFICA.xls
    121.5 KB · Affichages: 17

Beloteur

XLDnaute Nouveau
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.
1026877


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

merinos

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

Pièces jointes

  • test lignes_documents.xlsx
    25.7 KB · Affichages: 14

Beloteur

XLDnaute Nouveau
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

XLDnaute Accro
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?
 
Dernière édition:

Beloteur

XLDnaute Nouveau
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
 

Pièces jointes

  • Target Model.xlsm
    34.3 KB · Affichages: 9

merinos

XLDnaute Accro
@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
 

Pièces jointes

  • Compteur auto.xlsx
    20.2 KB · Affichages: 14

Discussions similaires