Résolu VBA, création d'une macro de mise à jour

[popeyem]

Bonjour à tous, au forum,

Alors voila, ça fait pas mal de temps que je suis les forums pour trouver des solutions à de menus problèmes sous excel ou autre. Cela à tout le temps été efficace, en cherchant on trouve quasiment tout le temps ce que l'on veut et même plus quelques fois.

Outil pratique, ce partage de connaissances est bénéfique pour tous! Merci à ceux qui le font vivre.

Mais là.. j'en appelle aux connaissances des chevronnés (ou pas) du VBA pour la construction d'une macro somme toute assez simpliste dans l'idée mais qui me pose des problèmes quant à sa programmation. Je suis débutant et je précise que j'ai envie d'apprendre et de comprendre surtout.

Donc, pour résumer:
- un erp me produit 10 rapports à la demande, sous format excel, correspondant à 10 pays différents pour les chiffres d'un mois.
- J'ai besoin de prendre les données de ces rapports (sans les ouvrir si possible) pour les coller dans un classeur Analysis contenant 11 onglets, dont 10 contiennent chacun les chiffres d'un pays. La structure de destination est la même que celle du fichier source pour eviter un trop gros travail.
- Je dois coller des plages discontinues (Une date, soit une cellule, le nom du pays soit une autre cellule et enfin la plage de valeurs)
- Le but etant que chaque mois, je n'ai qu'a appuyer sur un bouton MAJ que je vais parametrer pou r que les chiffres arrivent.

