Faire une recherche par Ligne sur plusieurs fichiers Excels

[Bjornkciev]

Bonjour à tous,

merci d'avance pour votre contribution.

Je me présente brièvement, je suis un utilisateur de PC aguéri, en revanche je suis un total néophyte sur excel.
Dans le cadre professionnel je suis vendeur d'art on utilise beaucoup excel, la direction a mis en place des fichiers excel afin de retrouver les prix publiques sur les produits que nous proposons.

En clair nos stocks varient et changent tous les mois, de ce fait nous n'avons jamais les mêmes produits et parfois on s'y perd un peu dans tous ces fichiers excels qui sont organisés par fournisseurs et non pas par catégorie de produit.

Alors quand on recherche le prix d'un produit à indiquer au client si on ne connait pas le nom du fournisseurs ni la catégorie de produit (celle ci peut varié d'une personne à l'autre qui crée la pricing liste, en effet par exemple, une fontaine en forme de tronc d'arbre pour certains peut en fait se retrouver dans Décoration d'extérieure ou dans Fontaine ou dans arbre ou tronc.) Fort heureusement les fichiers possèdent des filtres de recherche et nous permet de rechercher par nom de produit (exemple: cette fameuse fontaine en forme de tronc d'arbre , je peux la retrouver si je connais le fichier fournisseur excel à ouvrir grace au mot clé : Fontaine / tronc / arbre).

Le pricing list d'un fournisseur est cartographié par ligne dans excel : exemple:

