Macro pour aller chercher des données dans un autre fichier

**GoToon** · 20/01/2015, 21h05

Bonjour à tous,

Ceci est mon premier post sur ce forum, car je ne pense pas trouver de solution à mon problème.

J'ai deux fichiers Excel. Un premier (DataSource) contient des données en vrac, non ordonnées avec en première colonne des références d'articles, en première ligne des caractéristiques liées à ces articles.
L'autre fichier (DataDestination) est une sorte d'extrait du premier. Dans ce fichier, je cherche à sortir quelques caractéristiques pour quelques références données.

J'ai bien réussi à pondre une formule avec un recherchev couplé à un equiv pour afficher les données que je veux. Le problème, c'est que j'ai une base source de plus de 12000 articles avec plus de 1200 caractéristiques et que je cherche à en extraire une base de 6000 articles avec 400 caractéristiques....

Autant dire que dupliquer ma formule de 3 lignes de long sur ces 2 millions de cellules prend un peu trop de ressources à mon pauvre PC.

J'aimerai donc essayer de développer une macro qui irait me remplir (sous la forme d'une boucle ?) mon fichier DataDestination. J'indique dans une cellule le chemin de la base source et je lance la macro... tellement simple dans ma tête...

En espérant que vous pourrez m'aider.

Je peux vous fournir des fichiers d'exemple au besoin...

Si ça peut aider :
Pour ma première référence en A5 et ma première Caractéristique en B4, la formule donne ça :

Code :

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

=SIERREUR(SI(RECHERCHEV($A5;DataSource.xlsx!$A$1:$E$6;EQUIV(B$4;DataSource.xlsx!$A$1:$E$1;0);0)="";"";RECHERCHEV($A5;DataSource.xlsx!$A$1:$E$6;EQUIV(B$4;DataSource.xlsx!$A$1:$E$1;0);0));"")

Ensuite, je duplique la formule sur tout mon tableau...

@+

**clementmarcotte** · 20/01/2015, 21h10

Bonjour,

Méthode des AMIS (Anti macros inutilement superfétatoires)

Dans le ruban "Données"

Cliquer Bouton Autres Sources

Cliquer sur Microsoft Query

Rendu là, il me reste à choisir en Fichiers Excel et Excel Files

Si tu veux absolument une macro:

http://silkyroad.developpez.com/VBA/ClasseursFermes/

**GoToon** · 21/01/2015, 00h47

J'aurai vraiment voulu une macro pour le coup...
Je regarde ton lien.

Merci !

Bon, ben je me suis débrouillé tout seul, mais j'ai un souci que je n'avais pas prévu...

Voici mon 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
39
40
41
42
Option Explicit
Public wkbSRC As Workbook
Public wksDES As Worksheet
Public wksSRC As Worksheet
 
Sub ImportBase()
Dim Col As Byte
Dim Lgn As Integer
Dim DerLgnSrc As Long
Dim DerColSrc As Long
Dim DerLgnDes As Long
Dim DerColDes As Long
Dim ColSearch As Integer
Dim PremColDes As Integer
Dim PremLgnDes As Integer
Dim PremColSrc As Integer
Dim PremLgnSrc As Integer
 
PremColDes = 1
PremLgnDes = 4
PremColSrc = 1
PremLgnSrc = 1
 
Workbooks.Open ("C:\Users\Nous\Desktop\DataSource.xlsx")
 
Set wksSRC = Workbooks("DataSource.xlsx").Sheets("DataSource")
Set wksDES = Workbooks("DataDestination.xlsm").Sheets("DataDestination")
 
