XL 2019 "Améliorer" un code VBA

bambi

XLDnaute Occasionnel
Bonjour à tous

J'ai créé une macro qui nettoie les données d'un fichier .csv puis les colle dans un fichier xlsm sur une page précise
Je me suis aidé de l'enregistreur de Macro mais aussi de recherches sur ce forum et sur le web en général
J'ai essayé de supprimer tout ce qui me semblait inutilement créé par l'enregistreur

Le résultat fonctionne parfaitement

Mais je me demande s'il est possible de simplifier certaines lignes ou des les écrire plus "proprement"

Aussi je vous soumet mon code pour avoir vos suggestions

Merci d'avance ;)

VB:
Sub Report()

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False

'Supprimer les 7 premières lignes vides

    Rows("1:7").Delete
   
'Convertir les données en colonnes distinctes
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
   
'Supprimer les colonnes inutiles
    Columns("C:C").Delete
    Columns("E:E").Delete
    Columns("I:I").Delete
    Columns("J:Q").Delete
       
'Insertion colonne vide en A
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
'Remplace les . par une ,
   
    Range("A1:J100").Replace What:=".", Replacement:=","
   
'Formule en dernière colonne pour calculer
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-5]<>"""",IF(RC[-5]=""BUY"",RC[-4],RC[-4]*-1),"""")"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K100"), Type:=xlFillDefault

'Copier et formater date en colonne 1
    Columns("C:C").Copy
    Range("A1").Select
    ActiveSheet.Paste
    Selection.NumberFormat = "mm/dd"

'Formatage colonnes date en heure
    Columns("C:D").Select
    Selection.NumberFormat = "hh:mm"
   
'Récupération heure  et ajout +1 heure pour heure française
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-9]<>"""",""01:00"","""")"
    Range("L2").AutoFill Destination:=Range("L2:L50"), Type:=xlFillDefault
   
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-10]<>"""",RC[-10]+RC[-1],"""")"
    Range("M2").AutoFill Destination:=Range("M2:M50"), Type:=xlFillDefault
   
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-10]<>"""",RC[-10]+RC[-2],"""")"
    Range("N2").AutoFill Destination:=Range("N2:N50"), Type:=xlFillDefault
   
    Columns("M:N").NumberFormat = "h:mm;@"
   
