[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 !
 

tatiak

XLDnaute Barbatruc
Bonjour,

Pour un nombre conséquent de fichiers xlsm, je propose de passer via ADO avec le code suivant :
VB:
Sub Acquisition()
Dim rep As String, i As Integer, j As Integer, lg As Long
Dim TOng As Variant, T As Variant, Req As String
 
    Application.ScreenUpdating = False
    rep = ChoixRep
    If Not rep = "" Then
        ReDim T_Fichier(0)
        ListeFichiers rep
        lg = 2
        If UBound(T_Fichier) > 0 Then
            For i = 1 To UBound(T_Fichier)
                If T_Fichier(i) Like "*.xlsm" Then
                    TOng = Liste_Onglets(T_Fichier(i))
                    If UBound(TOng) > 0 Then
                        For j = 1 To UBound(TOng)
                            Req = "SELECT * FROM [" & TOng(j) & "]"
                            T = Select_Xls(T_Fichier(i), Req)
                            ActiveSheet.Range("A" & lg).Value = T_Fichier(i)
                            ActiveSheet.Range("B" & lg).Value = TOng(j)
                            ActiveSheet.Range("C" & lg).Value = UBound(T, 1) + 1
                            lg = lg + 1
                        Next j
                    End If
                End If
            Next i
        End If
    End If
    Application.ScreenUpdating = True
End Sub
Voir mise en œuvre dans le fichier joint :
  • un bouton pour choisir le dossier contenant les xlsm à lister,
  • pour chaque xlsm, chaque onglet est listé et le nombre de lignes est indiqué pour chacun d'eux.
Pierre
 

Fichiers joints

Dernière édition:

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 !
 

tatiak

XLDnaute Barbatruc
Bonjour,

Ce n'est pas un soucis, comme indiqué le bouton est bien prévu pour sélectionner un dossier/répertoire contenant des xlsm.
A noter cette procédure traite le contenu du dossier et celui des sous-dossiers éventuels.

Si on veux, et pour tester toujours le même répertoire, on peut bien sûr écrire en dur le chemin.
A la place de rep = ChoixRep, au début de Sub Acquisition(), on peut écrire :
VB:
rep = "W:\chemin\chemin\chemin\chemin\"
Ceci étant dit, et après autres tests, je propose ici une version 2 dont le code a été amélioré et simplifié pour être plus rapide et plus précis.

Et ici c'est bien la colonne A qui est testée, cf fin de la ligne :
VB:
Req = "SELECT * FROM [" & Tbl.Name & "A:A]"
Pierre
 

Fichiers joints

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

Fichiers joints

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

Fichiers joints

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

Fichiers joints

merinos

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

Fichiers joints

Discussions similaires


Haut Bas