DerLgnSrc = wksSRC.Cells(PremLgnSrc, PremColSrc).End(xlDown).Row
DerColSrc = wksSRC.Cells(PremLgnSrc, PremColSrc).End(xlToRight).Column
DerLgnDes = wksDES.Cells(PremLgnDes, PremColDes).End(xlDown).Row
DerColDes = wksDES.Cells(PremLgnDes, PremColDes).End(xlToRight).Column
For Col = PremColDes + 1 To DerColDes
    For Lgn = PremLgnDes + 1 To DerLgnDes
    wksDES.Cells(Lgn, Col).Value = WorksheetFunction.VLookup(wksDES.Cells(Lgn, PremColDes).Value, _
    wksSRC.Range(wksSRC.Cells(PremLgnSrc, PremColSrc), wksSRC.Cells(DerLgnSrc, DerColSrc)), _
    WorksheetFunction.Match(wksDES.Cells(PremLgnDes, Col).Value, wksSRC.Range(wksSRC.Cells(PremLgnSrc, PremColSrc), wksSRC.Cells(PremLgnSrc, DerColSrc)), False), False)
    On Error Resume Next
    Next Lgn
Next Col
Workbooks("DataSource.xlsx").Close savechanges:=False
End Sub

Le souci, c'est que sur mon fichier de test de 4 lignes par 5 colonnes, ça marche du feu de dieu...
Par contre, je ne pensais pas que ça serait aussi long sur ma base complète. Est-ce que vous auriez des conseils pour améliorer mon code afin qu'il gagne en rapidité ?

Merci !!!

@clementmarcotte : Peut-être faut-il que je me passe de la macro et que j'utilise ce que tu m'as dit, mais je ne vois pas comment me servir de ce paramètre. Lorsque je sélectionne mon fichier source après avoir sélectionné Microsoft Query, il ne se passe rien. J'ai un message d'erreur et puis c'est tout... Je vais essayer de regarder un peu plus longuement cette possibilité.

**Michel_M** · 21/01/2015, 09h05

Bonjour,

Déjà, écrire au départ dans ta macro

Code :

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

Application.screenupdating=false

qui figera l'écran et te fera gagner du temps

sur ces grands tableaux, il vaut mieux travailler avec des variables tableaux (voir tuto de Silkyroad) et sans traductions de formules
si tu veux un coup de main il faudrait que tu montres un extrait de ton classeur et que tu définisses exactement ce que tu veux en termes simples

Edit:
le tuto en question
http://silkyroad.developpez.com/vba/tableaux/

**GoToon** · 21/01/2015, 09h24

Ah oui, je n'avais pas pensé à figer l'écran.
Voici une image tirée de mes fichiers d'exemple :
J'ai un fichier (DataSource) contenant des données en vrac, non ordonnées avec en première colonne des références d'articles, en première ligne des caractéristiques liées à ces articles.
L'autre fichier (DataDestination) est une sorte d'extrait du premier : Dans ce fichier, je cherche à sortir quelques caractéristiques pour quelques références données.
Pièce jointe 166821
La macro que je viens d'écrire permet de remplir la cellule B5 de DataDestination avec la valeur de la cellule C5 de DataSource (Elle va chercher par un rechercheV couplé à un Equiv la valeur contenue dans la caractéristique 1 de la référence 1)
La cellule E6 de DataDestination est par exemple remplie avec la valeur de la cellule D3 de DataSource (par un rechercheV de Ref3 dans le tableau A1:E6).

Au final, la macro que j'ai écrit fonctionne parfaitement : on obtient bien les valeurs aux bons endroits, mais le problème c'est qu'elle est très rapide sur mes fichiers d'exemple DataSource et DataDestination (naturellement, au vu du nombre de ligne/colonnes). Le problème c'est que mes fichiers réels font plusieurs centaines de lignes par plusieurs centaines de colonnes. Et là, le temps de traitement est beaucoup plus long....

J'espère que je suis suffisamment clair.

Merci pour votre aide.

**Nico Chg** · 21/01/2015, 10h22

Bonjour,

Effectivement, si tu parcours toutes les cellules de ta plages de données, c'est plutôt long.

Je pense que tu devrais définir un tableau correspondant exactement à la plage de données que tu veux, soit

Code :

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

1
2
3
4
PremColDes + 1 To DerColDes
'Pour la première dimension
PremLgnDes + 1 To DerLgnDes
'Pour la seconde dimension

Ensuite, tu n'as plus qu'a parcourir ton tableau exactement comme tu le fais, et tu verra, ça va aller beaucoup plus vite !

**GoToon** · 21/01/2015, 12h59

