Copier coller via un vlookup
Une version v2 du compactage qui modifie les fichiers source.
Si cela peut simplifier le post-traitement.
edit : fichier corrigé
Il y a un soucis pour le compactage v2, vous perdez la première colonne. Elle était bien la dans le fichier compactage v1
Je regrette de ne plus pouvoir vous être utile car vos code sont autrement plus compliqué que ce que je ne sait faire.
Je vous joint un fichier à moi qui tente vainement de faire ce que vous faites. Il me restait du chemin mais j'avance petit à petit. ce qui me bloque le plus c'est de boucler sur tous les fichiers d'un même répertoire dans mes boucles de copie... Bref Vous êtes tout deux super !!
Attention, conserve bien les originaux, j'espère qu'on n'a pas perdu des infos, car la v2 est dangereuse, elle transforme les fichiers.
Je pense quand même que la v1 est plus intéressante pour ensuite faire des recherchev, quitte à le faire par paquets de 20 fichiers.
Je verrai demain les 2 versions ...
J'avais entamé une réponse, ça fait bien 4 heures maintenant, mais dans ma précipitation je l'ai volatilisée ! Du coup je me suis transféré en cuisine, et je reviens après punch, repas et semi-digestion...
Il y a eu une petite mésentente sur la colonne A.
Ce n'est pas moi qui ai mis l'intitulé de la colonne A: Nom et chemin des fichiers, laissant clairement penser qu'on allait y trouver ces indications. Et qui dit chemin, sous-entend qu'on en aura l'utilité, et son utilité pour un fichier à traiter est naturellement pour l'ouvrir.
Ce qui constituait un point de départ naturel d'une procédure : on traitait les nouvelles inscriptions dans cette colonne pour alimenter les lignes concernées en données provenant du fichier indiqué.
Tu me surprends donc en disant que tu voulais y mettre le nom du fichier extrait;, pour traiter le fichier il faut avoir son nom (ou savoir le trouver). Or aucune autre indication dans tes propos ne fait état de la façon dont le fichier te parvient, hormis cette indication de ton tableau... dont j'avais considéré que, bien qu'implicite, elle constituait une information claire !
Si le démarrage n'est pas censé s'opérer ainsi, tu en as fait l'impasse dans tes indications, et il convient d'apporter les précisions manquantes pour qu'on sache comment on démarre la procédure. S'agissant d'un élément externe au traitement proprement dit, cela peut être relativement secondaire, mais il faut des conditions de démarrage pour démarrer.
mais le code me dépasse un peu !!
Là il faut simplement acquérir quelques rudiments de VBA, je dis bien de VBA, hors commandes d'Excel, soit un nombre limité d'instructions permettant notamment de travailler avec des tableaux qui en font tout l'intérêt.
Si des éléments du code t'échappent ou t'intriguent, il te suffit de demander, je fournirai toutes les explications afférentes.
j'ai oublié une colonne pour l'extraction... la colonne commentaires est importantes aussi.
Ça c'est un peu plus ennuyeux.
Autrement dit, plutôt que de t'étendre largement sur des évocations de pseudo solutions, qui ne sauraient en être qu'une fois mises en place et testées positivement, il était plus pertinent de définir avec le maximum de détails la totalité des données à récupérer...
J'ai défini une méthode visant à ramener les étiquettes (indicateurs de la présence de données à récupérer) en colonne A et les valeurs correspondantes en colonne B. Evidemment les Commentaires y échappent...
Laissons ce point de côté pour l'instant. Dans l'immédiat, je souhaite savoir si la procédure fournie, qui fonctionne sur le fichier test, fonctionne sur d'autres fichiers. Or :
j'ai une erreur dans mes fichiers sources dans la colonne E (qui est fusionnée de E à L) à partir de la ligne 25. Ça me dit que les nombres sont au format texte et que je dois les convertir.
Dans le fichier test, tes nombres sont bien des données numériques, de même dans les deux autres fichiers que tu as communiqués.
D'autre part, je ne vois pas VBA te délivrer un message d'erreur disant que tes nombres doivent être convertis, il te dirait simplement : Erreur 13 - Incompatibilité de type.
Je ne vois pas non plus mon code buter là-dessus, mes variables d'accueil sont de type Variant et accueillent d'ailleurs des données texte concurremment aux nombres. Si les nombres étaient des données de type texte, elles pourraient d'ailleurs être automatiquement converties lors du transfert, et si ce n'était pas le cas on introduirait une conversion.
Mais il serait bon de savoir dans quel contexte s'est opéré ton constat... Et je souhaite toujours savoir si la procédure que j'ai fournie, appliquée à d'autres fichiers à traiter, rapatrie ou non les données prévues (exception faite des Commentaires qui n'était pas dans le coup jusqu'ici).
Cordialement.
Salut Steelson !
Il y a un soucis pour le compactage v2, vous perdez la première colonne.
hé oui, désolé, j'ai été trop vite en reprenant partiellement un code
correction apportée (à faire dans v1 aussi)
version v1 corrigée et complétée
la récup des données se fait ensuite dans le tableau T via INDIRECT
Sub compacter()
Dim donnees() As Variant
Set wbk1 = ThisWorkbook
chemin = ThisWorkbook.Path & "\"
monFichier = Dir(chemin & "*.xls")
Do While monFichier <> ""
If monFichier <> ThisWorkbook.Name Then
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = monFichier
Sheets("RECAP").Cells(2, 1).End(xlDown).Offset(1, 0).Value = monFichier
Set wbk2 = Workbooks.Open(chemin & monFichier)
Cells.UnMerge
donnees = Range(Cells(1, 1), Cells(100, 50)).Value
With wbk1.ActiveSheet
ligne = 1
For i = LBound(donnees, 1) To UBound(donnees, 1)
colonne = 1
nouvelleligne = False
For j = LBound(donnees, 2) To UBound(donnees, 2)
If donnees(i, j) <> "" Then
.Cells(ligne, colonne) = donnees(i, j)
colonne = colonne + 1
nouvelleligne = True
End If
Next
If nouvelleligne Then ligne = ligne + 1
Next
.Cells.EntireColumn.AutoFit
End With
wbk2.Close False
End If
monFichier = Dir
Loop
Sheets("RECAP").Select
End SubBonjour.
MFerrand
J'ai vu votre réponse hier soir mais moi aussi apres quelques verres de rhum j'ai préféré attendre ce matin pour répondre !!
1- Je suis sincèrement désolé pour la colonne A et ma non communication parfaite... Mais finalement c'est très très très pratique comme c'est maintenant. J'incrémente les noms et ça fonctionne super !! Mes limites m'ont fait défaut.
2- Je me rend de plus en plus compte en pratiquant et en m'instruisant du vba de l'outil génial qu'il est !! C'est incroyable la richesse de ce langage et de cet outil avec excel. Je regrette ma nocivité tant j'en aurais eu tellement besoin et compte bien faire les efforts nécessaires afin, peut être un jour, entendre Steelson dire, "les codes de Timothée sont proche de la perfection !!! hahahaha" Non je me doute que je n'y arriverais pas, ou du moins pas de suite.
3- pour la colonne commentaires.... je n'ai pas de mot autre qu'une franche excuse et éventuellement une caisse de vin à votre domicile, pour votre travail et mes excuses !!! (j'ai déjà proposé a Steelson)
4- La procédure fonctionne à merveille !!! Seul hic cette histoire d'erreur de nombre !! Il ne s'agit pas d'une erreur que la vba me donne. Je n'ai du coup aucunes données collées dnas les colonnes calibres car la procédure ne reconnait pas la colonne E ! Et la je dois dire que je ne comprends pas bien pourquoi vous ne l'avez pas vous aussi, nous avons les mêmes fichiers... Il semblerait que Steelson avait vu cette erreur et il y a aussi des gens sur le forum qui ont eu cette erreur ! Vous avez en fichier joint un imp ecran pour mieux comprendre.
Steelson :
La version V2 marche à merveille pour le compactage des fichiers sources.
Il semblerait qu'il y ai un problème dans la copie des données dans le tableau, ou bien c'est moi qui ne comprend pas.. (fortement probable)
Si j'enlève le première ligne Test, les données ne se copient plus... Le compactage et l'ajout d'onglet fonctionne super mais pas le remplissage du tableau recap. Est ce normal ?
Merci à tous les deux !
Il semblerait qu'il y ai un problème dans la copie des données dans le tableau, ou bien c'est moi qui ne comprend pas.. (fortement probable)
il n'y a aucune recopie dans le tableau, que des formules qui tiennent compte de la colonne A (identifiant onglet) et qui permettant d'aller chercher les valeurs dans les onglets.
Regarde justement le cas de TEST
Si j'enlève le première ligne Test, les données ne se copient plus... Le compactage et l'ajout d'onglet fonctionne super mais pas le remplissage du tableau recap. Est ce normal ?
N'enlève TEST qu'à la fin de la procédure
Cette ligne TEST permet de conserver les formules dont les autres lignes hériteront.
Bonjour,
Je vois le problème nombre... Je ne comprenais pas pourquoi cela bloquait, pensant qu'il s'agissait des valeurs, mais il s'agit en fait des étiquettes de calibres. Dans le fichier test ce sont bien des nombres aussi, mais il est sûr que si elles sont en format texte VBA va se cabrer !
On va donc considérer que cela peut être des nombres ou du texte, donc on effectuera la comparaison en convertissant en texte les deux membres comparés :
If IsNumeric(ET(1, n)) Then
For i = 1 To UBound(PlgD)
If CStr(PlgD(i, 1)) = CStr(ET(1, n)) Then
ET(1, n) = PlgD(i, 2): Exit For
End If
Next iNB- Conversion texte avec CStr et non conversion en nombre, car défilant la colonne A pour trouver la bonne étiquette, les autres que calibres étant bien du texte, on aurait une erreur symétrique d'incompatibilité en voulant convertir en nombre du texte non convertissable...
J'ai également fait un ajout pour récupérer le commentaire :
For i = 1 To dln
If .Cells(i, 3) Like "*Commentaire*" Then
.Cells(i, 1).Resize(, 2).Delete xlShiftToLeft
Exit For
End If
Next iLa méthode de compactage (pour employer les mêmes termes que Steelson) que j'utilise consiste à supprimer les lignes vides, et pour les lignes non vides à supprimer toutes les cellules intermédiaires. Ainsi, au terme, les étiquettes (ou intitulés de rubriques) se retrouvent en col. A et les valeurs associées en col. B.
Mais cela ne marchait pas pour les commentaires, n'étant pas seul sur la ligne...
Donc après compactage, le code ajouté va chercher Commentaire dans la 3e colonne, et s'il le trouve supprime les deux cellules qui précèdent de façon à ramener l'intitulé en col. A avec les autres.
Ça fonctionne avec le fichier test, à voir si cela fonctionnera pareillement avec d'autres...
Je n'aime pas trop dans la mesure où se dispositif reste vulnérable, à la merci d'une mention parasite intermédiaire, dont les probabilités sont sensiblement plus fortes que pour les autres. Mais je n'ai pas mieux pour l'instant.
J'essaierai une méthode différente... mais pas le temps de la coder maintenant. Mais si elle donne de meilleurs résultats (fait également le job, et plus rapidement...) je reviendrais avec une autre version.
Dans l'immédiat, le fichier modifié. Une colonne ajouté au tableau (Commentaire) et le code rectifié en conséquence.
Cordialement.
Bonjour,
Ce fut un peu laborieux parce que j'ai dû faire une série de tests sur les données, et que n'étant pas très en forme pour l'exercice, je me suis fait quelques croche-pied...
'Préparation
With .Worksheets(1).UsedRange
.MergeCells = False
dln = .Columns.Count
For i = 1 To .Rows.Count
For k = 1 To .Columns.Count
If .Cells(i, k) <> "" And Not IsDate(.Cells(i, k)) Then
If IsNumeric(.Cells(i, k)) Then
itm = CStr(.Cells(i, k))
If Len(itm) = 2 Or Len(itm) = 3 Then _
d(itm) = .Cells(i, k).Address
Else
itm = Trim(Replace(.Cells(i, k), ":", ""))
d(itm) = .Cells(i, k).Address
End If
End If
Next k
Next i
End With
'Récupération des données
With Worksheets(1)
For n = 2 To UBound(ET, 2)
itm = CStr(ET(1, n))
If d.exists(itm) Then
itm = d(itm): i = 1
With .Range(itm)
Do While i + .Column <= dln
If .Offset(, i) <> "" Then
ET(1, n) = .Offset(, i): Exit Do
End If
i = i + 1
Loop
If i + .Column > dln Then ET(1, n) = Empty
End With
Else
ET(1, n) = Empty
End If
Next n
End WithLa préparation se limite en fait à défusionner pour ce qui concerne la feuille proprement dite. Puis on constitue un dictionnaire en éliminant le plus possible de cellules dont on sait qu'elles ne correspondront pas à des libellés cherchés. Ce qui a l'air de fonctionner assez bien : 56 éléments de dico recueillies sur 160 valeurs de cellules dans le fichier test.
Pour chaque élément dico, la clé est le contenu de la cellule, dépouillé du caractère ":" et des espaces d'extrémités, et la valeur est l'adresse de la cellule.
Pas de compactage donc.
Le recueil des valeurs consiste pour chaque libellé cherché à tester si un élément dico y correspond. Si c'est le cas, on va chercher la valeur : la première qui suit la cellule contenant le libellé (qu'on connait donc), sur le même ligne.
Avec cette méthode on tombe sur une exécution en 250 à 300 millisecondes. Soit on divise par 4 le temps mis par la version précédente. Ce qui me paraît devenir satisfaisant.
Cordialement.
Bonjour
Wouaw !! c'est du travail de génie !! Le code 2 est magnifique, le 1 aussi d'ailleurs même si il est plus lent.
Il y a un petit problème (je pense petit) dans le code 2. En effet, j'ai essayé sur toute ma base de donnée (80 fichiers actuellement) et il ajoute des données pour des calibres inexistants. La code 1 laisse des cases vides ce qui est parfait !
Vous avez une image jointe pour plus de clarté et le fichier source !
Bonne fin de journée
Oh !
End With
d.RemoveAll
Next f
End Subd.RemoveAll à ajouter à la fin de la boucle sur les fichiers (c'est quasiment en fin de procéduré dans le code).
Si on n'élimine pas le dico d'un fichier avant de traiter le suivant, il demeure, et on peut récupérer des valeurs qui n'existent pas dans le nouveau fichier.
NB- les valeurs insérées à tort sont aléatoires, car le dico contient une adresse de cellule qui, prise comme référence sur une autre feuille, donnera un résultat imprévisible.
Il pourra y avoir aussi problème si un nombre non décimal se baladant dans les débuts du fichier correspond à un calibre (comme numéro de lot ou numéro de parcelle... J'ai tablé que dans ce cas il serait remplacé plus bas par le bon calibre, mais si en même temps le calibre est manquant dans le fichier, cela provoquera le même type d'anomalie.
Cela devrait être relativement rare, mais je coderai une parade (dès que possible, car il faudra faire en sorte de ne pas allonger sensiblement le temps d'exécution...)
Cordialement.
C'est impeccable !!! rien à dire !!
MFerrand super excel man !!
J'attends du coup votre complément pour éviter l'erreur d'un nombre dans l’entête. En effet votre prévoyance est superbe !
Merci beaucoup beaucoup beaucoup !!!
respectueusement.
Ce sera pour demain !
Bienvenue au z'oreille en métropole !