Modification sur 2 fichiers

Version imprimable

05/05/2010, 09h57
hantran

Modification sur 2 fichiers

J'ai 2 fichier très volumineux.

Par exemple le 1er contient 100 colonnes. Le 2ème en contient 30, ce sont les 30 colonnes les plus importants dans le 1er fichier. L'ordre des colonnes ne sont pas les même.

Je veux écrire un code VBA qui sert à modifier de façon automatique le 2ème fichier, lorsque je fais un modif dans le 1er.

Par exemple : ce sont les données de ventes, avec un order_id comme identifiant.
Quand je modifie le Pays de l'order_id 123456 dans le fichier source (le premier fichier), le code va chercher le même numéro d'order dans le 2ème fichier et va modifier le Pays.

Avez-vous une idée ?

Je pense à fais plusieurs VLOOKUP, en passant par une feuille de paramètre qui contient tous les noms de colonnes avec leur lettres. Je fais un InputBox pour demander l'order_id, ensuite un InputBox pour demander quelle modif sur quelle colonne. Et en fonction de ces 3 données entrées je fais les VLOOKUP sur les feuilles correspondantes pour corriger les données. Mais ça me paraît assez lourde comme solution.
05/05/2010, 12h21
Gorfael

Salut hantran et le forum
Ouais, ça me semble lourd aussi :mrgreen: !!!
VLOOKUP, pour moi, si je ne me trompe, RechercheV, est une fonction de feuille de calcul. Pourquoi ne pas utiliser une instruction VBA, comme .Find ?

Plus tu es moins précis et clair, moins les réponses pourront plus t'aider.
Pas d'adresse plage, pas de nom précis, pas d'algorithme exhaustif. Et tu attends ?

- Utiliser worksheet_Change qui se lance automatiquement
- Faire une recherche des cellules modifiées (lignes/colonnes) du 1er dans le 2ème, au travers de l'identifiant, trouver la colonne à modifieret changer la valeur de la ligne.
A+
05/05/2010, 12h45
Didier Gonard

Bonjour,

Citation:

Par exemple le 1er contient 100 colonnes. Le 2ème en contient 30, ce sont les 30 colonnes les plus importants dans le 1er fichier. L'ordre des colonnes ne sont pas les même.

Ce qui serait important, c'est de savoir :
- si ton deuxième fichier n'est que le reflet du premier et est donc finalement en lecture seule et ne reçoit ses modifs que par les changements du premier.
- l'ordre des colonnes est différents, mais l'ordre des lignes lui-est-il le même

Comme toutes les choses "lourdes", c'est la phase conception qui est la plus importante sinon....

PS : 100 colonnes = pas forcément lourd... combien de lignes ?
cordialement,

Didier

bonjour hantran et à tous,

tu pourrais utiliser l'événement change de la feuille du classeur 1.
par exemple avec un tableau de correspondance des 30 colonnes
(ici j'en ai mit 20 pour l'exemple)
Code:

1 2 3 4 5 6 7 Private Sub Worksheet_Change(ByVal Target As Range) col = Array(1, 2, 3, 5, 7, 9, 12, 15, 18, 20, 22, 24, 25, 26, 30, 42, 45, 55, 60, 62) If Not IsError(Application.Match(Target.Column, col, 0)) Then adresse = Cells(Target.Row, Application.Match(Target.Column, col, 0)).Address MsgBox " la cellule correspondante de l'autre fichier est : " & adresse End If End Sub

07/05/2010, 11h53
hantran

Salut Gorfael et sabzzz, merci pour la solution Worksheet_Change, je vais regarder.
@Gorfael : le .Find fonctionne pas très bien, il me semble, si on a des formats assez complex dans nos celllules (genre des dates, des tirets "-", etc...)

Salut Ormonth, pour répondre à tes questions, oui le 2ème fichier ne reçoit les modifs que par les changements du premier. Et l'ordre des lignes n'est pas non plus le même.