Merci, mais je ne vois pas comment le mettre en place.

Je pense avoir compris qu'il fallait que je remplace mes deux boucles For par l'application de mon RechercheV sur le tableau, mais je ne vois pas bien comment faire. Ni pour définir le tableau à proprement parlé.

Je vais essayer de faire des tests, mais je suis preneurs d'un peu plus de détails.

Merci en tous cas !!

J'ai trouvé ça http://excel.developpez.com/cours/

**Nico Chg** · 21/01/2015, 13h56

Re-bonjour,

Oui c'est exactement l'idée, il s'agit du même genre de tutoriel que celui qui t'es proposé plus haut.

Pour passer au variable tableau, il faut, a mon avis, que tu n'utilise pas les WorksheetFunctions, mais plutôt des fonctions VBA.

**GoToon** · 21/01/2015, 14h06

Ah... Et c'est là que le bât blesse... Je ne pense pas avoir les connaissances pour faire mon RechercheV sans passer par les worksheetfunction....
Je continue mon auto-formation sur les tableaux, mais j'ai l'impression que je vais finir face à un mur...

**Marc-L** · 21/01/2015, 14h09

Bonjour,

rien qu'en utilisant les formules de calcul en VBA pour un bloc de cellules contiguës sans boucle
et sans forcément en passer par une variable tableau comme dans cette discussion …

__________________________________________________________________________________________________
Tous unis, tous Charlie

**Nico Chg** · 21/01/2015, 14h14

Pas du tout !

L'utilisation des tableaux est simple, il faut persévérer un peu. Tu peux imager des tableaux simples (1/2 dimensions) comme une feuille Excel, c'est la même chose, mais c'est pas visuel. C'est une autre façon d'écrire, mais le fonctionnement reste similaire. Après, les deux tutoriels sont très bien écrits, simple et illustré.

**GoToon** · 21/01/2015, 14h39

Envoyé par Marc-L

Bonjour,

