Résolu Excel : Macro qui me résiste

  • Auteur de la discussion pepito78
  • Date de début
P

pepito78

Nouveau membre
Bonjour à toutes et à tous,

J'ai un travail de développement de macro et je suis totalement novice en la matière. Jusqu'à présent j'ai réussi à me nourrir de l'expérience de différents forumeurs et j'ai plus ou moins réussi à atteindre mon objectif, il y a juste un détail que je ne m'explique pas.

Je vous résume en gros mon projet : je dois créer une macro qui est capable de copier une colonne d'un groupe de fichiers .xlsm qui ont tous la même architecture, pour ensuite la coller dans un fichier "recueil de données". Une des difficultés pour moi était donc d'indiquer à la macro de coller les différentes colonnes aux bons endroits, c'est à dire en décalant à chaque fois la cible. J'ai donc écrit ce code qui s'exécute à l'ouverture d'un fichier "statistiques" :

Code: 
Private Sub Workbook_Open()

Application.ScreenUpdating = False ' cache l'exécution du script
ActiveWorkbook.Worksheets("recueil").Activate

' Initialisation
' --------------
Cells.Delete                                'supprime le contenu de toutes les cellules
Range("A1") = "Sexe"                        'inscrit les titres de lignes
Range("A2") = "nombre d'ateliers"
Range("A3") = "nombre de A"
Range("A4") = "nombre de B"
Range("A5") = "nombre de C"
Range("A6") = "nombre de D"
Range("A7") = "nombre de E"
'.... je vous passe le détail car il y en plus de 200 comme ça

Cells.EntireColumn.AutoFit                  'règle la largeur de colonne automatiquement

JaunePale = 13434879                        'donne une couleur à la colonne
Range("A:A").Interior.Color = JaunePale
Range("A:A").Font.Bold = True               'caractères en gras pour la colonne A

' Parcours de tous les fichiers
' -----------------------------

Dim chemin As String
chemin = ActiveWorkbook.Path                'définit chemin comme l'endroit où se trouve le fichier excel

ChDir chemin & "\2016"                      'choisit de travailler dans le répertoire 2016

ClasseurPatient = Dir(chemin & "\2016\*.xlsm")      'détermine les fichiers concernés par la suite du programme
While Len(ClasseurPatient) > 0                      'prend les fichiers un par un
    Workbooks.Open ClasseurPatient                  'ouvre le fichier
    ActiveWorkbook.Worksheets("recap").UsedRange.Cells(1, 3).EntireColumn.Copy      'copie la troisième colonne
    Workbooks("Statistiques 2016.xlsm").Activate         'active l'onglet où le collage se fera
    ActiveSheet.UsedRange.Cells(1, ActiveSheet.UsedRange.Columns.Count + 1).EntireColumn.Select     'sélectionne la colonne où le collage se fera
    ActiveSheet.Paste                               'colle la sélection
    Application.CutCopyMode = False                 'sort du mode couper/coller (pour éviter message presse papier)
    Workbooks(ClasseurPatient).Close True           'ferme le fichier, true signifie qu'il choisira "enregistrer" à la demande
    ClasseurPatient = Dir                           'revient sur le répertoire source
Wend

' Fin des travaux

Cells.EntireColumn.AutoFit                          'règle la largeur des colonnes

Sheets("recueil").Select                            'sélectionne la feuille "recueil" et copie tout pour coller dans "recueil bis"
Columns("A:ZZ").Select
    Selection.Copy
    Sheets("recueil bis").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False                   'collage spécial pour ne prendre que les valeurs (pas de formules)
    Application.CutCopyMode = False
    Range("A1").Select
ActiveWorkbook.Worksheets("recueil").Activate       'replace les onglets et le curseur
Range("A1").Select
ActiveWorkbook.Worksheets("statistiques").Activate
Range("A1").Select

End Sub

Ce qui m'intrigue, c'est qu'à l'ouverture du fichier "statistiques", le collage des colonnes se fait effectivement avec le décalage de +1 comme indiqué, sauf pour la toute première colonne à coller. Pour celle-ci, il tient compte des précédentes ouvertures de fichiers et décale tout vers la droite. En gros, comme j'ai déjà fait plusieurs essais, la première colonne collée arrive maintenant sur la colonne AA et tout les reste s'affiche correctement sur la droite.

