Macro RECHERCHEV entre 2 fichiers excel distincts [Trucs & Astuces]

**paflolo** · 18/10/2006, 17h54

Bonsoir,
est-il possible de réaliser une macro VBA qui permette de chercher des valeurs dans un fichier excel différent de celui sur lequel on travaille, et d'effectuer une recherchev sur ce fichier pour insérer les données dans le fichier en cours d'utilisation?

je sais que recherchev se déclare par VLOOKUP en vba.
mais comment en plus ne prendre que les valeurs présentes dans le 2ème fichier, en mettant une valeur par défaut dans le fichier en cours si la recherchev ne trouve rien?
pouvez-vous m'aider, je sèche....
merci pour votre future aide, comme d'hab

**RLX** · 18/10/2006, 18h10

Salut paflolo,

Si tu arrive a le faire dans le même fichier alors il est très simple d'adapter le code sur plusieur fichier, il suffit juste d'utiliser workbooks("NomClasseur.xls") où NomClasseur.xls est le nom de ton classeur sur lequel tu veux faire la recherche ou écrire des données. Autre info, tu peux utiliser ThisWorkbook aussi qui se rapporte au classeur dans lequel est écrit la macro.

Un petit exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Workbooks("NomClasseur.xls").Worksheets("Toto").Cells(1,1).value = "Mon Texte"
ThisWorkbook.Worksheets("Toto").Cells(1,1)Interior.ColorIndex = 15

Le premier écrit "Mon Text" dans une cellule du classeur NomClasseur.xls et le deuxième colorie la cellule dans le classeur où est écrit la macro.

**AlainTech** · 18/10/2006, 22h59

Une bonne habitude à prendre, quand on doit travaiiler sur plusieurs fichiers (et pas seulement sur plusieurs), c'est de créer des instances de ces classeurs.
Petit exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Dim wbkSource As Workbook
Dim wbkDest As Workbook
Set wbkSource = Workbooks("Classeur1")
Set wbkDest = Workbooks("Classeur2")
' Un exemple de manipulation:
wbkSource.Sheets("Feuille1").[A1].Copy _
      Destination:=wbkDest.Sheets("Feuille1").[A1]

**paflolo** · 19/10/2006, 00h08

ah cool merci pour cette astuce!
c'est comme quand on travaille sur plusieurs feuilles ;-)
je vais essayer de chercher des exemples pour vlookup parce que pour l'instant j'ai pas réussi à faire mon rechercheV de valeurs

**AlainTech** · 19/10/2006, 05h21

Au lieu de chercher dans les fonctions de feuilles de calcul, cherche dans VBA.

La méthode Find est ce qui te convient.

Bien que je trouve l'exemple de l'aide en ligne bien clair, voici un petit coup de pouce:
Find s'applique à un Range et renvoie un objet Range (composé d'une cellule).
Comme on va devoir se référer à cet objet, on va en créer une instance (Set), comme pour les feuilles ou les classeurs.
On pourra donc utiliser cette instance comme référence pour trouver les cellules voisines.

J'espère que ces quelques indications t'aideront à comprendre la notion d'objet et à écrire tes programmes de manière plus "propre" et moins Basic.

**paflolo** · 19/10/2006, 10h13

pour le moment j'en suis à ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Sub emp()
Dim f2 As Worksheet
Dim f4 As Worksheet
Dim c As Range 'Colonne cellule  copier
Dim d As Range 'Colonne cellule  copier
Dim lignevidecopy As Long
Set f2 = Feuil3
Set f4 = Feuil6
 
'requete
 
