Mise a jour d'un tabeau

Bonjour,

j'essaye depuis quelque temps déjà de mettre un tableau à jour, a partir d'un autre tableau.

Je m'explique:

J'ai un tableau d'environ 40000 ligne comprenant des données (symbole, désignation, conditionnement, prix....).

Tout les ans un fichier Excel sort avec les nouveaux tarifs.

J'aimerais à partir d'une macro, cliquer sur un bouton "mettre à jour", et que s'ouvre une fenêtre de choix des colonnes à mettre à jour. Une fois les colonnes sélectionné, je voudrais qu'on me demande ou se trouve ce fichier de mise a jour.

Je précise que la colonne de référence et la colonne "Symbole"

Oula, ça fait beaucoup de répétition ça!!! tempi je laisse pour (essayer d') être plus clair.

J'en demande beaucoup, je sais même pas si c'est possible tous ça.

Est ce que ça l'est?

Je précise que je fais mes débuts en VBA, donc si quelqu'un à une solution, merci de m'expliquer les possibilités et le fonctionnement.

Je joins deux fichier très simplifié pour expliquer ce que je voudrais obtenir.

J'espère avoir été clair, dans le cas contraire n'hésitez pas à me poser des questions.

Je vous remercie d'avance.

Ouhhh ça doit vraiment être compliqué!!

Personne pour m'aider??

Re bonjour tout le monde.

Je reviens à nouveau vers vous car j'ai avancé sur mon fichier. Les mises à jour se font bien dans les colonnes "conditionnement" et "prix".

Ce que je n'arrive pas à faire, c'est rajouter automatiquement les lignes qui manques (nouveaux produit dans le fichier "fichier à jour").

Je mets en pièce jointe, mes deux fichiers. Pour que ça fonctionne, il faut placer le fichier "Fichier a jour.xls" en C:\.

Pouvez-vous m'aider s'il vous plait?

Je vous remercie d'avance.

PS: Je précise que je suis débutant en VBA, donc soyez indulgent SVP

Salut,

Désolé d'intervenir dans ton monologue

cos81 a écrit :

J'aimerais à partir d'une macro, cliquer sur un bouton "mettre à jour", et que s'ouvre une fenêtre de choix des colonnes à mettre à jour.

Si non, pourquoi ne pas nous indiquer simplement quelles colonnes doivent être comparées/actualisées à chaque fois ?

A te relire.

Salut Yvouille,

Merci d'intervenir dans mon monologue!! je commençais à me sentir seul! Mais je comprends que ma demande n'est pas très bien formulé.

En fait, j'ai voulu faire beaucoup trop compliqué pour quelque chose qui pourrait être au final très simple.

C'est pourquoi, j'ai changé d'idée.

En ce qui concerne l'actualisation des colonnes, il s'agit des colonnes conditionnement et prix, ce que j'ai fait dans les derniers fichiers que j'ai posté et qui je pense est fonctionnel.

Maintenant ce que j'aimerai, c'est que toute les lignes (entière) qui sont dans le "fichier à jour" qui ne sont pas dans mon fichier d'origine, soit automatiquement ajouté.

Juste pour info, mon tableau ne sera peut être pas tout les ans fait pareil et toutes les colonnes ne m'intéresse pas, c'est pourquoi, je voulais un choix de colonne à mettre à jour. Finalement, je vais coller les colonnes qui m'intéresses dans le "fichier à jour" avant de cliquer sur le bouton "mettre à jour".

Voilà, j'espère avoir été un peu plus clair! Si tu as des idées, je suis preneur!

En tout les cas, je te remercie de t'intéresser à mon "cas"

Salut,

Je te propose une macro un peu modifiée (simplifiée) qui réalise tes deux contrôles en une fois.

Cordialement.

Salut Yvouille,

Merci beaucoup!! c'est exactement ça!! Il faut juste que je le test sur mon fichier de 40000 lignes en plus, hyper simplifié!! enfin très court! Je t'avoue que je ne comprends pas tout. Si tu as le temps de m'expliquer un peu le code je suis preneur! si tu n'as pas le temps, c'est pas grave, je chercherais sur le net.

Tu vas surement trouver que j'abuse, mais comme je dois faire un copier coller dans mon "fichier a jour", autant copier mes valeurs directement dans une feuille dédié dans le même fichier,non? Dans ce cas, comment je dois procéder? quel ligne dois-je modifier?

Une fois le mise à jour effectué, pour que mon fichier global soit moins lourd, pourrait on effacer toute les lignes à partir de la ligne 8?

Rien ne presse, et si tu n'as pas le temps, je cherche encore de mon coté.

Merci beaucoup pour tout.

Salut,

cos81 a écrit :

Je t'avoue que je ne comprends pas tout. Si tu as le temps de m'expliquer un peu le code je suis preneur!

cos81 a écrit :

......, mais comme je dois faire un copier-coller dans mon "fichier a jour", autant copier mes valeurs directement dans une feuille dédié dans le même fichier,non?

Une question à mon tour : combien de temps durait ta première macro pour 40'000 lignes ? En comparaison, ma nouvelle macro est plus rapide ?

A te relire.

Salut Yvouille,

Ce que je ne suis pas sûre de comprendre, c'est ce code:

DerLig = .Range("B" & Rows.Count).End(xlUp).Row + 1
            Range("B" & i & ":I" & i).Copy .Range("B" & DerLig)

Il copie à la dernière ligne? c'est ça?? mais comment il trouve la dernière ligne?

et ce code:

DerLig = Range("B" & Rows.Count).End(xlUp).Row
Range("B8:I" & DerLig).Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlYes

En ce qui concerne ma demande, c'est exactement ce que je voudrais. Je te met en fichier joint le fichier avec quelques explications.

Je n'avais pas encore testé ma macro sur mon fichier de 40000 lignes. Je l'ai fait ce matin, et surprise mon code ne fonctionne pas!!

j'ai un retour "erreur d'éxecution 6 dépassement de capacité"

Option Explicit

Sub Maj()
    Range("C2").Formula = Now
    Range("C2").NumberFormat = "dd/mm/yyyy" & " " & "à" & " " & "hh:mm"
Dim Chemin As String, i As Integer, j As Integer, k As Integer, Symbole As String
Application.ScreenUpdating = False
j = Range("B50000").End(xlUp).Row

Workbooks.Open Filename:="C:\Fichier a jour.xls"

    For i = 2 To j
        Symbole = Workbooks("Fichier_symbole_en_cours_V2_23-02-14.xls").Worksheets("Base de donées").Range("B" & i)
        Range("B8").Activate
        Do Until ActiveCell = "" Or ActiveCell = Symbole
            ActiveCell.Offset(1, 0).Activate 'Déplacement d 'une ligne vers le bas par rapport à la cellule active
        Loop
        If ActiveCell = Symbole Then
            Workbooks("Fichier_symbole_en_cours_V2_23-02-14.xls").Worksheets("Base de donées").Range("D" & i) = ActiveCell.Offset(0, 2) 'Déplacement de 7 cellules vers la droite par rapport à la cellule active

        End If
        If ActiveCell = Symbole Then
            Workbooks("Fichier_symbole_en_cours_V2_23-02-14.xls").Worksheets("Base de donées").Range("I" & i) = ActiveCell.Offset(0, 7) 'Déplacement de 7 cellules vers la droite par rapport à la cellule active

        End If
    Next i
    ActiveWorkbook.Close savechanges:=False
End Sub

Bon ce n'est pas grave car ton code marche très bien, il prends 1m32s à faire la mise a jour.

Voila, j’espère avoir tout dit.

En te remerciant encore.

@ +

DerLig = .Range("B" & Rows.Count).End(xlUp).Row + 1 et DerLig = Range("B" & Rows.Count).End(xlUp).Row

Une des manière de rechercher la dernière ligne d’une colonne est de remonter depuis une cellule quelconque jusqu’à trouver la première cellule non-vide. Ceci se fait avec l’instruction .End combiné avec (xlup) pour dire que l’on cherche de bas en haut. Dans mon code je pars de la dernière ligne de la version Excel utilisée avec l’instruction Rows.Count (soit 65'000 et quelque pour 2003 ou plus d’un million pour Excel 2007 et suivants). La valeur de la dernière ligne trouvée peut être augmentée de 1 ou non selon que tu as besoin de connaître la dernière ligne d’un tableau (par exemple pour un tri) ou la première ligne vide (pour y ajouter des données) et peut être affecté à une variable pour des utilisations postérieures (dans mon cas à la variable DerLig).

Range("B" & i & ":I" & i).Copy .Range("B" & DerLig)

Ceci est une version simplifiée d’un copier-coller. A gauche, jusqu’à .Copy, la partie copiée, à droite la destination.

Range("B8:I" & DerLig).Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlYes

Ceci est une version simplifiée d’un tri. Jusqu’à .Sort, la partie à trier. Key1 indique la cellule (la colonne) sur laquelle se fait le tri. Header indique s’il y a des titres ou non.

Si besoin de plus de précisions au sujet des codes, reviens à la charge.

Tu indiques que tu joins un fichier, mais il n’y en a pas.

Pour ton code, il semble que ça ne passe pas car tu as limité la valeur de j à 32'768 (Integer). Indique donc dans la déclaration des variables : j As Long à la place de j As Integer.

Amicalement.

Salut,

Merci beaucoup pour toutes ces explications!! j'y vois déjà plus clair.

Je vais essayer d'appliquer maintenant!

Mon fichier est encore loin d’être fini... Je veux faire encore plein de chose compliqué après ça.

Effectivement, j'avais oublié de mettre le fichier. c'est fait maintenant.

J'ai mis "J as long" et ça m'a déplacer l'erreur! J'ai mis "I as long" et la c'est parti!! Mais au bout de 7 minutes d'attentes, j'ai laisser tomber!! le code n'est pas fonctionnel pour un si grand nombre de ligne!!

Je reste sur le tiens.

J’espère avoir tout dit.

A bientôt.

Salut,

Comme il y a déjà un ou deux messages que l’on a parlé de tes nouveaux souhaits, je n’ai tout d’abord pas trop compris ce que tu voulais. Mais bon, tu devrais avoir dans le fichier ci-joint ce que tu désires.

J’ai supprimé ton tri événementiel. Si vraiment il t’était utile, à voir comment le remplacer.

Tes informations manquent de précision. Par exemple tu dis vouloir supprimer certaines données en fin de code, mais tu ne dis pas sur quelle feuille. Dans ce cas-ci, ce n’est pas bien grave, mais pense d’être plus rigoureux ; ça évite toujours des confusions.

Amicalement.

Hello,

Toutes mes excuses pour ce manque de précisions, ce n'est pas toujours facile d'expliquer ce qu'on voudrait.

Merci beaucoup, c'est exactement ce que je voulais. Au début ça ne fonctionnait pas, mais après relecture de ton post, j'ai compris que ça venait de mon tri événementiel. Comme j'en ai besoin pour le reste de mon "programme", j'ai ajouté "Application.EnableEvents = False" au début du code et "Application.EnableEvents = True" à la fin, et la ça fonctionne. c'est la bonne méthode?

J'ai juste encore deux trois questions:

- dans ton code il y a "For i = 8 To j". Normalement si j'ai bien compris, c'est pour faire une boucle. Mais normalement on utilise 1 to 7 par exemple pour répéter la boucle 7 fois, C'est ça? La tu commences à 8 jusqu’à J. Ça veut dire colonne 8 a colonne J??

- au début du code tu as mis "Application.ScreenUpdating = False" mais à aucun moment tu as remis "Application.ScreenUpdating = True" est ce normal?

- Lorsque la mise à jour de mon fichier se fait, après avoir cliquer sur le bouton "mise a jour", un fichier du même nom s'ouvre et ce referme à la fin de la mise a jour. Peux tu m'expliquer?

Voila, je crois avoir fait le tour et t'avoir fait assez perdre de temps.

Je te remercie beaucoup pour ton aide et te suis très reconnaissant.

A +

Selon moi on ne travaille pas sur le même fichier Lorsque tu dis :’ Au début ça ne fonctionnait pas, mais après relecture de ton post, j'ai compris que ça venait de mon tri événementiel’, je ne sais pas exactement ‘ce qui ne fonctionnait pas’ mais dans mon fichier ça fonctionne ! Sois toujours précis lorsque tu donnes des explications.

Egalement ton histoire de ‘fichier du même nom s'ouvre et se referme à la fin de la mise à jour’, je ne vois absolument pas de quoi tu veux parler. Fournis-moi alors ton fichier réel que je puisse y jeter un coup d’œil.

Enfin pour répondre à tes questions :

- Si tu utilises une instruction du genre Application…….., toute ton application Excel restera tel que tu l’as ‘fixée’ à un certain moment et ceci jusqu’à ce que tu appliques l’instruction contraire ou jusqu’à ce que tu refermes Excel. Donc si tu places l’instruction Application.EnableEvent = False, plus aucune macro événementielle ne sera lancée et – comme il y en a relativement souvent dans les fichiers – ça peut être embêtant. Par contre, je n’ai encore jamais vu de macro pour laquelle il est important que l’écran soit réactualisé en milieu d'une macro (attention, je n’ai pas dit que ça n’existait pas) ; je trouve inutile de réactiver cette option en fin de macro. Mais rien ne t’empêche de le faire.

Lorsque tu crées un boucle, c’est toujours bien de se demander de où à où doit aller cette boucle. Si c’est de la colonne 31 à la colonne 40, autant inscrire For i = 31 to 40 et de pouvoir utiliser directement la valeur de i dans le code sans correction au lieu d’écrire For i = 1 to 10 puis d’écrire dans le code i + 30 pour faire référence à la 31ème colonne la première fois. Pour ton besoin, il faut boucler de la ligne 8 à la dernière ligne (valeur attribuée précédemment à j), alors autant écrire For i = 8 to j plutôt que d’écrire for i = 1 to j – 7 puis d’ajouter 7 à i afin d’obtenir 8 lors de la première boucle.

Au plaisir de voir ton fichier.

Salut Yvouille,

En effet, le fichier que je mets sur le forum et un fichier très simplifié de mon fichier d'origine. Donc à chaque fois que tu me transmet un fichier modifié, je l'adapte à mon "vrai" fichier.

Le fichier que tu m'as transmis fonctionnait en effet parfaitement. Mais lorsque j'ai mis ton code Dans mon "vrai" fichier, cela ne marchait plus, je lançais la macro et le sablier tournait indéfiniment. Je pense à cause de mon tri qui est d'origine sur la feuille" base de données."

Pour le fichier du même nom qui s'ouvre et ce referme: ce que je voulais dire c'est que lorsque je clic sur le bouton pour lancer la macro, dans la barre des tâches Windows s'ouvre un nouveau fichier excel du même nom que celui sur lequel je travail, puis une fois la mise à jour effectué, il se referme.

Voilà j'espère que c'est plus clair pour toi. En tout cas tes explications sont clair. Merci, j'ai compris.

Pour mon fichier, je ne peux pas le mettre en ligne car ce sont des données de mon entreprise, mais si tu es d'accord je peux te l'envoyer en privée.

A bientôt.

cos81 a écrit :

mais si tu es d'accord je peux te l'envoyer en privée.

Oui, je serais quand même intéressé à savoir ce qu'il en est exactement.

A te relire ..... entre 4 yeux

Alors voici le résultat de mes constatations :

Si je prends le fichier tel que tu me l’as envoyé en privé – avec les macros événementielles enclenchées – ça tourne presque à l’infini.

J’ai ensuite enclenché l’instruction Application.EnableEvent = False et j’ai placé un MsgBox chaque 100 itérations de la boucle For i = 8 To j et là, oh surprise, ça prend environ 70 secondes sur un ordinateur moyennement puissant. En calculant pour tes 20'400 lignes, ça prendrait donc 4 heures ! Je présume que sur un PC légèrement plus puissant on pourrait réduire cette durée au quart, mais quand même. J’avoue avoir un peu de peine à comprendre.

J’ai quand même essayé de réduire ton fichier en supprimant toutes les feuilles qui ne s’appellent pas ‘Base de données’ ou ‘Données à jour’ et – oh bonne surprise – le même code prend alors 90 secondes sur ma vieille bécane pour 20'400 lignes. Doit-on en conclure que le reste de ton fichier ralenti le code à mort ? Je n’ai jamais entendu parler d’un tel phénomène, mais ce n’est pas impossible.

Donc la solution à ton problème de lenteur serait-elle de sortir tes deux feuilles sur un autre fichier juste le temps de faire la mise à jour ? Tout ceci pourrait bien entendu être automatisé. Par exemple on pourrait imaginer que tu aurais un fichier ‘Mise à jour’ dans lequel tu collerais tes données comme sur la feuille ‘Données à jour’, puis la macro irait chercher la feuille ‘Base de données’ dans le fichier de base, l’actualiserait dans ce fichier séparé puis la replacerait dans le fichier de base ??

A toi de dire.

Autrement je n’ai pas du tout ce phénomène du fichier du même nom qui s’ouvre et se referme chez moi, avec aucun de mes essais !!!! Je ne peux donc absolument pas te dire de quoi il s’agit. Désolé.

Cordialement.

Bonjour Yvouille,

Désolé pour le temps de réponse, je n'ai pas trop eu le temps aujourd'hui.

Merci pour tes conclusions!

Par contre il y a quelque chose que je ne comprends pas dans ce que tu as écrits.

En ayant enclenché l'instruction application.enableevent= fasse dans mon fichier? chez moi ça tourne pendant 1m30 environ puis ça s'arrête! Est tu entrain de me dire que la mise à jour de mon fichier n'a pas été faite?

Ce qui pose problème pour moi, c'est que sur la feuille base de données, j'ai mis un code qui me sert a trier mon tableau automatiquement des qu'on entre une valeur dans la cellule de la colonne prix et qu'on appuie sur entré. Dans ton code il y a également un tri automatique! C'est donc la qu'est le conflit non?

C'est pour cela que j'avais rajouté l'instruction application.enableEvent=false pour que le code (trie) de ma feuille base de données soit désactivé lors du clic sur le bouton mise à jour.

D'ailleurs, j'avais fais le test de mise à jour en enlevant d'abord le code qui est sur ma feuille base de données et c'est la que j'ai découvert que ça marchais ainsi.

Est ce que j'ai juste ou je suis à coté?

A bientôt

Salut,

Selon moi, si ton code tourne pendant une minute et demi et qu’il s‘arrête normalement, c’est qu’il a été au bout de son travail. Par contre s’il tournait indéfiniment ou qu’il bloquait clairement (alors tu dois débugger), ça serait autre chose.

Maintenant, par sécurité, tu peux faire un essai en contrôlant au plus proche les valeurs de la première et de la dernière cellule à modifier (si nécessaire après y avoir inscrits des montants bien différents) et tu verras bien si ça joue.

En ce qui concerne ton code événementiel Private Sub Worksheet_Change sur la Feuil1, il ne se déclenche pas à chaque fois que tu entre une valeur dans la colonne prix, mais à chaque fois que tu modifies quoi que ce soit sur cette feuille. Puis le code contrôle si la modification a été faite sur la colonne prix et effectue des opérations complémentaires – en fait un tri - le cas échéant.

Donc à chaque fois qu’une modification est effectuée sur la feuille par la macro Maj – si l’instruction Application.EnableEvents = False n’y a pas été inscrite au départ - la macro Private Sub Worksheet_Change est déclenchée, ce qui ralenti considérablement le temps d’exécution, mais c’est tout.

Sans l’instruction Application.EnableEvents = False, si le code Maj effectue 20'000 boucles, le tableau est trié 20'000 fois en cours de route et une fois de plus à la fin de la macro Maj. Il n’y a donc pas d’interférence entre ces codes, juste une suite de tris inutile et aberrante.

Cordialement.

Salut,

J'ai bien fait le test, et quand Application.EnableEvents = False est ajouté dans le code, les prix sont bien mis à jour, donc ca fonctionne très bien!! Mais, parce qu’il y a toujours un mais!! Je viens de trouver un autre problème! la colonne conditionnement ne se met pas toujours a jour!!

Symbole = valeur de référence dans la colonne B

Conditionnement = colonne D

Je m’explique: Lors de la mise à jour, lorsque une valeur dans la colonne symbole existe déjà dans ma feuille base de données, la valeur de la colonne conditionnement n'est pas mis a jour, mais si le symbole n'existe pas dans ma base de données, la, le conditionnement est mis a jour.

Je ne vois nul part "D" dans le code, hors il devrait apparaitre vu que c'est la colonne D le conditionnement (Qui devrait également être mis a jour). non?

Dois je rajouter D dans cette ligne?

.Range("B" & i & ":I" & i).Copy Range("B" & DerLig) 

A bientôt.