Quelqu'un serait-il capable de m'expliquer ça? Et surtout comment y remédier? C'est un détail car au final, j'arrive à extraire mes statistiques, mais bon après quelques mois d'utilisation, mes colonnes se colleront en ZZ...

Merci d'avance pour vos éclairages!


 
drul

drul

Obscur pro du hardware
Staff
Ben c'est normal, en utilisant "UsedRange +1" il va forcément allé à la première colonne vide, comme tu ne vide jamais ta feuille "recap", à chaque ouverture, il va recopier les données a la suite ...
vide ta feuille recap avant la boucle while et ça devrait fonctionner.
N.B. ta macro est pleine d'horreur, si tu le veux on peut nettoyer tout ça ...
Un truc m'inquiète: tu arrête le screenupdating au début ta macro (c'est généralement très mauvais signe quand au code qui suit ...) mais tu ne le réactive jamais ...
 
P

pepito78

Nouveau membre


Bonjour drul et merci pour votre réponse.

Il me semblait que la ligne "cells.delete" permettait justement de tout supprimer. C'est en tout cas ce que j'observe quand je fais le débogage pas à pas. Et d'ailleurs après avoir lancé la macro, les anciennes colonnes sont vides et les premières colonnes où le copier/coller a fonctionné sont au niveau de AA (pour l'instant, mais ça se décale à chaque redémarrage)

Je me doute bien que cette macro est très laide pour quelqu'un qui maîtrise la programmation. C'est bien pour vous en avertir que j'ai précisé que j'étais novice. J'ai appris l'existence des macros en faisant celle-ci en fait. Quoiqu'il en soit je suis preneur de conseils pour la nettoyer! Mais svp, accompagnés d'explications car tant qu'à faire, j'aimerais bien en profiter pour apprendre un peu et comprendre ma macro!

Quant au screenupdating, je l'ai arrêté simplement parce que je voulais éviter que toutes les fenêtres s'ouvrent et se ferment en la lançant. J'ignorais qu'on devait le réactiver et à vrai dire je n'ai pas encore vu en quoi ça me dérange pour la suite...
 
drul

drul

Obscur pro du hardware
Staff
Meilleure réponse
Hum effectivement, j'avais raté ...
Mouais donc j'essayerais une autre approche:
Code: 
Private Sub test()

Dim DestSheet As Worksheet
Dim DestCell As Range
Dim SourceFile As Workbook
 
'Application.ScreenUpdating = False ' cache l'exécution du script

Set DestSheet = ActiveWorkbook.Worksheets("recueil")

 
' Initialisation

' --------------


With DestSheet
    .Cells.Delete                            'supprime le contenu de toutes les cellules
    .Range("A1") = "Sexe"                        'inscrit les titres de lignes

    .Range("A2") = "nombre d'ateliers"

    .Range("A3") = "nombre de A"

    .Range("A4") = "nombre de B"

    .Range("A5") = "nombre de C"

    .Range("A6") = "nombre de D"

    .Range("A7") = "nombre de E"

'.... je vous passe le détail car il y en plus de 200 comme ça

 
    .Cells.EntireColumn.AutoFit                  'règle la largeur de colonne automatiquement

 
    JaunePale = 13434879                        'donne une couleur à la colonne

    .Range("A:A").Interior.Color = JaunePale

    .Range("A:A").Font.Bold = True               'caractères en gras pour la colonne A
End With
 
' Parcours de tous les fichiers

' -----------------------------
Set DestCell = DestSheet.Cells(1, "B")
 
Dim chemin As String

chemin = ActiveWorkbook.Path                'définit chemin comme l'endroit où se trouve le fichier excel

 
ChDir chemin & "\2016"                      'choisit de travailler dans le répertoire 2016

 
ClasseurPatient = Dir(chemin & "\2016\*.xlsm")      'détermine les fichiers concernés par la suite du programme

While Len(ClasseurPatient) > 0                      'prend les fichiers un par un
    Set SourceFile = Workbooks.Open(ClasseurPatient)
    
    SourceFile.Worksheets("recap").Cells(1, 3).EntireColumn.Copy DestCell     'copie la troisième colonne
    Set DestCell = DestCell.Offset(0, 1)
    ClasseurPatient = Dir                           'revient sur le répertoire source
    SourceFile.Close
    'Set SourceFile = Nothing
