Chercher valeur dans "moult" fichiers fermés

[p.pat27]

Bonjour a tous.

Nouveau problème qui se pose à moi aujourd'hui !!!
J'ai dans un répertoire une multitude de fichiers excel (une centaine) qui ont tous la même structure, et je dois chercher dans chaque onglet de chaque fichier une valeur, qui est toujours dans la même colonne (C par ex) pour ramener une autre valeur qui, elle aussi, est toujours dans la même colonne (J par ex).
J'ai réussi a adapter un code qui me liste tous les fichers contenu dans le répertoire, triés du plus récent au plus ancien.
Maintenant je cherche a écrire le code VBA équivalent à la fonction "rechercheV" avec la liste de fichiers (la valeur cherchée est UNIQUE, et ne peux être que dans un seul fichier).
J'ai cherché sur le forum mais pas vraiment trouvé ce qui m'interresse! J'ai vu des codes qui cherche une valeur toujours au même endroit mais pas de rechercheV.
Pouvez vous m'aider ? Y a t'il déjà des post la dessus ?

Merci

Pat

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Bonjour le forum.

Après quelques jours de remue-neurone (oui y en a tjs qu'un!) , je reviens vers vous avec un début de code pour résoudre mon problème!!
J'arrive maintenant a récupérer les noms de fichiers avec les noms d'onglets, mais toujours pas a ramener la valeur cherchée !!
La formule avec VLOOKUP (correspondant à rechercheV d'après ce que j'ai compris) ne m'affiche pas le résultat mais tjs la formule!!! Je ne comprends pas!
Pouvez vous m'aider SVP

Merci

Voici le code


Dim chemin As String

Private Sub ListeFichiers()
Dim Dossier As Object, Fichier As Object
Dim i As Long

'Chemin du dossier à analyser (à faire: modif avec "getopen...")
chemin = ThisWorkbook.Path

'Définition de la variable
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(chemin)

' Boucle sur les fichiers
i = 2
For Each Fichier In Dossier.Files
Cells(i, 1) = Fichier.Name
i = i + 1
Next

Call ComptageFeuille

End Sub

Private Sub CommandButton2_Click()
Call ListeFichiers
End Sub

Sub ComptageFeuille()
'
Dim Nomfich, Nomfeuil As String
Dim i, Nbfeuil, y As Integer
Dim Wb As Workbook
Nbfeuil = 0

nbligne = Range("A65536").End(xlUp).Row

'reprise du nom de fichier de la colonne A et comptage du nombre d'onglet
For i = 2 To nbligne
Nomfich = Range("A" & i).Value
Set Wb = GetObject(chemin & "\" & Nomfich)
Nbfeuil = Wb.Sheets.Count
Cells(i, 2) = Nbfeuil
'recup nom feuille
For y = 1 To Nbfeuil
Nomfeuil = Wb.Worksheets(y).Name
Cells(i, y + 2).Value = Nomfeuil

Cells(1, 12).Formula = " VLOOKUP(range(K1),[" & Nomfich & "]" & Nomfeuil & "!$A$2:$B$30,2,false)"

Next y
Next i

End Sub

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Bonjour P.Pat27 ,
Si j'ai bien compris ton problème (valeur unique), essaie ce code

Sub test()
Dim Chemin As String, AChercher As String, Cellule As Range
Dim Dossier As Object, Fichier As Object, Classeur As Workbook, Feuille As Worksheet
Dim I As Integer
AChercher = InputBox("Valeur à chercher :", "Recherche multi fichiers")
Chemin = ThisWorkbook.Path
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Fichier In Dossier.Files
If Right(Fichier.Name, 4) <> ".xls" Then GoTo Suite
Set Classeur = GetObject(Chemin & "\" & Fichier.Name)
For Each Feuille In Classeur.Worksheets
Set Cellule = Feuille.Cells.Find(AChercher, LookAt:=xlWhole)
If Not Cellule Is Nothing Then
MsgBox "Fichier : " & Fichier.Name & vbCrLf & "Feuille : " & Feuille.Name _
    & vbCrLf & "Cellule : " & Cellule.Address & vbCrLf & "Cellule à côté : " & Cellule.Offset(0, 1).Value
End If
Next
Suite:
Next
End Sub

On corrigera ensuite les plages de recherche ...
Bonne journée

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Salut JPN

Merci de ton aide. Cela fonctionne impeccable.
A la lecture de ton code je vois que j'ai pas mal de progrès à faire.


Peut on améliorer 3 petites choses?
1- est il possible de ne pas afficher les fichiers qui s'ouvrent les uns après l'autres? Ca ralenti beaucoup la macro! et surtout après ca empêche d'ouvrir le fichier trouvé (message fichier déjà ouvert alors qu'il ne l'est pas dans excel ???)
2- La valeur cherchée (unique) est TOUJOURS dans la colonne D (une valeur par ligne.)
Celle a ramener TOUOURS dans la G; par contre elle se trouve dans une cellule fusionnée et peux donc être commune a plusieures valeurs de la colonne D (jusqu'a 5 lignes).
D'après les tests la macro fonctionne bien si la valeur cherchée est sur la 1ere ligne de la cellule fusionnée. mais ne fonctionne pas sur les autres lignes.
3- peut on arrêter la macro lorsque la valeur est trouvée (puisque unique) ?

Merci encore de ton aide précieuse.

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Re ,

Peut on améliorer 3 petites choses?
1- est il possible de ne pas afficher les fichiers qui s'ouvrent les uns après l'autres? Ca ralenti beaucoup la macro! et surtout après ca empêche d'ouvrir le fichier trouvé (message fichier déjà ouvert alors qu'il ne l'est pas dans excel ???)
Marant, sous 2007, pas de message d'erreur, mais bon, c'est vrai que mes fichiers tests étaient ultra léger, et que des gros fichiers ouverts peuvent ralentir, voici la correction, sachant que j'ai aussi exclu le classeur d'origine (sinon, il le fermerait aussi ...), et supprimé l'affichage pour accélérer la macro.
2- La valeur cherchée (unique) est TOUJOURS dans la colonne D (une valeur par ligne.)
Celle a ramener TOUOURS dans la G; par contre elle se trouve dans une cellule fusionnée et peux donc être commune a plusieures valeurs de la colonne D (jusqu'a 5 lignes).
D'après les tests la macro fonctionne bien si la valeur cherchée est sur la 1ere ligne de la cellule fusionnée. mais ne fonctionne pas sur les autres lignes.
Cells prends toutes les cellules, Columns(4) prend la colonne D
Pour les cellules fusionnées, ben y faut vérifier si elles le sont, et dans ce cas-là, stocker tous les résultats en face.
3- peut on arrêter la macro lorsque la valeur est trouvée (puisque unique) ?
Oui, en utilisant un Exit Sub juste après avoir affiché le résultat.
Merci encore de ton aide précieuse.

Sub test()
Dim Chemin As String, AChercher As String, Cellule As Range[COLOR=seagreen][B], PlageFusionnée As Range, CelluleFusionnée As Range[/B][/COLOR]
Dim Dossier As Object, Fichier As Object, Classeur As Workbook, Feuille As Worksheet[COLOR=seagreen][B], Résultat As String[/B][/COLOR]
Dim I As Integer
AChercher = InputBox("Valeur à chercher :", "Recherche multi fichiers")
Chemin = ThisWorkbook.Path
[COLOR=blue][B]Application.ScreenUpdating = False[/B][/COLOR]
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Fichier In Dossier.Files
If Right(Fichier.Name, 4) <> ".xls" [COLOR=blue][B]Or Fichier.Name = ThisWorkbook.Name[/B][/COLOR] Then GoTo Suite
Set Classeur = GetObject(Chemin & "\" & Fichier.Name)
For Each Feuille In Classeur.Worksheets
Set Cellule = Feuille.[COLOR=seagreen][B]Columns(4).[/B][/COLOR]Find(AChercher, LookAt:=xlWhole)
If Not Cellule Is Nothing Then
[COLOR=seagreen][B]If Cellule.MergeCells = True Then[/B][/COLOR]
[B][COLOR=seagreen]Set PlageFusionnée = Cellule.MergeArea[/COLOR][/B]
[B][COLOR=seagreen]For Each CelluleFusionnée In PlageFusionnée[/COLOR][/B]
[B][COLOR=seagreen]Résultat = Résultat & CelluleFusionnée.Offset(0, 3).Value & vbCrLf[/COLOR][/B]
[B][COLOR=seagreen]Next[/COLOR][/B]
[B][COLOR=seagreen]Else[/COLOR][/B]
[B][COLOR=seagreen]Résultat = Cellule.Offset(0, 3).Value[/COLOR][/B]
[B][COLOR=seagreen]End If[/COLOR][/B]
End If
Next
[B][COLOR=blue]Classeur.Close[/COLOR][/B]
[B][COLOR=red]If Résultat <> "" Then[/COLOR][/B]
[COLOR=seagreen][B]MsgBox Résultat[/B][/COLOR]
[COLOR=red][B]Exit Sub[/B][/COLOR]
[B][COLOR=red]End If[/COLOR][/B]
Suite:
Next
[COLOR=blue][B]Application.ScreenUpdating = True[/B][/COLOR]
End Sub

Bon courage

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Re

Merci c'est super, c'est exactement ce dont j'avais besoin.

J'ai juste remis la Msgbox de ton 1er code, celle qui affichait le nom du fichier, la feuille etc... mais sans le nom de la feuille car sinon ça bug !!
Pourtant le nom de feuille est bien défini, tout de moins de la même façon que le 1er code, mais ça affiche :
"variable objet ou variable de bloc With non définie !"

une idée ??


Merci

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Re ,
Je ne pensais pas que tu en avais encore besoin .
Le problème est l'emplacement où tu le mets. Il faut qu'il soit à l'emplacement original

If Not Cellule Is Nothing Then
[COLOR=red][B]MsgBox "Fichier : " & Fichier.Name & vbCrLf & "Feuille : " & Feuille.Name _
    & vbCrLf & "Cellule : " & Cellule.Address & vbCrLf & "Cellule à côté : " & Cellule.Offset(0, 1).Value
[/B][/COLOR]If Cellule.MergeCells = True Then

sinon, le classeur étant fermé, quelle feuille veux-tu qu'il te renvoie !
Bonne fin d'après-midi

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Re coucou

Il semble y avoir un bémol. Ca ne marche qu'avec la première ligne de la valeur cherchée (je joins un fichier: case verte ça trouve, case orange ça trouve pas)
Sans doute un problème au niveau de la cellule fusionnée je pense, mais mes connaissance ne me permetent pas de trouver le quoik!

Jai encore besoin d'un peu d'aide
Merci

Et le code

Private Sub CommandButton1_Click()

Dim Chemin As String, nomfeuil As String, AChercher As String, Cellule As Range, PlageFusionnée As Range, CelluleFusionnée As Range
Dim Dossier As Object, Fichier As Object, Classeur As Workbook, Feuille As Worksheet, Résultat As String
Dim I As Integer

AChercher = InputBox("Saisir les 5 derniers digits du code bobine," & vbCrLf & "ex: pour HRP00028115, saisir 28115 !", "Recherche code HARPER")
Chemin = ThisWorkbook.Path

Application.ScreenUpdating = False
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Fichier In Dossier.Files
If Right(Fichier.Name, 4) <> ".xls" Or Fichier.Name = ThisWorkbook.Name Then GoTo Suite
Set Classeur = GetObject(Chemin & "\" & Fichier.Name)
For Each Feuille In Classeur.Worksheets
Set Cellule = Feuille.Columns(4).Find(AChercher, LookAt:=xlWhole)
If Not Cellule Is Nothing Then
If Cellule.MergeCells = True Then
Set PlageFusionnée = Cellule.MergeArea
For Each CelluleFusionnée In PlageFusionnée
Résultat = Résultat & CelluleFusionnée.Offset(0, 3).Value & vbCrLf
Next
Else
Résultat = Cellule.Offset(0, 3).Value
nonfeuil = Feuille.Name
End If
End If
Next
Classeur.Close
If Résultat <> "" Then
MsgBox "Fichier : " & Fichier.Name & vbCrLf & _
"Feuille : " & nomfeuil & vbCrLf & _
"Code GP : HRP000" & AChercher & vbCrLf & _
"Code HARPER : " & Résultat
Exit Sub
Else

End If
Suite:
Next
MsgBox " Valeur non trouvée !"
Application.ScreenUpdating = True
End Sub

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Re ,

Il semble y avoir un bémol. Ca ne marche qu'avec la première ligne de la valeur cherchée (je joins un fichier: case verte ça trouve, case orange ça trouve pas)
Sans doute un problème au niveau de la cellule fusionnée je pense, mais mes connaissance ne me permetent pas de trouver le quoik!

Juste une erreur de lecture de ma part, j'avais compris que les cellules fusionnées étaient celles de recherche, et non celles recherchées .

Sub test2()
Dim Chemin As String, AChercher As String, Cellule As Range, PlageFusionnée As Range, CelluleFusionnée As Range
Dim Dossier As Object, Fichier As Object, Classeur As Workbook, Feuille As Worksheet, Résultat As String
Dim I As Integer
AChercher = InputBox("Valeur à chercher :", "Recherche multi fichiers")
Chemin = ThisWorkbook.Path
Application.ScreenUpdating = False
Set Dossier = CreateObject("Scripting.FileSystemObject").GetFolder(Chemin)
For Each Fichier In Dossier.Files
If Right(Fichier.Name, 4) <> ".xls" Or Fichier.Name = ThisWorkbook.Name Then GoTo Suite
Set Classeur = GetObject(Chemin & "\" & Fichier.Name)
For Each Feuille In Classeur.Worksheets
Set Cellule = Feuille.Columns(4).Find(AChercher, LookAt:=xlWhole)
If Not Cellule Is Nothing Then
MsgBox "Fichier : " & Fichier.Name & vbCrLf & "Feuille : " & Feuille.Name _
    & vbCrLf & "Cellule : " & Cellule.Address & vbCrLf & "Cellule à côté : " & Cellule.Offset(0, 3).MergeArea.Cells(1, 1).Value
Exit Sub
End If
Next
Classeur.Close
Suite:
Next
Application.ScreenUpdating = True
End Sub

fonctionnera mieux.
Bonne soirée

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Bjr JNP

Merci pour cette ultime modif, ça marche super maintenant, et avec toutes les valeurs

Pour mon info perso, VLOOKUP marche t'il avec Excel 2000?

Et un dernier conseil si tu veux bien.
J'ai un Pb d'affichage avec Visual basic, depuis quelques temps j'ai les fenêtres "Projets" & "propriètés" qui s'affichent les unes à cotés des autres (et non plus les unes en dessous des autres) cela me laisse très peu de place pour la fenêtre "code" et c'est pas vraiment pratique.
j'ai du faire une fausse manip dans l'affichage mais je ne retrouve pas quoi !
As tu une idée pour remettre tout ça dans l'ordre?

Bonne journée

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Re ,

Pour mon info perso, VLOOKUP marche t'il avec Excel 2000?

Oui, bien sûr, mais VLookUp est une recherche sur une matrice renvoyant le résultat d'une colonne dans la matrice en utilisant une formule de feuille.
Find est l'équivalent de la fenêtre Rechercher, donc pas du tout la même utilisation...
Le seul règlage que tu as dans VLookUp est la correspondance vrai ou faux en fonction de si tu veux la valeur exacte ou t'arrêter dès que tu as une valeur proche. De plus il ne peux te renvoyer que la 1ère valeur.
Find lui possède toutes les propriétés, cellule entière ou pas, respecter la casse ou pas, etc. Il permet aussi de lancer une recherche horizontale après la verticale, par exemple. Et surtout, il permet de chercher la valeur suivante avec FindNext .

J'ai un Pb d'affichage avec Visual basic, depuis quelques temps j'ai les fenêtres "Projets" & "propriètés" qui s'affichent les unes à cotés des autres (et non plus les unes en dessous des autres) cela me laisse très peu de place pour la fenêtre "code" et c'est pas vraiment pratique.
j'ai du faire une fausse manip dans l'affichage mais je ne retrouve pas quoi !
As tu une idée pour remettre tout ça dans l'ordre?

Quand tu clique sur le haut de ta palette pour la déplacer, il suffit de la glisser vers un bord (droit, haut, bas ou gauche) pour que la palette se réancre. De même, il a suffit que tu la glisses vers le centre pour la désencrer.
Bon appétit

 

[p.pat27]

Re : Chercher valeur dans "moult" fichiers fermés

Re,

Merci pour tous ces conseils et explications prècieuses.
Tout est rentré dans l'ordre pour Visual Basic !
c'est quand même mieux avec un pro


A+

Bonne journée

 

[Ubot303]

Re : Chercher valeur dans "moult" fichiers fermés

Bonjour,

J'arrive après toute une série d'échanges...
Pour recherche une valeur dans une centaine de fichiers (Excel ou autres), moi j'utilise la fonction windows Démarrer -> Rechercher -> Des fichiers ou des dossiers et on remplit la valeur dans "contenant le texte"...

 

[JNP]

Re : Chercher valeur dans "moult" fichiers fermés

Re ,

Pour recherche une valeur dans une centaine de fichiers (Excel ou autres), moi j'utilise la fonction windows Démarrer -> Rechercher -> Des fichiers ou des dossiers et on remplit la valeur dans "contenant le texte"...

Certes, mais est-ce que ça te ramène automatiquement la valeur de la cellule un peu plus loin tout en tenant compte des cellules fusionnées ?...
Non, je plaisante, je l'utilise aussi pour chercher dans mes archives de messages, par exemple, mais dans le cas présent, notre ami souhaitait récupérer des valeurs en automatique .
Bonne soirée

 

[Ubot303]

Re : Chercher valeur dans "moult" fichiers fermés

Des fois, une approche différente peut changer la donne