J'aimerais donc trouver un code pour
1. Si le mois choisi est celui voulu (=si le mois du fichier source est le mois choisi dans le rapport Analysis en onglet 11, alors colle-moi les valeurs, sinon mets moi un message d'erreur parametrable)

Ci-dessous un bout de code pour 1 pays seulement:

[cpp]Sub MiseAJour()
'la macro MiseAJour permettra de mettre à jour, en fonction du mois, les données extraites du systeme dans un rapport excel.
'Le but etant d'eviter le copier/coller manuel de tableaux (structure fixe dans le fichier de destination)



'Copie de la date de mise à jour LAM

Workbooks.Open("\\Fr\...\SourcePays1.xls").Sheets("Sheet1").Range("$A$25").Copy
Workbooks("Analysis.xls").Sheets("Pays1").Activate
ActiveSheet.Paste Destination:=Range("$B$5")

With Range("$B$5")
.Font.ColorIndex = 3
.Font.Size = 8
.HorizontalAlignment = xlLeft

End With

'Copie du nom du pays

Workbooks("SourcePays1.xls").Sheets("Sheet1").Range("$B$10").Copy
Workbooks("Analysis.xls").Sheets("Pays1").Activate
ActiveSheet.Paste Destination:=Range("$B$8")

'Copie/collage (spécial) depuis SourcePays1 des valeurs du tableau pour Analysis, feuille Pays1.

Workbooks("SourcePays1.xls").Sheets("Sheet1").Range("$B$12:$H$24").Copy
Workbooks("Analysis.xls").Sheets("Pays1").Activate
ActiveSheet.Paste Destination:=Range("$B$10:$H$22")

End Sub[/cpp]


Pour l'instant mes 2 premiers pb sont:
- au niveau de la copie du nom pays, ce sont des cellules fusionnées tant dans la source que dans la destination. Donc erreur
- Je vais avoir plusieurs plages de cellules, du genre B12:H24, J1224, R12:X24 à copier coller. Utilisation de Union(Range("..."), Range("...")) ?

Enfin, je dois faire ça pour 10 rapports differents donc pour les 10 onglets de mon fichier Analysis..... Unn moyen de structurer tout ça ??

J'ai peur que ce soit lourd à traiter et finalement un peu compliqué à faire..
Toute critique et proposition sont les bienvenues.

Merci bien! (surtout d'avoir été jusqu'au bout!)

Nicolas

 

[zeb]

Bonjour et bienvenue,

Merci de lire et de respecter le règlement. Utilise la balise

. Pour modifier ton message, clique sur l'une des deux petites icônes représentant une feuille et un éclair en ba à droite de ton message.
[:zeb:6]

 

[popeyem]

Bon peut-etre que c'est un peu brouillon mon explication. :??:

J'ai fait des fichiers (1source, 1analyse) simplifiés de mon pb pour une meilleure vue, je peux les mettre sur une plateforme de telechargement?

Merci

 

[zeb]

Non Popeye. Pas de téléchargement. Imagine qu'un salaud se fasse passer pour un gentil forumiste et qu'il nous envoie une macro-qui-marche-pô du genre

system("format c:")

-----------------------------------------

C'est très facile ce que tu demandes ! Et en plus, tu as déjà fais 80 % du boulot. Mais maintenant, on va faire les choses proprement.

D'abord, dis-toi bien que pour lire un fichier, il faut l'ouvrir !
Ensuite, on ne va surtout pas se baser sur le fait que tel classeur est actif, que telle cellule est sélectionnée. Puis, on va éviter de pourrir le presse papier de l'utilisateur avec nos données.

Ça donne :

Dim wb_source As Workbook
Dim wb_target As Workbook

Set wb_source = Workbooks.Open("\\Fr\...\SourcePays1.xls" )
Set wb_target = Workbooks("Analysis.xls" )
	
' // Copier les valeurs d'une cellule vers une autre
wb_target.Worksheets(1).Range("B5" ).Value = wb_source.Worksheets(1).Range("A25" ).Value
wb_target.Worksheets(1).Range("B8" ).Value = wb_source.Worksheets(1).Range("B10" ).Value

' // Pour copier les valeurs d'une plage, soit on les fait une à une, quitte à faire une boucle, soit :
wb_source.Worksheets(1).Range("B12:H24" ).Copy Destination:=wb_target.Worksheets(1).Range("B10:H22" )

 

[popeyem]

oui on ne sait jamais, mais excel demande si on veut activer/desactiver les macro à l'ouverture, non?

80% du taf: pas mal!
ok, alors en fait, je n'osais pas utiliser Dim parce que je ne comprend psa trop sa structure ..
[cpp] Dim une variable As qqch (texte, nb, objet(?)[/cpp]
Mais comment l'utiliser et la faire marcher ?!

Donc du coup c'est vrai que ça faisait pas forcement propre!

Merci Zeb de ces éclairages, je vais me pencher dessus

 

[zeb]

Ben si tu désactives les macros, tu n'y a plus accès. Donc aucun intérêt. Et puis TON Excel est correctement configuré et TE pose les bonnes questions, mais chez les autres ? Donc par principe, pas de téléchargement. Et puis ta question et les réponses vont rester ici. Quid de ton site d'upload ? Pour que tout cela reste cohérent, pas de téléchargement.

---------------

Dim, c'est pour déclarer ses variables. Je préconise (pour ne pas dire j'ordonne, je veux et j'exige...) l'utilisation de la clause Option Explicit qui impose que toutes les variables soient déclarées. VB est de base un langage bien trop permissif :

Dim nom_de_la_variable As type_de_la_variable

 

[popeyem]

Je voulais dire: desactiver pour visualiser le code et voir ce genre d'entourloupe ! Bref, c'est pas le sujet:

a la lumière de ce que tu m'a donné, j'ai pu un peu avancer et voici à quoi j'arrive, mais je bloque sur un truc:

[cpp]Sub MiseAJour()

Dim wb_source As Workbook
Dim wb_target As Workbook

Set wb_source = Workbooks.Open("d:\Mes documents\SourcePays1.xls")
Set wb_target = Workbooks("Analysis.xls")

' // Integration de la condition: le mois choisi doit correspondre avec le mois du rapport source

If wb_target.Worksheets(1).Range("H1").Value <> wb_source.Worksheets(1).Range("B8") Then
Select Case MsgBox("Les données source ne correspondent pas avec le mois choisi. Choisir à nouveau le mois correspondant.", vbOKCancel)
Case vbYes

Case vbNo
wb_source.Close
wb_target.Close

End Select

Else
' // Copie de la date de mise à jour

wb_target.Worksheets(2).Range("B5").Value = wb_source.Worksheets(1).Range("A25").Value

With Range("$B$5")
.Font.ColorIndex = 3
.Font.Size = 8
.HorizontalAlignment = xlCenter
End With

' // Copie des noms de régions correspondant aux bonnes données (par mesure de sécurité)

wb_target.Worksheets(2).Range("B8").Value = wb_source.Worksheets(1).Range("B10").Value
wb_target.Worksheets(2).Range("J8").Value = wb_source.Worksheets(1).Range("J10").Value
wb_target.Worksheets(2).Range("R8").Value = wb_source.Worksheets(1).Range("R10").Value

' // Pour copier les valeurs d'une plage, soit on les fait une à une, quitte à faire une boucle, soit :

wb_source.Worksheets(1).Range("B12:H24").Copy
wb_target.Worksheets(2).Range("B10:H22").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb_source.Worksheets(1).Range("J1224").Copy
wb_target.Worksheets(2).Range("J1022").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb_source.Worksheets(1).Range("R12:X24").Copy
wb_target.Worksheets(2).Range("R10:X22").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb_source.Close

End If

End Sub[/cpp]

Pour ma MessBox j'aimerais que le clic YES me permette de me retrouver sur le classeurAnalysis, feuille 1, curseur sur la cellule choix du mois (H1).. J'imagine pas trop compliqué mais je trouve pas gd chose à ce sujet.

Et deuxiement, je suis parti pour ouvrir et refermer non pas 1 fichier source mais 10, plutot lourds donc ca va mouliner..
Y a t il deja un moyen pour organiser ca mieux, cad mettre dans chaque feuille ce qui est propre à chacune (ouvrir source-copier-coller dans analysis- fermer) en faisant un "appel" pour toutes les feuilles depuis module1??

A te lire

 

[zeb]

Moi, je veux bien d'aider à ne pas faire lourd, mais dans ce cas, commence par faire ce que je te propose. Ne pourris pas le presse-papier, n'utilise pas Copy/Paste mais Copy Destination:= (Lignes 41 à 47).

Ligne 26 de ton code, tu ne précises pas à quelle feuille et de quelle classeur doit appartenir la cellule B5. C'est source d'erreur. Surtout que tu veux par ailleurs activer et sélectionner telle cellule de telle feuille de tel classeur.

Sois précis, ligne 11, tu compares la valeur de H1 avec B8. Précise pour B8 qu'il s'agit aussi de la valeur.

Pour te retrouver sur le classeurAnalysis, feuille 1, curseur sur la cellule choix du mois (H1), c'est facile :

wb_target.Activate
wb_target.Worksheets(1).Activate
wb_target.Worksheets(1).Range("H1").Select

Si tu utilises 50 fois dans ton code la même feuille, tu peux définir une variable pour cette feuille :

Dim ws_source_1 As Worksheet
Dim ws_target_2 As Worksheet

...

Set ws_source_1 = wb_source.Worksheets(1)
Set ws_target_2 = wb_target.Worksheets(2)

...

ws_source_1.Range("R12:X24" ).Copy Destination:=ws_target_2.Range("R10:X22" )

 

[popeyem]

Salut Zeb,

Moi, je veux bien d'aider à ne pas faire lourd, mais dans ce cas, commence par faire ce que je te propose. Ne pourris pas le presse-papier, n'utilise pas Copy/Paste mais Copy Destination:= (Lignes 41 à 47).

J'aimerais bien !! j'ai laissé cette forme car je n'arrive pas encore à faire de collage spécial (collage de valeurs seulement) en utilisant copy destination:= :sweat: !

Ligne 11 et ligne 26 -> corrigées.

Sinon je suis en train de repenser l'architecture et en fait, :
- pour chaque feuille "pays" je ne laisse qu'une macro (MAJ1, MAJ2,..) qui s'occupe juste de coller les valeurs dont j'ai besoin entre le fichier source et mon fichier "analysis".

- J'appelle ces macros (MAJ1, MAJ2) donc depuis la feuille 1 de mon classeur analysis en mettant des conditions (idéalement j'aimerais faire une boucle, mais c'est là que je bloque) voici ce que j'ai commencé mais la je sature et n'arrive pas à trouver les arguments nécessaires pour poser ces conditions.
Toute idée est bienvenue !

[cpp] Option Explicit

Private Sub ButtonMAJ_Click()

Dim wb_target, pays As Workbook
Dim mois_target, Chemin As String

Set wb_target = Workbooks("Analysis.xls")
Set mois_target = wb_target.Feuil1.Range("H1").Value

Chemin = ThisWorkbook.Path
ChDir Chemin
pays = Dir("SourcePays*.xls")

Select Case MsgBox("Etes-vous sûr de vouloir charger les données de" & " " & mois_target & "?", vbYesNo)
Case vbYes

For Each pays In pays.Path 'le dossier contenant les fichiers source, je ne sais pas comment l'appeler

Workbooks.Open (pays)
If Val(mois_target) <> Val(pays.sheets(1).Range("B8").Value) Then
MsgBox "Le mois choisi ne correspond pas avec les données du rapport !", vbExclamation
Workbooks(pays).Close


Else
'Call la macroMAJx, relative au pays en question
'(ex. Call Feuil3.MiseAJour2)
'MsgBox "Vos données ont bien été mises à jour pour " & mois

End If

Next

Case vbNo

End Select

'Set wb_source1 = Workbooks.Open("d:\Mes documents\SourcePays1.xls")
'Set wb_source2 = Workbooks.Open("d:\Mes documents\SourcePays2.xls")
'moisx = Workbooks("SourcePays*.xls").sheets(1).Range("B8").Value

End Sub [/cpp]

Merci bien!

 

[popeyem]

En me relisant je vois que certaine ligne de codes n'auraient pas du etre prises en compte, le cas de :
ligne 39 à 42.
ligne 29 devrait venir en ligne 34 je pense. Sorry !!!

 

[zeb]

On ne touche pas au presse-papier. C'est un principe ! Imagine que tu y ais collé des infos de toute première importance, et qu'une fichue macro qui tourne à ce moment-là supprime ce précieux contenu pour y mettre des données temporaires :/

' // Pas beau : fait appel au presse-papier
wb_source.Worksheets(1).Range("B12:H24").Copy
wb_target.Worksheets(2).Range("B10:H22").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
' // Cool
Dim c As Range
For Each c In wb_source.Range("B12:H24")
  wb_target.Cells(c.Row - 2, c.Column).Value = c.Value
Next

Alors, c'est joli, non ?

En plus, si j'ai une zone plus importante, pas de problème :

For Each c In wb_source.Range("B12:H24,J12:P24,R12:X24")
  wb_target.Cells(c.Row - 2, c.Column).Value = c.Value
Next

:sol:

Quant à parcourir le contenu d'un répertoire pour y trouver des fichiers, je ne vais pas te recopier ici l'aide de VB. [:zeb:4] Regardes-en l'exemple, il est clair. (Le coup du For Each était bien vu, mais la fonction Dir est un archaïsme du Basic, et ça ne marche pas comme ça. Dommage.)

 

[popeyem]

héhé c'est encore moi!

Alors, c'est joli, non ?

En plus, si j'ai une zone plus importante, pas de problème :

Code :

For Each c In wb_source.Range("B12:H24,J1224,R12:X24" )
wb_target.Cells(c.Row - 2, c.Column).Value = c.Value
Next


:sol:

Corrigé!

Apres j'arrive toujours à rien mais j'aimerais faire
(avant que j'arrete de me prendre la tete avec cette boucle t1cable: !)

En parlant de VBA (je précise!)
Trouve moi l'emplacement avec mes fichiers. Trouve moi les fichiers "SourcePays*.xls" et ouvre les tous, un par un
- Prend moi le premier
- controle de la concordance des mois
- Si ok, appelle moi macro correspondant à ce pays et copie colle
(sans utiliser le pressepapier of course!)
- Si pas ok, msg box ("mois pas ok"), ferme et passe au prochain
- Prend moi le deuxieme
- Bis Repetita jusqua ce que a plus

Je bloque sur Si ok appelle moi la macro correspondant au fichier source ouvert (Call MiseAJour1 si SourcePays1, Call MiseAJour2 si SourcePays2)

je n'arrive pas à placer de variable spécifique pour executer cela

Un ptit coup de pouce serait bienvenu. Merci

 

[zeb]

-Trouve moi l'emplacement avec mes fichiers. Trouve moi les fichiers "SourcePays*.xls" et ouvre les tous, un par un
- Prend moi le premier

Meuh non !

- Trouve moi les fichiers "SourcePays*.xls"
- Ouvre le premier.
- Fait ce que doit
- Recommence avec le suivant.


Bon, et puis je n'ai rien compris à tes histoires de macro MAJ.
Où sont-elles ?
A quoi servent-elles ?
Pourquoi ne pas leur donner un nom générique ?

 

[popeyem]

les macros MiseAJour sont les macros contenues dans chaque feuille "pays" et qui executent seulement un copier / coller de la date, et plage de valeurs (les fameuses - pour les plages valeurs- :

[cpp]Code :

For Each c In wb_source.Range("B12:H24,J1224,R12:X24" )
wb_target.Cells(c.Row - 2, c.Column).Value = c.Value
Next
[/cpp]

Voila en gros comme je le disais, j'ai une macro (Feuil1) qui en appelle d'autres (Feuil2, 3, ...) selon le pays "étudié" et le tout dans le meme classeur. Clair ou pas?

Meuh non !

- Trouve moi les fichiers "SourcePays*.xls"
- Ouvre le premier.
- Fait ce que doit
- Recommence avec le suivant.

Bon maintenant j'ai plus qu'a traduire en VBA.. C'est qui le patron ici?!!!

 

[zeb]

Bon. Tu as une feuille Analyse (cible) et n feuilles Pays (sources).

Soluce n°1 (++)
Au lieu de n macros, tu en utilises une seule, au niveau de la feuille cible, voire au niveau du Workbook. Evidemment, cette macro sera paramétrée avec la feuille source.

Sub MAJ(r_source As Range, ws_target As Worksheet, dRow As Long, dCol As Integer)
    Dim c As Range
    For Each c In r_source
        ws_target.Cells(c.Row+dRow, c.Column+dCol).Value = c.Value
    Next
End Sub

Soluce n°2 (--)
Toutes tes macros s'appellent pareil ([atch]). Et tu fais :

Run "Feuil2.pareil"

 

[popeyem]

j'arrive pas à faire marcher ta macro, doit manquer qqch au niveau de la ligne 4, non?

 

[zeb]

.....

Message édité par zeb le 28-04-2010 à 16:50:07

Pas du tout. Il marche très bien mon programme

 

[popeyem]

Salut Zeb,

Aors je vais integrer ta macro après. Pour l'instant je bloque encore et toujours sur ma boucle. En fait rien ne se passe, aucun fichier ne s'ouvre :??: . J'ai tout changé 5 - 6 fois en essayant des choses différentes mais rien n'y fait. Pourrais-tu jeter un coup d'oeil en dessous ?

[cpp]Option Explicit

Private Sub ButtonMAJ_Click()

Dim wb_target As Workbook
Dim chemin, mois_target, wb_sourcex, mois_sourcex As String

'Définit le répertoire contenant les fichiers
chemin = ActiveWorkbook.Path

'Boucle sur tous les fichiers xls du répertoire.
wb_sourcex = Dir(chemin & "SourcePays*.xls")

Set wb_target = Workbooks("Analysis.xls")
Set mois_target = Workbooks("Analysis.xls").sheets(1).Range("H1")

Select Case MsgBox("Etes-vous sûr de vouloir charger les données de" & " " & mois_target & "?", vbYesNo)
Case vbYes

Do While Len(wb_sourcex) > 0
Workbooks("SourcePays*.xls").Open
If ActiveWorkbook.Range("B8").Value <> mois_target.Value Then
MsgBox "Le mois choisi ne correspond pas avec les données du rapport !", vbExclamation
Workbooks("SourcePays*.xls").Close
Else
Call Feuil2.MiseAJour1
End If
wb_sourcex = Dir()
Loop
MsgBox "Vos données ont bien été mises à jour pour " & mois_target
Case vbNo
End Select
End Sub[/cpp]

Merci !

 

[zeb]

Ohla !

Dim wb_target As Workbook
Dim chemin, mois_target, wb_sourcex, mois_sourcex As String

wb_target est un classeur.
chemin, mois_target, wb_sourcex sont des Variants
mois_sourcex est une chaîne de caractères.

C'est ce que tu voulais ?

Moi, j'aime pas les Variants
¯¯¯¯¯¯¯¯¯¯¯¯¯/¯¯¯¯¯¯¯¯¯

Vous devez être connecté pour voir les images.

wb_sourcex = Dir(chemin & "SourcePays*.xls" )
Do While Len(wb_sourcex) > 0
  Workbooks("SourcePays*.xls" ).Open
  wb_sourcex = Dir()
Loop

Non mais tu vas corriger toi-même des erreurs aussi grossières ! (ligne 3 [:zeb:4])

Bon, pis ça, c'est horrible :

Workbooks(wb_sourcex).Open
ActiveWorkbook...

Si par malheur le classeur ouvert ne passe pas en premier plan, ou si ce n'est pas la feuille 1 qui est ouverte en premier, ça ne marche pas.
Alors on écrit les choses correctement :

Dim wb_source As Workbook

Set wb_source = Workbooks(wb_sourcex).Open
If wb_source.Worksheets(1).Range("B8" ) ...

Bon, pis le Call ne sert pas à grand'chose. Mais si tu trouves que c'est joli, je n'ai rien dis

A part ça, nickel

 

[popeyem]

Ah j'aime bien entendre ça !!
Mais ca ne marcheee point :sweat:

voila ce que j'ai corrigé
[cpp]Dim chemin, mois_target, wb_sourcex As Variant
Do While Len(wb_sourcex) > 0
Set wb_source = Workbooks(wb_sourcex).Open
If wb_source.sheets(1).Range("B8").Value <> mois_target.Value Then
MsgBox "Le mois choisi ne correspond pas avec les données du rapport !", vbExclamation
Workbooks("SourcePays*.xls").Close
Else
Call Feuil2.MiseAJour1
End If
wb_sourcex = Dir()
Loop[/cpp]

toujours le blanc complet, rien ne s'ouvre