En fait, avant, ces deux fichiers ne sont qu'un. J'ai mis les 100 colonnes à gauche, et à droite les 30 colonnes "résumés". Et grace aux formules d'Excel qui lient les colonnes entre eux, quand je fais un modif sur une ça se met à jour automatiquement pour l'autre. Alors que là c'est deux fichiers à part, et dans le 2ème, je ne veux pas avoir de formules dans les cellules, mais juste les valeurs.

Comme c'est une base de données pour les ventes d'une grosse boîte, le nombre de lignes s'augmente des dizaines de milliers chaque quarter. Et ce sont des fichiers qui date depuis 2007. ;)

J'aimerais trouver une solution / une macro rapide et efficace qui ne prend pas 5 min pour s'exécuter.

L'objectif est de ne rien toucher au fichier "résumé". On modifie seulement la source.

J'en profites pour vous poser une question tout bête : pouvez-vous m'expliquer ce que c'est les Modules / Class Modules et à quoi ils servent ? Car pour le moment j'ai fait plusieurs macros d'analyses, je les ai mis tout dans un sheet "Macro", et dans l'éditeur VBA ils sont tous au même endroit. Maintenant je veux le diviser en plusieurs modules, chaque module concerne un type de macro. J'ai essayé mais les macros ne tournent plus quand je les sépares.
07/05/2010, 12h23
Didier Gonard

Bonjour,

Citation:

J'en profites pour vous poser une question tout bête : pouvez-vous m'expliquer ce que c'est les Modules / Class Modules et à quoi ils servent ? Car pour le moment j'ai fait plusieurs macros d'analyses, je les ai mis tout dans un sheet "Macro", et dans l'éditeur VBA ils sont tous au même endroit. Maintenant je veux le diviser en plusieurs modules, chaque module concerne un type de macro. J'ai essayé mais les macros ne tournent plus quand je les sépares.

Une règle = 1 question par Fil ;) , c'est mieux vu que des gens qui ne s'intéressent pas à celui en cours peuvent être capable de répondre à cette question. (et surtout pour les recherches ensuite..)

Donc ouvre un nouveau fil en ayant regardé dans les tutos pour les définitions de module et en étant un peu plus explicite pour tes besoins de division avec une balise code et un exemple ;)

cordialement,

Didier
07/05/2010, 12h29
Didier Gonard

Bonjour,

Citation:

En fait, avant, ces deux fichiers ne sont qu'un. J'ai mis les 100 colonnes à gauche, et à droite les 30 colonnes "résumés". Et grace aux formules d'Excel qui lient les colonnes entre eux, quand je fais un modif sur une ça se met à jour automatiquement pour l'autre. Alors que là c'est deux fichiers à part, et dans le 2ème, je ne veux pas avoir de formules dans les cellules, mais juste les valeurs.

Pourquoi se casser la tête ?, ne serait-il pas possible de faire tes 30 colonnes sur un feuille xx de ton premier fichier avec les valeurs qui varient via les liaisons, comme au départ, et à chaque évènementiel définit par tes soins, tu sauvegardes cette feuille en écrasant à chaque fois l'ancien fichier ?

cordialement,,

Didier
07/05/2010, 12h45
hantran

Citation:

Pourquoi se casser la tête ?, ne serait-il pas possible de faire tes 30 colonnes sur un feuille xx de ton premier fichier avec les valeurs qui varient via les liaisons, comme au départ, et à chaque évènementiel définit par tes soins, tu sauvegardes cette feuille en écrasant à chaque fois l'ancien fichier ?

Oui c'est surement possible de faire mais on aura un fichier assez lourd qui prend du temps à se charger à chaque fois qu'on l'ouvre, en plus avec des macros, des pivot tables dedans ça fait un peu le bordel, on a eu des soucis pour faire générer deux pivot tables en même temps, etc... Du coup on a décidé de séparer en deux fichiers. En plus, le 2ème fichier va être updater avec les données venant des autres bases de données qui sont les mêmes types mais différent formats et compagnie...

Je sais c'est casse tête mais j'y peux rien c'est pas moi qui décide de faire comme ça.
07/05/2010, 12h52
Didier Gonard

Citation:

