Comparaison de deux tableaux sur des fichiers séparés
Bonjour m'sieurs dames !
Après m'être contenté de lire les cours VBA du site (ce qui m'a d'ailleurs bien aidé, merci à l'admin au passage), et avoir fouiné sur le forum (ce qui m'a encore plus aidé, merci aux fanas de VBA aussi) pour certains projets que je devais réaliser, je profite de cette matinée pluvieuse (chez moi, en tout cas), pour venir poser ma première (petite ?) question sur ce forum. M'envoyez donc pas sur le bûcher si je fais pas ça dans les règles de l'art, hein !
Donc, ci-joint, deux fichiers. Il faut imaginer que en réalité, j'aurai plusieurs fichiers sources (de peut-être 2000 lignes chacun), un seul database (mais de p'tet 13 000 lignes). J'aimerais créer un script VBA dans le fichier source (que je pourrai utiliser plus tard dans les autres sources). Son fonctionnement ?
1) Je prends la première référence de source, et je recherche si elle existe dans database.
2) Si oui, je prend la ligne de source (soit ici le prix en plus, en vrai y'aura d'autres données), et je remplace la ligne de cette référence dans database (pour actualiser le prix s'il a changé, dans mon cas).
3) Si non, je rajoute la ligne de source à la fin de database.
4) Même principe jusqu'à finir de checker les références de source.
De ce que j'ai pu essayer, mon problème est double. Primo, vu le grand nombre de ligne, trouver un script assez rapide. J'ai déjà bricolé quelques lignes, que je peux au besoin fournir, mais ça risque d'être extrêmement lent sur de grandes plages. Ce qui m'amène au deuxième problème : il s'agit de deux fichiers séparés, ce qui fait que même avec Application.ScreenUpdating=False, si à chaque fois je dois sélectionner une ref, changer de fichier, vérifier, repasser sur source, copier la ligne, coller dans database, on va littéralement y passer des heures. De ce que j'ai pu voir, il ne faudrait pas faire de boucles sur les cellules (ce que j'ai en fait jusqu'à présent..), mais plutôt se servir d'un tableau mis en mémoire tampon, mais là, je suis pommé.
Voilà, j'espère ne pas en avoir effrayé plus d'un avec ma demande !
Merci d'avance à ceux qui passeront et reviendront avec quelques lignes de code.
Manti'
[EDIT] 9:31, je viens de voir dans les topics similaires proposé en bas du post un topic datant de 2011 que je n'avais pas vu, j'espère ne pas avoir écrit ça pour rien
[EDIT2] 11:32, je viens de finir un script VBA fonctionnel . Pour simplifier, source et database sont dans un seul fichier, sur une seule feuille. J'arrive à faire ce que je veux, mais problème : ce sont des boucles, donc sur des milliers de lignes, cela va vraiment prendre des heures, et j'ai utilisé un GoTo, chose que j'aimerais si possible éviter. Si besoin, je peux rajouter le fichier aux pièces jointes.
Bonjour
si j'ai bien compris voici un code a mettre dans le fichier database et a lancer
il y a deux, trois lignes a paramétré en début de code pour l'adaptation a une plage plus grande
reste a voir une instruction que je n'arrive pas a trouver c'est de coller un ligne spécifique de la variable tableau dans une ligne d'une feuille de classeur.. pas trouver de solution pour le moment donc passage par une boucle for pour le moment
a+
fred
Option Explicit
Sub compare_remplace()
Dim tab_S() As Variant
Dim i As Integer
Dim lig As Integer
Dim plage As Range
Dim Plg_database, Plg_source As String
Dim adresse
Dim Fname As String
'plage a adapter en conséquence
Plg_database = "A:B"
Plg_source = "A1:B"
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx), *.xls", Title:="Choisir le fichier Source,", MultiSelect:=False)
If Fname = "" Then Exit Sub
'ouverture du fichier
Workbooks.Open Fname
'mise en mémoire de la plage de données dans une variable tableau
Set plage = Sheets(1).Range(Plg_source & Sheets(1).[A65536].End(xlUp).Row)
tab_S = plage.Value
'fermeture du fichier source sans enregistrement
ActiveWorkbook.Close False
'libération de la mémoire
Set plage = Nothing
With Sheets(1)
For i = 2 To UBound(tab_S,1)
'recherche sur la plage des colonnes A:B du fichier database
Set adresse = .Range(Plg_database).Find(What:=tab_S(i, 1), LookAt:=xlPart)
'suivant le cas ou la référence a été trouvée ou non
If Not (adresse Is Nothing) Then
'si référence trouvée
lig = adresse.Row
Else
'si reference non trouvée on va ajouter la référence en bout de tableau
lig = .[A65536].End(xlUp).Row + 1
End If
'recopie des donnnées sans le fichier database
Range("A" & lig).Resize(, UBound(tab_S, 2)) = Application.Index(tab_S, lig)
Next i
End With
'libération de la mémoire
Set adresse = Nothing
End SubTout d'abord, merci pour la réponse rapide, je ne sais pas exactement combien de temps il t'a fallu pour écrire ce script. Je l'ai lu, je l'ai compris (à quelques lettres près^^), je l'ai essayé. Au début, j'ai même remplacé "Choisir le fichier Source", au début, par le chemin exact du fichier, jusqu'à voir que tu m'ouvrais une fenêtre me demandant de choisir le fichier. Moi qui m’apprêtais à rajouter une inputbox pour spécifier le chemin... Même pas besoin. Ton idée de passer dans database est encore plus simple que d'insérer ce code dans chacun des fichiers sources.
Sinon, ça me semble fonctionner parfaitement !
Des rapides essais que j'ai pu faire, rien à signaler, il actualise database sans problème. Niveau rapidité, c'est instantané, la boucle for de copie sur la ligne, très courte, ne devrait pas trop poser de problèmes. Moi sur mon essai avec les deux listes sur la même feuille, j'avais deux boucles do while imbriquées, avec un label, et puis la longue méthode range(XX).select, selection.copy, range(YY).select, selection. paste (tout ça pour chacune des milliers de cellules^^) Et puis une boucle if aussi, sinon c'est pas drôle. Autrement dit, beaucoup plus long.
En résumé, un grand merci fred, je pense que c'est exactement ce qu'il me fallait ! Donc si tu ne me revois pas pointer le bout de mon nez ici, c'est que je n'ai pas de problèmes en situation réelle que ce code bah, est parfait !
re
je viens de modifier le code dans le premier post en supprimant la dernière boucle For pour le collage des infos car trouvé la solution pour coller directement une ligne d'un tableau de plusieurs colonnes ce qui devrait encore accélérer les choses
une dernière chose que l'on pourrait faire c'est de passer par deux variables tableaux pour que cela soit encore plus rapide....
mais pas encore fait ...; et je dois commencer a faire mes bagages pour partir en vacances
je verrais si j'ai le temps....
a+
fred
Edit : pour optimiser le temps d'execution d'un code, il faut absolument éviter les range.select, range.copy, range.paste.....
La deuxième méthode semble marcher aussi, donc aucun problème. Après, même si les milliers de lignes commencent à prendre du temps, j'ai la possibilité de laisser tourner un ordi pour cette tâche, donc pas trop de problèmes de ce côté là, je pense qu'il faudra un sacré paquet de lignes pour que ça tourne des heures.
Et ouai, les select, copy, paste, c'est long. Mais c'est pas ça qui aurrais pris du temps. Si tu veux rire, ce que je faisais :
1) prendre une ref de source
2) parcourir toutes les refs de database avec une boucle do while.
3) modifier ou copier à la fin (avec ma longue méthode^^)
4) prendre la deuxième ref de source
Donc 10 ref parmi une database de 30, ça passe, mais 10.000 parmi 30.000, j'aurais pu avoir un problème.
Fin bref, voilà comment résoudre en (insérer ici temps de travail), ce sur quoi je bossais depuis deux jours complets.
euh... moins d'une demi journée quand même... j'ai passé plus de temps a chercher comment faire pour éviter la dernière boucle for qu'a faire proprement dit le code "de base" car copier une ligne spécifique d'un tableau a plusieurs dimensions c'était quelque chose que je n'avais jamais ... enfin pour te donner un ordre de grandeur du temps passé sur ce morceau de code 1/2H, 45 minutes
a+
et bonnes vacances si tu en as car moi je pars cette fin de semaine
fred
Re bonjour, Fred.
Je me permets de repasser vite fait dans le coin. J'ai donc testé ton programme en conditions "réelles". Jusqu'à quelques milliers de lignes (avec quelques colonnes de plus), le code marche sans problème. Il fait son boulot, copie bien comme il faut, ou modifie ce qu'il faut. Néanmoins, quand on arrive vers 2000 lignes dans source et 10 000 dans database, certaines lignes ne sont pas copiées (ou modifiées), sans que je trouve de logique à ces exclusions. Et puis, à la fin du tableau, il apparaît (parfois) une plage remplie de #REF (pas de rapport entre le nombre de lignes de #REF et celui de lignes non copiées/modifiées)
Puisque le programme marche très bien jusqu'à un certain niveau, il n'a pour moi aucune raison d'être en cause. J'opterais plus pour une limite de mémoire, de capacité de copie, ou quelque chose dans le genre... Vu que tu as écris ce code, tu aurais une petite idée sur le sujet ?
bonjour
en effet je rechercherais d'abord dans ton fichier sources,... est tu sur qu'il n'y a pas de #ref dedans ??
pour qu'une ligne ne soit pas mise a jour je verrais bien un problème de casse :
par exemple dans un fichier tu as une espace de plus a la fin du nom rechercher dans le deuxième fichier la ligne ne sera pas trouvée et donc pas mis a jour
autre chose je test la fin du fichier source sur la colonne A c'est bien elle qui a le nombre de max de lignes ?
voilà quelques pistes a voir ...
fred
Merci pour la réponse, je vais aller vérifier ça en détail. Concernant les #REF, je suis sûr qu'il n'y en a pas dans la feuille source.
Sinon, je pense plus que c'est la colonne A, mais de database, qui aura le nombre max de ligne. Se pourrait-il que cela vienne de là ?
PS : typiquement, voilà ce que qui se passe, voir les PJ, quand j'actualise database. J'y avais supprimé certaines lignes avec ref commençant par C, mis partout fabricant à "Fab 0", poids à 0 et code prod à "TVRCX". Après actualisation, C070, par exemple, n’apparaît pas. Pas fais gaffe si d'autres manquaient à l'appel cette fois. En revanche, j'ai du C4871 à 4970 qui est apparut deux fois. Et dans la colonne poids, les valeurs ne sont plus les bonnes.
corrige cette ligne
Range("A" & lig).Resize(, UBound(tab_S, 2)) = Application.Index(tab_S, lig)en
Range("A" & lig).Resize(, UBound(tab_S, 2)) = Application.Index(tab_S, i)les # reference vienne de là....
Déjà un problème de moins ! Bon, sinon, il semblerait que cela soit bon... J'ai supprimé toute la database, sauf les refs en y, et ça m'a rajouté ce qu'il fallait. Ensuite, suppression des codes prod, prix et poids, ça m'a bien rajouté ce qu'il fallait. Essais avec suppression de deux lignes dans database, et rajout d'une autre dans source, elles sont bien réapparues à la fin.
Plus de problèmes de décalage de valeur, les plages de grandes taille semblent passer. Plus de #REF à la fin. J'espère donc pouvoir te laisser partir en vacances.
Encore merci pour l'aide apportée !
Vivement les vacances
comme plus de connexion internet, c'est ma femme qui va être contente...
je vais arrêter d’être devant mon ordinateur...
a+fred