rien qu'en utilisant les formules de calcul en VBA pour un bloc de cellules contiguës sans boucle
et sans forcément en passer par une variable tableau comme dans cette discussion …
[COLOR=#d3d3d3]

Malheureusement, via ce sujet, je n'arrive pas trop à comprendre la différence avec ma macro actuelle.

Envoyé par Nico Chg

Re-bonjour,

Oui c'est exactement l'idée, il s'agit du même genre de tutoriel que celui qui t'es proposé plus haut.

Pour passer au variable tableau, il faut, a mon avis, que tu n'utilise pas les WorksheetFunctions, mais plutôt des fonctions VBA.

J'ai réussi à paramétrer mon tableau, mais du coup, sans mon rechercheV, comment aller lui dire de chercher telle valeur dans telle colonne ??

Envoyé par Nico Chg

Bonjour,

Effectivement, si tu parcours toutes les cellules de ta plages de données, c'est plutôt long.

Je pense que tu devrais définir un tableau correspondant exactement à la plage de données que tu veux, soit

Code :

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

1
2
3
4
PremColDes + 1 To DerColDes
'Pour la première dimension
PremLgnDes + 1 To DerLgnDes
'Pour la seconde dimension

Ensuite, tu n'as plus qu'a parcourir ton tableau exactement comme tu le fais, et tu verra, ça va aller beaucoup plus vite !

En fait, je ne comprends pas à quoi peut servir de paramétrer un tableau pour mon fichier de Destination. A un moment donné, je vais bien être obligé d'effectuer un recherchev de ma valeur en colonne 1 sur mon fichier Source via une boucle non ?

**Nico Chg** · 21/01/2015, 14h54

C'est bien pour ça que je pense qu'il faut re-coder une fonction vlookup en VBA, mais si tu sais exactement ce que tu cherche, c'est largement faisable, il faut juste décomposer les étapes.

Et quand je dis paramétrer un tableau, c'est créer un tableau avec les valeurs de ton fichier source, faire tes opérations / recherche dans le tableau, et copier les données dont tu as besoin dans ton fichier Destination

**GoToon** · 21/01/2015, 14h59

Chaud.... recoder le vlookup en vba... Je m'y met, mais je vois le mur au loin quand même...

[Edit : Non, très honnêtement, je pense que recoder cette fonction est au-délà de mes capacités...]

Je ne sais pas trop quoi faire... la perspective de diviser par 100 mon temps de calcul était pourtant alléchante.

**Nico Chg** · 21/01/2015, 15h16

Sinon, tu peux faire une simple copie de ta page Source dans ton classeur Destination, et faire un vlookup manuel dans ton classeur Destination?

**GoToon** · 21/01/2015, 15h22

C'est justement l'ancienne méthode que je voudrai éviter. Avant, je faisais une copie de ma feuille source dans mon classeur Destination, puis je répliquai ma formule RechercheV à la main sur toute ma feuille Destination. Cela prenait un temps fou et j’espérai que le fait de passer par une macro puisse accélérer cela, mais au final, c probablement pire qu'en manuel...
Donc la solution c'est peut-être de se coltiner ça à la main à chaque fois oui...

**Philippe Tulliez** · 21/01/2015, 15h44

Bonjour,
Plutôt que d'utiliser RECHERCHEV et EQUIV, la solution serait d'utiliser INDEX combinée à EQUIV.

Illustration faite sur la même feuille à adapter pour deux classeurs.
Nom : INDEX EQUIV.jpg
Affichages : 15810
Taille : 196,1 Ko

Nom : INDEX EQUIV.jpg
Affichages : 15810
Taille : 196,1 Ko

Pour les données telles qu'illustrée ci-dessus.
La formule tapée manuellement est

Code :

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

=INDEX($B$2:$E$13;EQUIV($G2;$A$2:$A$13;0);EQUIV(H$1;$B$1:$E$1;0))

traduite en VBA

Code :

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

=INDEX($B$2:$E$13,MATCH($G2,$A$2:$A$13,0),MATCH(H$1,$B$1:$E$1,0))

Exemple pour une procédure simple à adapter bien entendu pour la rendre dynamique

Code :

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

1
2
3
4
5
6
Dim myFormula As String
myFormula = "=INDEX($B$2:$E$13,MATCH($G2,$A$2:$A$13,0),MATCH(H$1,$B$1:$E$1,0))"
With shtDemo.Range("H2:K4")
.Formula = myFormula  ' Insertion de la formule
.Value = .Value             ' Remplace les formules par leurs valeurs
End With

**GoToon** · 21/01/2015, 16h13

Je regarderai l'histoire de l'index après avoir finalisé le code en l'état, j'ai peur de me perdre sinon.
(Mais je ne connaissais pas cette fonction. Je vais m'y pencher dès que j'aurai terminé ça : c'est sûr)
J'en suis rendu (faute de mieux pour le moment) à faire mon rechercheV à l'intérieur de mes tableaux paramétrés :

Code :

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

1
2
3
4
5
6
7
8
 
For Col = PremColDes + 1 To DerColDes
    For Lgn = PremLgnDes + 1 To DerLgnDes
    TblDes(Lgn, Col) = WorksheetFunction.VLookup(TblDes(Lgn, PremColDes), TblSrc, _
    WorksheetFunction.Match(TblDes(PremLgnDes, Col), EnTetSrc, False), False)
    On Error Resume Next
    Next Lgn
Next Col

Cependant il me fait une "Erreur d'exécution '13': incompatibilité de type" sur

Code :

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

1
2
TblDes(Lgn, Col) = WorksheetFunction.VLookup(TblDes(Lgn, PremColDes), TblSrc, _
    WorksheetFunction.Match(TblDes(PremLgnDes, Col), EnTetSrc, False), False)

Je ne peux pas faire de worksheetfunction sur un tableau ?

**Nico Chg** · 21/01/2015, 16h25

Et bien dans WorksheetFunction, il y a Worksheet, ce qui veux dire que ça correspond à une fonction de l'objet Worksheet, soit un onglet Excel, ou une feuille de calcul, donc non !

Je te conseil d'étudier la solution de Philippe Tulliez, je pense que ça correspond bien à ton problème !