11 h 53
Salut Ormonth, pour répondre à tes questions, oui le 2ème fichier ne reçoit les modifs que par les changements du premier

Citation:

12h45
En plus, le 2ème fichier va être updater avec les données venant des autres bases de données qui sont les mêmes types mais différent formats et compagnie...

tu ne nous facilite pas la tâche ;)

Didier
07/05/2010, 13h13
hantran

Oui, c'est ça. J'ai peut être pas tout à fait compris ce que tu voulait dire mais en tout cas ce que je veux dire c'est en ce qui concerne les modifications de la source, on modifie dans la source et non pas dans l'autre fichier. Après on a forcément des choses en plus à faire avec l'autre fichier, pas seulement les modifs.

Bref, c'est pas un problème qu'on va pouvoir résoudre du jour au lendemain.

Cordialement
07/05/2010, 13h36
Didier Gonard

Tout ça c'est encore une orientation de conception à la base...

Bon si tu veux gérer des données sur différentes tables, il te faut sur les deux fichier un N° de clef commun.

Au pire si vous ne touchiez par à l'ordre des lignes ça pourrait être une table de correspondance, mais ce n'est pas le cas..

Donc dans tes 30 colonnes, 1 doit contenir un id commun avec la source.

ça par recherche sur 1 colonne, ça te donne ton N° de ligne dans ton fichier 2.

Ensuite, pour un fichier de données, tu dois avoir des en-têtes de lignes, de façon cohérente, elle doivent être identiques à la source, en affectant aux en-têtes des noms identiques sur les 2 fichiers, ça te donnera le N° de colonne dans ton fichier 2

Du coup tu repères ta cellule en x,y, vu que la cellule correspondant à un id et à une en-tête donnée peut changer d'adresses, mais pas de nom de ses axes...

cordialement,

Didier

Merci, des recherches sur des lignes et colonnes c'est à peu près la solution que je pensais faire, le n° clef pour les deux fichiers sera sans doute l'ORDER_ID, mais j'ai utilisé des InputBox et des VLookup comme je disais en haut, c'est moyen... Ensuite pour éviter qu'on modifie tout et n'importe quoi il faut mettre en place les tests pour que les données qu'on rentre soient cohérentes avec les données existantes etc... Je penses à une liste déroulante contenant les valeurs possibles, l'utilisateur n'a qu'à en choisir une, au lieu de taper, pour éviter les erreurs. Mais pour mettre la solution en place, je ne sais pas encore.

Code:

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
Sub test_input()
 
   myOrder = Application.InputBox(prompt:="Quote #", Title:="Modif sur quel Quote # ?", Type:=1)
 
   Do
   myColumn = Application.InputBox(prompt:="Colonne", Title:="Modif sur quel colonne ?")
 
   If Len(myColumn) <= 1 Then
      msg = MsgBox("Entrez le label de la colonne, pas la lettre", vbCritical)
   End If
 
   Loop While Len(myColumn) <= 1
 
   myColumnLetter = Application.WorksheetFunction.VLookup(myColumn, _
                    Sheets("parametre3").[A:B], 2, False)
 
   myColumnNumber = Sheets("ppr").Range(myColumnLetter & 1).Column
 
   On Error Resume Next
 
   MsgBox "Valeur actuelle du Order numéro " & myOrder & " dans la colonne " & myColumn & " est " _
           & Application.WorksheetFunction.VLookup(myOrder, Sheets("ppr").Range("A:" + myColumnLetter), myColumnNumber, False)
 
   If Err.Number > 0 Then
 
      MsgBox "Erreur VLookup"
 
   End If
 
   On Error GoTo 0
 
   myValue = Application.InputBox(prompt:="Entrez la modification", Title:="Modification", Type:=2)
 
   colA = Sheets("ppr").Cells(Cells.Rows.Count, "A").End(xlUp).Row
 
   For i = 1 To colA Step 1
      If Sheets("ppr").Range("A" & i).Value = myOrder Then
         myLine = i
      End If
   Next
 
   Sheets("ppr").Range(myColumnLetter & myLine).Value = myValue
 
End Sub