Wend

 
' Fin des travaux

 
Cells.EntireColumn.AutoFit                          'règle la largeur des colonnes

 
Sheets("recueil").Select                            'sélectionne la feuille "recueil" et copie tout pour coller dans "recueil bis"

'Columns("A:ZZ").Select

'    Selection.Copy

'    Sheets("recueil bis").Select

 '   Range("A1").Select

 '   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False                   'collage spécial pour ne prendre que les valeurs (pas de formules)

 '   Application.CutCopyMode = False

 '   Range("A1").Select

ActiveWorkbook.Worksheets("recueil").Activate       'replace les onglets et le curseur

Range("A1").Select

'ActiveWorkbook.Worksheets("statistiques").Activate

'Range("A1").Select

 
End Sub

Si tu as des questions, je t'explique avec plaisir ;)
N.B. je deteste les activate et autre select, de plus je préconise de TOUJOURS spécifié l'objet sur lequel on travail.
N.B. 2: Je n'ai travaillé que jusqu'au wend, raison pour laquelle le reste est en commentaire.
 
P

pepito78

Nouveau membre


J'ai recopié tel quel votre code et en effet le problème est résolu. C'était peut-être enfantin pour toi mais bien joué quand même! Je me suis permis d'ajouter un "True" après le SourceFile.Close car les fichiers sources ont une petite macro en fermeture donc demandent systématiquement d'enregistrer. Alors maintenant place à ma liste de questions ;) (je t'avais prévenu!) :

- j'ai vu qu'il y avait des points devant certaines lignes de code, ça a une importance?
- quand tu dis que tu préconises de spécifier l'objet sur lequel on travaille, tu parles des "Dim" et des "Set" que tu places en début?
- qu'est-ce que ça change de mettre un "with"?
- si j'ai un peu compris c'est la commande DestCell.Offset(0, 1) qui décale chaque copie d'une colonne vers la droite. C'est quoi la différence avec UsedRange?
- et enfin la question qui tue... comment expliques-tu que mon code faisait décaler les copies à chaque démarrage? Parce que moi je n'ai toujours pas compris...

Bonne soirée!
 
drul

drul

Obscur pro du hardware
Staff
quelques réponses demain, la dernière restera un mystère je le crains ...
 
drul

drul

Obscur pro du hardware
Staff
Alors:
Les "." devant les méthodes sont lié à l'utilisation de "with", cela permet d'éviter de taper mille fois le nom d'un objet.
Code: 
with Sheets(1)
  .range("a1").value=1
  .range("a2").value=2
end with
'peut aussi s'écrire:
Sheets(1).range("a1").value=1
Sheets(1).range("a2").value=2
' c'est une méthode pour alléger un peu le code

Quand je parle de spécifier les objets, c'est parce que lorsque l'on jongle avec plusieurs Sheets et/ou Workbooks, il est très fréquent de se planter en pensant que tel sheet est active alors qu'en fait c'est une autre. Les "dim" et les "set" sont là pour faciliter le code et éviter de devoir taper des références trop longue à chaque fois ...

Offset et UsedRange n'ont pas grand chose en commun en fait ... UsedRange te retourne la plage utilisée dans une feuille, par contre j'ai l'impression qu'il ne gèrait pas très bien l'effacement dans ta macro, pour une raison que je ne m'explique pas. Offset(i,j) retourne une cellule qui i ligne et j colonne plus loin.

Quand à la question qui tue, j'ai deux pistes, soit UsedRange (je ne l'utilise jamais, donc je connais mal son comportement) gère mal l'effacement, soit il y avait un problème de référence: à force de changer de sheet et de workbook on s'y perd parfois ...

N.B. toujours à dispo pour développerun peu se que j'ai expliqué ci dessus ;)
 
drul

drul

Obscur pro du hardware
Staff
Une question:
As-tu d'autres colonnes avec de la couleur ? Si oui, Cells.Delete n'efface pas le "formatting" et UsedRange considère une cellule avec de la couleur comme utilisée.
Donc il est normal qu'a l’exécution il reparte après la dernière cellule non "blanche".
Essaie de remplacer cells.delete par cells.clear dans ta macro originale, ça devrait aussi fonctionner
 