'Copie horaires modifiés colonne M et N dans colonnes C et D
    Range("M2:N50").Copy
    Range("C2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

'Copie Size dans colonne H

Range("K2:K50").Copy
    Range("G2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

' Colonne marché en 3ème colonnne
    Columns("E:E").Copy
    Columns("C:C").Insert Shift:=xlToRight

'Suppression Colonnes Inutiles
   Columns("L:O").Delete
   Columns("F:G").Delete

'Remise dans l'ordre et suppression des colonnes inutiles
    Columns("F:F").Copy
    Columns("D:D").Insert Shift:=xlToRight
    Columns("F:F").Copy
    Columns("E:E").Insert Shift:=xlToRight
    Columns("I:I").Copy
    Columns("F:F").Insert Shift:=xlToRight
    Columns("K:K").Copy
    Columns("H:H").Insert Shift:=xlToRight
    Columns("I:L").Delete
    Rows("1:1").Delete

'Mise en forme
    Range("A1:I50").HorizontalAlignment = xlCenter
    Range("A1:I50").VerticalAlignment = xlCenter
    Columns("A:I").EntireColumn.AutoFit
   
    Range("A1:I50").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
   End With

'Converti les données de la colonne I afin que les calculs dans la feuille de report fonctionnent après le copier-coller
    Columns("I:I").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

'Copie dans le presse papier l'ensemble des données nettoyées
    Range("A1").CurrentRegion.Copy

'Ouvre le fichier TDB et colle le résultat à partir de la première case vide colonne B, feuille CDT
    Workbooks.Open Filename:="D:\MDT\Racine\TDB.xlsm"
    Sheets("CDT").Select
    Range("B" & Range("B65536").End(xlUp).Row + 1).Select
    ActiveSheet.Paste

    Range("A1").Select

    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
   
End Sub

NB: Cette demande a uniquement pour but d'apprendre à coder plus "proprement" en VBA en bénéficiant de votre expérience.
 
Dernière édition:

sylvanu

XLDnaute Barbatruc
Supporter XLD
Bonsoir Bambi,
Quelques pistes ci dessous, il doit bien en exister d'autres :
Code:
'-------------------------------------------------
'Supprimer les colonnes inutiles
    Columns("C:C").Delete
    Columns("E:E").Delete
    Columns("I:I").Delete
    Columns("J:Q").Delete
' Remplacé par :
    Range("C:C,E:E,I:Q").Delete Shift:=xlToLeft
'-------------------------------------------------
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-5]<>"""",IF(RC[-5]=""BUY"",RC[-4],RC[-4]*-1),"""")"
' Remplacé par :
    Range("K2").FormulaR1C1 = _
        "=IF(RC[-5]<>"""",IF(RC[-5]=""BUY"",RC[-4],RC[-4]*-1),"""")"
'-------------------------------------------------
'Formatage colonnes date en heure
    Columns("C:D").Select
    Selection.NumberFormat = "hh:mm"
' Remplacé par :
    Columns("C:D").NumberFormat = "hh:mm"
'-------------------------------------------------
    Range("A1:I50").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
   End With
' Remplacé par :
    With Range("A1:I50").Font
        .Name = "Calibri"
        .Size = 8
   End With
'-------------------------------------------------
' ou encore :
    Range("A1:I50").HorizontalAlignment = xlCenter
    Range("A1:I50").VerticalAlignment = xlCenter
    Columns("A:I").EntireColumn.AutoFit
  
    Range("A1:I50").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
   End With
' Remplacé par :
    With Range("A1:I50")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .EntireColumn.AutoFit
        .Font.Name = "Calibri"
        .Font.Size = 8
   End With
'-------------------------------------------------
End Sub
Il faut éviter les .Select, généralement on peut s'en passer.
 

bambi

XLDnaute Occasionnel
Merci sylvanu
C'est exactement le genre de réponses que j'espérais 🙂
J'ai supprimé énormément de select mais je n'arrivais pas à structurer mon code pour les autres
Je créais des bugs plus qu'autre chose
Donc ta réponse est parfaite
Je vais la regarder de près et essayer de la retenir. 👍
 

soan

XLDnaute Barbatruc
Bonjour bambi, sylvanu,

je te propose ce code VBA (complet, en 77 lignes) :

VB:
Option Explicit

Sub Report()

  Application.ScreenUpdating = 0: Application.DisplayStatusBar = 0

  Rows("1:7").Delete 'Supprimer les 7 premières lignes vides

  'Convertir les données en colonnes distinctes
  Columns("A").TextToColumns Destination:=[A1], DataType:=xlDelimited, Comma:=True

  Range("C:C, E:E, I:Q").Columns.Delete 'Supprimer les colonnes inutiles
  
  Columns("A").Insert 2, 0 'Insertion colonne vide en A
  
  Range("A1:J100").Replace What:=".", Replacement:="," 'Remplace les . par une ,

  'Formule en dernière colonne pour calculer
  [K2].FormulaR1C1 = "=IF(RC[-5]<>"""",IF(RC[-5]=""BUY"",RC[-4],RC[-4]*-1),"""")"
  [K2].AutoFill [K2:K100]

  'Copier et formater date en colonne 1
  Columns("C").Copy: [A1].Select: ActiveSheet.Paste: [A1].NumberFormat = "mm/dd"

  Columns("C:D").NumberFormat = "hh:mm" 'Formatage colonnes date en heure

  'Récupération heure et ajout +1 heure pour heure française
  [L2].FormulaR1C1 = "=IF(RC[-9]<>"""",""01:00"","""")"
  [L2].AutoFill [L2:L50]

  [M2].FormulaR1C1 = "=IF(RC[-10]<>"""",RC[-10]+RC[-1],"""")"
  [M2].AutoFill [M2:M50]

  [N2].FormulaR1C1 = "=IF(RC[-10]<>"""",RC[-10]+RC[-2],"""")"
  [N2].AutoFill [N2:N50]

  Columns("M:N").NumberFormat = "h:mm;@"

  'Copie horaires modifiés colonne M et N dans colonnes C et D
  [M2:N50].Copy: [C2].PasteSpecial 12

  [K2:K50].Copy: [G2].PasteSpecial 12 'Copie Size dans colonne H

  Columns("E").Copy: Columns("C").Insert 2 'Colonne marché en 3ème colonne

  Range("F:G, L:O").Columns.Delete 'Suppression Colonnes Inutiles

  'Remise dans l'ordre et suppression des colonnes inutiles
  Columns("F").Copy: Columns("D").Insert 2
  Columns("F").Copy: Columns("E").Insert 2
  Columns("I").Copy: Columns("F").Insert 2
  Columns("K").Copy: Columns("H").Insert 2
  Columns("I:L").Delete: Rows("1").Delete

  With [A1:I50] 'Mise en forme
    .HorizontalAlignment = 3: .VerticalAlignment = 2: Columns("A:I").AutoFit
    With .Font: .Name = "Calibri": .Size = 8: End With
  End With

  'Convertit les données de la colonne I afin que les calculs dans la feuille
  'de report fonctionnent après le copier-coller
  Columns("I").TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
  Application.CutCopyMode = 0

  [A1].CurrentRegion.Copy 'Copie dans le presse papier l'ensemble des données nettoyées

  'Ouvre le fichier TDB et colle le résultat en feuille CDT, à partir de la première
  'cellule vide de la colonne B
  Workbooks.Open "D:\MDT\Racine\TDB.xlsm": Worksheets("CDT").Select
  Range("B" & Cells(Rows.Count, 2).End(3).Row + 1).Select
  ActiveSheet.Paste

  [A1].Select: Application.DisplayStatusBar = True

End Sub

soan
 

Staple1600

XLDnaute Barbatruc
Bonjour le fil

=>bambi
J'ai créé une macro qui nettoie les données d'un fichier .csv
Est-il possible d'avoir un exemple du fichier.csv ?
(ou une version anonymisée et ou connaître sa structure)
(*) Car on peut déjà faire pas mal de choses dès l'import du CSV dans Excel (avec Données/Fichier texte)
(**) Et comme tu es sur Excel 2019, on peut aussi utiliser avantageusement cette version (grâce à PowerQuery)

(*) On peut piloter cette phase en VBA

(**) Aucune ligne de code VBA, simplement moins de 10 lignes de code M(1)
(généré par PQ lorsque on manipule le fichier à la souris dans PQ)
(Et une fois la requête PQ créé, il suffit juste de l'actualiser)

(1) Exemple basique de code M (obtenu uniquement par PQ piloté à la souris)
(Ici j'ai juste chargé un fichier CSV et supprimer les colonnes C , E et J)
Code:
let
    Source = Csv.Document(File.Contents("C:\Users\STAPLE\Documents\bambi.csv"),[Delimiter=";", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"Column3", "Column5", "Column9"})
in
    #"Colonnes supprimées"
 
Dernière édition:

bambi

XLDnaute Occasionnel
@soan
Merci beaucoup pour ton code 👍
Je vais l'étudier de près et regarder ce qu'il donne en le mettant dans mon fichier
C'est une sacré simplification :)

@Staple1600
Je vais regarder si je peux le faire car ta méthode m'interesse
Je ne la connais pas du tout, ce serait encore une occasion d'apprendre ;)
 

Staple1600

XLDnaute Barbatruc
Bonjour le fil

=>bambi
Apprendre d'Excel, c'est toujours un plaisir ;)
C'est surtout que ce serait dommage de ne pas profiter d'une nouvelle fonctionnalité offerte par ta version d'Excel.
(qui en plus ne nécessite pas de connaissances particulières puisque tout se manipule à la souris et le code M est généré par PowerQuery commentaires "explicites" compris (et en français de surcroit)
Si on reprend, l'exemple dans mon message précédent, ceci a été ajouté automatiquement: #"Colonnes supprimées"

Donc si cela t'intéresse, il faudrait un exemple de fichier CSV pour que je puisse faire le traitement avec PowerQuery de A à Z.
 

bambi

XLDnaute Occasionnel
@Staple1600
Très occupée, me voilà enfin avec un fichier csv
Je le met en pièce jointe.
Je joins également le résultat voulu avec la feuille Résultat
Ce résultat est copié en fin de macepte pas les extensions .csv
J'ai mis ma macro dans la feuille résultat uniquement pour la poster avec l'ensemble
En réel, elle est dans ma feuille PERSONAL.XLSB
Merci d'avance ;)
 

Pièces jointes

  • excel-demande.zip
    25.3 KB · Affichages: 16

Staple1600

XLDnaute Barbatruc
Bonsoir le fil

=>bambi
Voici ce que donne mon premier essai
(tu vois que les commentaires ajoutés automatiquement détaillent bien les étapes effectuées à la souris dans PowerQuery)
VB:
let
    Source = Csv.Document(File.Contents("C:\Users\Staple\Documents\excel-demande\modele.csv"),[Delimiter=",", Columns=20, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
    #"Premières lignes supprimées" = Table.Skip(#"Type modifié",8),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Premières lignes supprimées",{"Column3", "Column6", "Column7", "Column11", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20"}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes supprimées",{"Column2", "Column1", "Column5", "Column4", "Column8", "Column9", "Column10", "Column12"}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Colonnes permutées",".",",",Replacer.ReplaceText,{"Column8", "Column9", "Column10", "Column12"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","-","/",Replacer.ReplaceText,{"Column2"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée1",{{"Column2", type datetime}}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Type modifié1","-","/",Replacer.ReplaceText,{"Column4"}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Valeur remplacée2",{{"Column4", type datetime}}),
    #"Date extraite" = Table.TransformColumns(#"Type modifié2",{{"Column2", DateTime.Date, type date}})
in
    #"Date extraite"
Il me faudrait le renfort de chris, Amilo, Roblochon ou R@chid
(pardon aux PQistes que j'oublie) car j'ai un souci avec les dates.
Voici le résultat obtenu avec PowerQuery
(c'est un premier essai)
bambi.png
 

Discussions similaires

Réponses
38
Affichages
2 K

Statistiques des forums

Discussions
290 922
Messages
1 911 414
Membres
177 160
dernier inscrit
rabinaud
Haut Bas