Set c = f4.Cells.Find(What:="13462", After:=f4.Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
  If c Is Nothing Then
    MsgBox "Nom Introuvable ", vbCritical
    Exit Sub
  End If
    f4.Select
    c.Select
    f4.Range("B" & c.Row).Select
    Valo = f4.Range("B" & c.Row).Value
    f4.Range("B" & c.Row).Copy
    f4.Range("D" & c.Row).Value = Valo
    f4.Range("E" & c.Row).Value = c
 
Set d = f2.Cells.Find(What:=c, After:=f2.Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
  If d Is Nothing Then
    MsgBox "Nom Introuvable ", vbCritical
    Exit Sub
  End If
    f2.Select
    f2.Range("J" & d.Row).Select
    ActiveSheet.Paste
 
End Sub

J'arrive à partir d'un numéro que je donne à me positionner sur l'autre feuille sur la valeur que je souhaite coller.

Je n'arrive pas à faire la boucle sur la 1ère feuille pour parcourir toutes les valeurs puis chercher si elles sont présentes dans l'autre feuille, puis à coller les valeurs dans la colonne I de ma 1ère feuille lorsqu'elles sont repérées ...

si la valeur de 1ere feuille n'existe pas dans la 2nde, alors je ne fais rien et je passe à la valeur suivante de la feuille1
si la valeur existe, alors je copie la cellule qui est sur la colonne B, et je la colle sur la colonne I de la 1ere feuille sur la ligne correspondante à la valeur

**paflolo** · 19/10/2006, 12h22

J'en suis à ce niveau: çà fonctionne tant qu'il y a les valeurs, enfin apparemment lol

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
Sub emp2()
Dim f2 As Worksheet
Dim f4 As Worksheet
Dim c As Range 'Colonne cellule  copier
Dim d As Range 'Colonne cellule  copier
Dim lignevidecopy As Long
Set f2 = Feuil3
Set f4 = Feuil6
 
'1ere sous-requete
 
 f2.Select
'    f2.Cells.Sort Key1:=Range("A3"), Order1:=xlAscending, _
  '  Header:=xlGuess, OrderCustom:=1, MatchCase:=False
 
For i = 3 To f2.UsedRange.Rows.Count
    vale = f2.Cells(i, 1).Value ' référence
 
    f2.Cells(i, 9).Value = vale
 
 
'requete
 
Set c = f4.Cells.Find(What:=vale, After:=f4.Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
  If c Is Nothing Then
    MsgBox "Nom Introuvable ", vbCritical
    Exit Sub
  End If
    f4.Select
    c.Select
    f4.Range("B" & c.Row).Select
 
Set d = f2.Cells.Find(What:=c, After:=f2.Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
 ' If d Is Nothing Then
  '  MsgBox "Nom Introuvable ", vbCritical
   ' Exit Sub
 ' End If
    f2.Select
    f2.Range("J" & d.Row).Select
    ActiveSheet.Paste
 
Next i
End Sub

Mon problème, c'est que lorsqu'une valeur n'est pas trouvé dans la deuxiement feuille, çà s'arrete! Alors qu'une valeur suivante dans la feuille 1 peut etre présente dans la feuille2.
Je n'arrive pas à sortir de la boucle, et boucler jusqu'a la fin des valeurs, en ne modifiant pas la valeur de colonne I si la valeur n'est pas présente!

Pouvez vous m'aider?
MERCI d'avance
quelqu'un doit bien pouvoir m'aider...

**AlainTech** · 19/10/2006, 23h13

Un des buts (entre autres) de l'instanciation est de ne plus avoir besoin des Select et autres Activate.

Là, je vais me coucher mais, si tu veux, demain j'essayerai de t'expliquer.

A moins que tu ne trouves d'ici là.

**AlainTech** · 20/10/2006, 10h43

Plusieurs commentaires sur ton code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Set f2 = Feuil3
Set f4 = Feuil6

Là, tu fais une opération inutile.
Feuil3 est déjà un objet et tu n'as pas besoin d'en recréer une instance puisque tu en as déjà une à ta disposition.
Toutefois, il y a, à la fois, un certain risque et un manque de lisibilité à travailler avec le CodeName d'une feuille.
Tu admettras que, si tu dois relire ton code dans 1 an, tu te demanderas à quels onglets correspondent Feuil3 et Feuil6.
Voici un exemple d'instanciation d'une feuille avec un nom lisible (celui qui est visible sur l'onglet):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Dim wksSource As Worksheet
Dim wksDest As Worksheet
Set wksSource = Sheets("ListeBrute")
Set wksDest = Sheets("ListeParCode")

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

f2.Select

Inutile de sélectionner la feuille puisqu'on peut s'y référer quand on veut.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

For i = 3 To f2.UsedRange.Rows.Count

Ici, plutôt que d'utiliser un compteur pour se déplacer dans la première feuille, on va créer un objet Range des cellules utilisées dans la première colonne et parcourir ce Range avec un For Each.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Set rSource = Range(wksSource.[A3], _
                      wksSource.Cells(wksSource.Cells.SpecialCells(xlLastCell).Row, 1))
  For Each c In rSource

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

vale = f2.Cells(i, 1).Value ' référence

Dans la boucle For Each, c est un objet cellule. On peut donc s'y référer directement.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

strValeur = c.Value

Bon, là j'ai du boulot.
Je te livre déjà ces quelques réflexions.
Je reviendrai, si j'ai le temps, continuer ce message. Viens y voir de temps en temps.

**AlainTech** · 26/10/2006, 08h37

Comme ton problème m'intéressait et que je pense qu'il peut servir d'exemple pour l'usage des instanciations (de feuilles ou de cellules) et passage de ces instances en paramètres de fonctions, j'ai continué à le développer.
Voici où il en est actuellement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub emp2()
  Dim wksSource As Worksheet
  Dim wksDest As Worksheet
  Dim rSource As Range
  Dim c As Range ' cellule source
  Dim d As Range ' cellule destination
  Dim lignevidecopy As Long
  Set wksSource = Sheets("ListeBrute")
  Set wksDest = Sheets("ListeParCode")
 
  Set rSource = Range(wksSource.[A3], _
                      wksSource.Cells(wksSource.Cells.SpecialCells(xlLastCell).Row, 1))
  For Each c In rSource
    Set d = TrouveCode(c.Value, wksDest)
 
  Next c
End Sub
 
Function TrouveCode(sValue As String, Wks As Worksheet) As Range
  Dim c As Range
  Set c = Wks.Columns(1).Find(What:=sValue, LookAt:=xlWhole, MatchCase:=False)
  If c Is Nothing Then
    ' Attention, la feuille destination doit avoir, au moins, une ligne remplie
    Set c = Wks.Cells(Wks.Cells.SpecialCells(xlLastCell).Row + 1, 1)
    c = sValue
  End If
  Set TrouveCode = c
End Function

**AlainTech** · 26/10/2006, 10h01

Version complète:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Option Explicit
 
Sub emp2()
  Dim wksSource As Worksheet
  Dim wksDest As Worksheet
  Dim rSource As Range
  Dim c As Range ' cellule source
  Dim d As Range ' cellule destination
  Dim i As Integer
 
  Set wksSource = Sheets("ListeBrute")
  Set wksDest = Sheets("ListeParCode")
 
  Set rSource = Range(wksSource.[A3], _
                      wksSource.Cells(wksSource.Cells.SpecialCells(xlLastCell).Row, 1))
  For Each c In rSource
    Set d = TrouveNumero(c.Value, wksDest)
    For i = 1 To 6
      AjouteValeur c.Offset(0, i), d, i
    Next i
  Next c
End Sub
 
Function TrouveNumero(sValue As String, Wks As Worksheet) As Range
  Dim c As Range
  Set c = Wks.Columns(1).Find(What:=sValue, LookAt:=xlWhole, MatchCase:=False)
  If c Is Nothing Then
    ' Attention, la feuille destination doit avoir, au moins, une ligne remplie
    Set c = Wks.Cells(Wks.Cells.SpecialCells(xlLastCell).Row + 1, 1)
    c = sValue
  End If
  Set TrouveNumero = c
End Function
 
Sub AjouteValeur(sValue As String, DestCell As Range, nCol As Integer)
  If IsEmpty(DestCell.Offset(0, nCol)) Then
    DestCell.Offset(0, nCol) = sValue
  Else
    If InStr(1, DestCell.Offset(0, nCol), sValue) = 0 Then
      DestCell.Offset(0, nCol) = DestCell.Offset(0, nCol) & "/" & sValue
    End If
  End If
End Sub

Minimum de code.
Pas de Select ni Activate.

**illight** · 14/11/2006, 14h37

Bonjour,

je reviens sur tout ce qu'a dit AlanTech sur ce post

Je trouve fort intéressant tout ça, est vu le nombre de questions que l'ona a ssez souvent sur comment travailler sur 2 fichiers Excel en parallèle, il serait bien de le mettre comme un truc & astuces, ou une chose de ce genre la

juste le passage ou il parle des instances, et le pourquoi du comment

Après ce n'est qu'un avis personnel

**AlainTech** · 14/11/2006, 16h37

Dont acte

**illight** · 14/11/2006, 16h40

Et en meme temps, le placer dans "Vos contributions" sinon il va repartir dans les bas fonds du forum

Tiens d'ailleurs j'ai une question par rapport aux instances..

Comment on fai tpour instancier un classeur actif ? Comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Dim wbkDest As Workbook
Set wbkSource = activeworbook

? ou faut mettre quelquechose derrière l'activeworkbook ?

**AlainTech** · 15/11/2006, 03h46

Envoyé par illight

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set wbkSource = activeworbook

Faut juste pas faire de faute à activeworkbook

Macro RECHERCHEV entre 2 fichiers excel distincts [Trucs & Astuces]

Macros et VBA Excel

Discussions similaires

Partager

Partager