P

pepito78

Nouveau membre
Bonjour,

Pour répondre déjà à la question, non il n'y avait aucune cellule de couleur dans ma feuille. J'ai pu constater que Cells.Delete n'effaçait pas les couleurs mais ça ne me gênait pas que la couleur reste puisque l'architecture était toujours la même. Mais tu as raison, après changement de Cells.Delete par Cells.Clear, le problème est résolu aussi. Alors qu'est-ce qui cloche avec Cells.Delete?? En tout cas, merci pour le tuyau et pour toutes les explications. J'ai beaucoup appris en faisant cette macro (forcément en partant de zéro...) et je t'en dois une partie!

Bonne continuation!
 
drul

drul

Obscur pro du hardware
Staff
Un commentaire, une mise en forme, un cadre, ... ??? En tous cas quelque chose resistait au cells.delete.

Heureux que tu aies pu apprendre. Si tu as de nouveau besoin de conseil repasse par ici ;)

Tu mainteant clore le sujet si tu es satisfait (Sélectionner comme meilleure solution)
 
P

pepito78

Nouveau membre
Oui apparemment quelque chose résistait au Cells.Delete mais j'ignore encore quoi. En tout cas, je vais prendre l'habitude d'utiliser Cells.Clear histoire d'être sûr que ça nettoie bien!

Sinon, j'aimerais intégrer un peu votre "leçon" de programmation. J'ai compris que ce n'était pas très "clean" de faire des activate, select, copy, paste, etc. Et si j'ai bien compris vous avez tout résumé dans cette ligne :

Code: 
SourceFile.Worksheets("recap").Cells(1, 3).EntireColumn.Copy DestCell
En ayant pris soin de définir à l'avance SourceFile et DestCell. Juste après on redéfinit DestCell dans la boucle en le décalant d'une colonne vers la droite. Je me trompe?

J'aimerais réutiliser votre manière de faire, histoire de bien l'intégrer. Comme vous l'avez vu, au début de ma macro je prépare mon tableau avec un tas de range(A1) = "". Sauf que c'est lourd pour moi, il y a déjà 240 lignes et ça pourrait augmenter ou demander à être réorganiser. Vous imaginez le boulot... Surtout que cette colonne de titre est présente dans chacun de mes fichiers sources donc il suffirait d'aller la copier dans un de ces fichiers ou dans mon "fichier type" qui reste toujours à côté du fichier "statistiques".
Bref, je pense que c'est simple pour vous, mais pour moi c'est tout un défi à relever et pour l'instant je n'y arrive pas...

Pourriez-vous me dire ce qui cloche dans mon code? :

Code: 
FichierType = chemin & "\_fichier type à renommer et placer dans dossier.xlsm"
Set DestCell = DestSheet.Cells(1, "A")
SourceFile.FichierType("recap").Cells(1, 1).EntireColumn.Copy DestCell




 
drul

drul

Obscur pro du hardware
Staff
En ayant pris soin de définir à l'avance SourceFile et DestCell. Juste après on redéfinit DestCell dans la boucle en le décalant d'une colonne vers la droite. Je me trompe?
Cliquez pour agrandir...
C'est bien ça, tu as parfaitement saisi le truc

La tu (par définition le forum est une zone de tutoiement) ne définis pas Sourcefile,

SourceFile.FichierType("recap").Cells(1, 1).EntireColumn.Copy DestCell
Cliquez pour agrandir...

il manque "set sourceFile =workbooks.open FichierType"
Ensuite ce sera Sourcefile .sheets("recap").cells(1,1).EntireColumn.Copy DestCell
 
P

pepito78

Nouveau membre
Super merci ça marche bien. J'ai juste ajouté des parenthèses, sinon il y avait erreur.

"set sourceFile =workbooks.open(FichierType)"
 
Vous devez vous inscrire ou vous connecter pour répondre ici.
Dernières actus
Derniers messages publiés
Statistiques globales
Discussions
730 132
Messages
6 718 000
Membres
1 586 387
Dernier membre
ouistititouille
Partager cette page
Haut