Colonne 1 Colonne 2 Colonne 3 Colonne 4
Réf 125266 Photo du produit Description (exemple Fontaine Tronc d'arbre ) Prix Publique

En revanche ca devient vite la misère si on ne connait pas le nom du fournisseurs, car comme indiqué plus haut dans mon post, nous avons une centaine de fournisseurs différents.
En revanche la pricing list est plus ou moins tout le temps formatée de la même façon par ligne.

Alors après ce petit exposé pour vous expliqué le problème : Est - il possible de faire une recherche par mot clés (en l'occurence ça concernerait la Description du produit) qui prendrait en compte tous les fichiers excel de tous les fournisseurs mais qui me donnerai un résultat avec la Ligne entière du produit recherché , afin de trouver le prix du produit sur la même ligne.

Exemple :

Je recherche donc le mot clé Fontaine :
Voici le résultat escompté :
Colonne 1 Colonne 2 Colonne 3 Colonne 4
Réf 25222 Fournisseurs A Description (exemple Fontaine En forme d'ours ) Prix Publique
Réf 125456 Fournisseurs B Description (exemple Fontaine Tronc d'arbre ) Prix Publique
Réf 128566 Fournisseurs C Description (exemple Fontaine Tronc d'arbre2 ) Prix Publique
Réf 1254566 Fournisseurs D Description (exemple Petite Fontaine En marbre ) Prix Publique

Est ce possible?

Je vous remercie de votre lecture.

CDLT

 

[Robert]

Bonjour Bjornkciev (p...! C'est aussi dur à écrire qu'a prononcer. Mais t'as vu, je me suis appliqué...) bonjour le forum,

C'est faisable mais il faut connaître le chemin d'accès de tous les fichiers. S'ils se trouvent dans le même dossier c'est encore plus facile ! Il faut aussi savoir si, quand on traite un fichier, on doit traiter un onglet en particulier de ce fichier ou tous ses onglets. En clair, les données sont toujours dans un seul onglet (le premier ?) ou dans plusieurs onglets ? Il faut que tu nous donnes ces renseignements...

 

[Bjornkciev]

Bonsoir,
Alors il y a des fichiers Excel qui ont des onglets (si par onglet on fait référence à différentes feuilles dans le meme fichier Excel ) alors oui il y a des fichiers avec des onglets .
On se qui concerne les fichiers en soit ils sont effectivement tous dans le même dossier sans sous dossiers

 

[Robert]

Bonjour Bjornkciev,

Le code ci-dessous fonctionne si :
- Le classeur destination se trouve dans le même dossier que les classeurs source.
- Les données des Pricing List commencent dans la cellule A1.
- Le tableau de la Princing List n'est pas coupé d'une ligne entière vide ou d'un colonne entière vide.

Tape le mot à rechercher en B1, les données trouvées s'affichent à partir de A5. La colonne A affiche le nom du classeur, la colonne B le nom de l'onglet, la colonne C le numéro de ligne et, à partir, de la colonne D le ligne recherchée...

Le code :

Sub Macro1()
Dim CD As Workbook 'déclare la variable CD (Classeur Destination)
Dim OD As Worksheet 'déclare la variable OD (Onglet Destination)
Dim CA As String 'déclare la variable CA (Chemin d'Accès)
Dim F As String 'déclare la variable F (Fichier)
Dim CS As Workbook 'déclare la variable CS (Classeur Source)
Dim O As Worksheet 'déclare la variable O (Onglet)
Dim OS As Worksheet 'déclare la variable OS (Onglet Source)
Dim TV As Variant 'déclare la variable TV (Tableau des Valeurs)
Dim NL As Long 'déclare la variable NL (Nombre de Lignes)
Dim NC As Byte 'déclare la variable NC (Nombre de Colonnes)
Dim TL() As Variant 'déclare la variable TL (Tableau des Lignes)
Dim I As Long 'déclare la variable I (Incrément)
Dim J As Byte 'déclare la variable J (incrément)
Dim K As Long 'déclare la variable K (incrément)
Dim L As Byte 'déclare la variable L (incrément)

Set CD = ThisWorkbook 'définit le classeur destination CD
Set OD = CD.Worksheets("Feuil1") 'définit l'ontglet destination OD
CA = CD.Path & "\" 'définit le chemin d'accès CA
If OD.Range("A5").Value <> "" Then OD.Range("A5").CurrentRegion.Clear 'efface d'éventuelles anciennes données
If OD.Range("B1") = "" Then Exit Sub 'si la cellule B1 de l'onglet OD est effacée, sort de la procédure
Application.ScreenUpdating = False 'masque les rafraîchissements d'écran
K = 1 'initialise la variable K
F = Dir(CA & "*.xlsx") 'définit le premier fichier F, ayant l'extension ".xlsx", du dossier dont le chemin d'accès est CA
Do While F <> "" 'boucle tant qu'il existe des fichiers F
    Application.Workbooks.Open (CA & F) 'ouvre le fichier F
    Set CS = ActiveWorkbook 'définit la classeur source CS
    For Each O In CS.Sheets 'boucle 1 : sur tous les onglet O du classeur source CS
        Set OS = O 'définit l'onglet source OS
        If OS.UsedRange.Cells.Count > 1 Then 'condition 1 : si l'onglet contient au moins une cellule éditée
            TV = OS.Range("A1").CurrentRegion 'définit le tableau des valeurs TV
            NL = UBound(TV, 1) 'définit le nombre de lignes NL du tableau des valeurs TV
            NC = UBound(TV, 2) 'définit le nombre de colonnes NC du tableau des valeurs TV
                For I = 1 To NL 'boucle 2 : sur toutes les lignes I du tableau des valeurs TV
                    For J = 1 To NC 'boucle 3 : sur toutes les colonnes J du tableau des valeurs TV
                        'condition 2 : si le texte recherché  (en B1 de l'onglet OD) est contenu dans la donnée en ligne I colonne J de TV
                        If InStr(1, TV(I, J), OD.Range("B1"), vbTextCompare) <> 0 Then
                            'on redimensionne le tableau TL avec 15 lignes car tu dis que les tableaux ont toujours "plus ou moins" le même format
                            'on évite ainsi un risque d'erreur si on se retrouve avec un tableau de plus de 4 colonnes
                            ReDim Preserve TL(1 To 15, 1 To K) 'redimensionne le tableau des lignes TL (15 lignes, K colonnes)
                            TL(1, K) = CS.Name 'récupère dans la ligne 1 de TL le nom du classeur
                            TL(2, K) = OS.Name 'récupère dans la ligne 2 de TL le nom de l'onglet
                            TL(3, K) = I 'récupère dans la ligne 3 de TL le numéro de ligne
                            For L = 4 To NC + 3 'boucle 4 : sur toutes les colonnes L - 3 du tableau des valeurs TV
                                TL(L, K) = TV(I, L - 3) 'récupère dans la ligne L de TL la valeur en colonne L - 3  de TV (= Transposition)
                            Next L 'prochaine colonne de la boucle 4
                            'les dernières lignes restent sans donnée c'est pas grave...
                            K = K + 1 'incrémente K (ajoute une colonne au tableau des lignes TL)
                            Exit For 'sort de la boucle 3
                        End If 'fin de la condition 2
                    Next J 'prochaine colonne de la boucle 3
                Next I 'prochaine ligne de la boucle 2
        End If 'fin de la condition 1
        Erase TV 'vide le tableau TV
    Next O 'prochain onglet de la boucle 1
    CS.Close False 'ferme le classeur source sans l'enregistrer
    F = Dir 'définit le prochain fichier de la boucle initiale
Loop 'boucle initiale
If K > 1 Then 'condition : si K est supérieur à 1
    'renvoie dans la cellule A5 redimensionnée de l'onglet destination OD, le tableau TL transposé
    OD.Range("A5").Resize(UBound(TL, 2), UBound(TL, 1)).Value = Application.Transpose(TL)
Else 'sinon
    MsgBox "Aucune ligne trouvée contenant le mot : " & OD.Range("B1").Value & " !" 'message
End If 'fin de la condition
Application.ScreenUpdating = True 'affiche les rafraîchissements d'écran
End Sub

Le fichier :