Bonjour,

Je souhaite faire une macro qui concatène des fichiers csv d'un dossier et qu'il m'enregistre le tout dans un fichier unique. (avec suppression des doublons et le remplacement des valeurs "Undef" par "0" )

J'ai réalisé un enregistrement de la macro suivante:

Pourriez vous m'aidez à nettoyer ce code et à le comprendre.

Merci

Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
 
 
Sub Macro5()
'
' Macro5 Macro
'
 
'
    ActiveWorkbook.Queries.Add Name:="Transformer l'exemple de fichier (4)", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Paramètre4,[Delimiter="";"", Columns=57, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Modifier le type"" = Table.TransformColumnTypes(#""Utiliser la première ligne pour les en-têtes"",{{""TimeInt"", Int64.Type}, {""T" & _
        "imeStr"", type datetime}, {""AI1_2_TemperatureOven"", Int64.Type}, {""AI1_3"", Int64.Type}, {""AI2_2"", Int64.Type}, {""AI2_3"", Int64.Type}, {""AI3_2"", Int64.Type}, {""AI3_3"", Int64.Type}, {""AI4_2"", Int64.Type}, {""AI4_3"", Int64.Type}, {""DI1_0_BP_StartStop"", Int64.Type}, {""DI1_2_Heater2_OK"", Int64.Type}, {""DI1_3_Heater2OperatingOrder"", Int64.Type}, {""DI" & _
        "2_0_Heater1_OK"", Int64.Type}, {""DI2_2_FlowMeter_OK"", Int64.Type}, {""DI2_3_RegulatorOperatingOrder"", Int64.Type}, {""DI3_2_CircuitBreaker_F2_F3_OK"", Int64.Type}, {""DI3_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI4_2_CircuitBreaker_F4_F5_OK"", Int64.Type}, {""DI4_3_PowerOnIndicatorLightGreen"", Int64.Type}, {""DI5_2_CabOverheat_OK"", Int64.Type}, {""DI5" & _
        "_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI6_2_CabVoltagePresence_OK"", Int64.Type}, {""DI6_3_PID_Controler_Heating"", Int64.Type}, {""DI7_2_FanOperatingOrder"", Int64.Type}, {""DI7_3"", Int64.Type}, {""DI8_2_Heater1OperatingOrder"", Int64.Type}, {""DI8_3"", Int64.Type}, {""DO1_0_LanEnabled"", Int64.Type}, {""DO1_2"", Int64.Type}, {""DO1_3"", Int64.Type}, " & _
        "{""DO2_2"", Int64.Type}, {""DO2_3"", Int64.Type}, {""Sensor1AbsoluteHumidity"", type text}, {""Sensor1DewPointTemperature"", type text}, {""Sensor1Enthalpy"", type text}, {""Sensor1MixingRatio"", type text}, {""Sensor1RelativeHumidity"", type text}, {""Sensor1Status"", Int64.Type}, {""Sensor1Temperature"", type text}, {""Sensor1WetBulbTemperature"", type text}, {""S" & _
        "ensor2AbsoluteHumidity"", type text}, {""Sensor2DewPointTemperature"", type text}, {""Sensor2Enthalpy"", type text}, {""Sensor2MixingRatio"", type text}, {""Sensor2RelativeHumidity"", type text}, {""Sensor2Status"", Int64.Type}, {""Sensor2Temperature"", type text}, {""Sensor2WetBulbTemperature"", type text}, {""Sensor3AbsoluteHumidity"", type text}, {""Sensor3DewPoi" & _
        "ntTemperature"", type text}, {""Sensor3Enthalpy"", type text}, {""Sensor3MixingRatio"", type text}, {""Sensor3RelativeHumidity"", type text}, {""Sensor3Status"", Int64.Type}, {""Sensor3Temperature"", type text}, {""Sensor3WetBulbTemperature"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Modifier le type"""
    ActiveWorkbook.Queries.Add Name:="Paramètre4", Formula:= _
        "#""Exemple de fichier (4)"" meta [IsParameterQuery=true, BinaryIdentifier=#""Exemple de fichier (4)"", Type=""Binary"", IsParameterQueryRequired=true]"
    ActiveWorkbook.Queries.Add Name:="Exemple de fichier (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\3\SynchroFile"")," & Chr(13) & "" & Chr(10) & "    Navigation1 = Source{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Navigation1"
    ActiveWorkbook.Queries.Add Name:="Transformer le fichier (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = (Paramètre4) => let" & Chr(13) & "" & Chr(10) & "        Source = Csv.Document(Paramètre4,[Delimiter="";"", Columns=57, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "        #""Utiliser la première ligne pour les en-têtes"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "        #""Modifier le type"" = Table.TransformColumnTypes(#""Utiliser la première ligne pour l" & _
        "es en-têtes"",{{""TimeInt"", Int64.Type}, {""TimeStr"", type datetime}, {""AI1_2_TemperatureOven"", Int64.Type}, {""AI1_3"", Int64.Type}, {""AI2_2"", Int64.Type}, {""AI2_3"", Int64.Type}, {""AI3_2"", Int64.Type}, {""AI3_3"", Int64.Type}, {""AI4_2"", Int64.Type}, {""AI4_3"", Int64.Type}, {""DI1_0_BP_StartStop"", Int64.Type}, {""DI1_2_Heater2_OK"", Int64.Type}, {""DI1" & _
        "_3_Heater2OperatingOrder"", Int64.Type}, {""DI2_0_Heater1_OK"", Int64.Type}, {""DI2_2_FlowMeter_OK"", Int64.Type}, {""DI2_3_RegulatorOperatingOrder"", Int64.Type}, {""DI3_2_CircuitBreaker_F2_F3_OK"", Int64.Type}, {""DI3_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI4_2_CircuitBreaker_F4_F5_OK"", Int64.Type}, {""DI4_3_PowerOnIndicatorLightGreen"", Int64.Type}, " & _
        "{""DI5_2_CabOverheat_OK"", Int64.Type}, {""DI5_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI6_2_CabVoltagePresence_OK"", Int64.Type}, {""DI6_3_PID_Controler_Heating"", Int64.Type}, {""DI7_2_FanOperatingOrder"", Int64.Type}, {""DI7_3"", Int64.Type}, {""DI8_2_Heater1OperatingOrder"", Int64.Type}, {""DI8_3"", Int64.Type}, {""DO1_0_LanEnabled"", Int64.Type}, {""D" & _
        "O1_2"", Int64.Type}, {""DO1_3"", Int64.Type}, {""DO2_2"", Int64.Type}, {""DO2_3"", Int64.Type}, {""Sensor1AbsoluteHumidity"", type text}, {""Sensor1DewPointTemperature"", type text}, {""Sensor1Enthalpy"", type text}, {""Sensor1MixingRatio"", type text}, {""Sensor1RelativeHumidity"", type text}, {""Sensor1Status"", Int64.Type}, {""Sensor1Temperature"", type text}, {" & _
        """Sensor1WetBulbTemperature"", type text}, {""Sensor2AbsoluteHumidity"", type text}, {""Sensor2DewPointTemperature"", type text}, {""Sensor2Enthalpy"", type text}, {""Sensor2MixingRatio"", type text}, {""Sensor2RelativeHumidity"", type text}, {""Sensor2Status"", Int64.Type}, {""Sensor2Temperature"", type text}, {""Sensor2WetBulbTemperature"", type text}, {""Sensor3A" & _
        "bsoluteHumidity"", type text}, {""Sensor3DewPointTemperature"", type text}, {""Sensor3Enthalpy"", type text}, {""Sensor3MixingRatio"", type text}, {""Sensor3RelativeHumidity"", type text}, {""Sensor3Status"", Int64.Type}, {""Sensor3Temperature"", type text}, {""Sensor3WetBulbTemperature"", type text}})" & Chr(13) & "" & Chr(10) & "    in" & Chr(13) & "" & Chr(10) & "        #""Modifier le type""" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source" & _
        ""
    ActiveWorkbook.Queries.Add Name:="SynchroFile (4)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Folder.Files(""C:\3\SynchroFile"")," & Chr(13) & "" & Chr(10) & "    #""Fichiers masqués filtrés1"" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)," & Chr(13) & "" & Chr(10) & "    #""Appeler une fonction personnalisée1"" = Table.AddColumn(#""Fichiers masqués filtrés1"", ""Transformer le fichier (4)"", each #""Transformer le fichier (4)""([Content]))," & Chr(13) & "" & Chr(10) & "    #""Colonnes renommées1"" = Tab" & _
        "le.RenameColumns(#""Appeler une fonction personnalisée1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & "    #""Autres colonnes supprimées1"" = Table.SelectColumns(#""Colonnes renommées1"", {""Source.Name"", ""Transformer le fichier (4)""})," & Chr(13) & "" & Chr(10) & "    #""Colonne de tables développée1"" = Table.ExpandTableColumn(#""Autres colonnes supprimées1"", ""Transformer le fichier (4)"", Table.Col" & _
        "umnNames(#""Transformer le fichier (4)""(#""Exemple de fichier (4)"")))," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(#""Colonne de tables développée1"",{{""Source.Name"", type text}, {""TimeInt"", Int64.Type}, {""TimeStr"", type datetime}, {""AI1_2_TemperatureOven"", Int64.Type}, {""AI1_3"", Int64.Type}, {""AI2_2"", Int64.Type}, {""AI2_3"", Int64.Type}, {""A" & _
        "I3_2"", Int64.Type}, {""AI3_3"", Int64.Type}, {""AI4_2"", Int64.Type}, {""AI4_3"", Int64.Type}, {""DI1_0_BP_StartStop"", Int64.Type}, {""DI1_2_Heater2_OK"", Int64.Type}, {""DI1_3_Heater2OperatingOrder"", Int64.Type}, {""DI2_0_Heater1_OK"", Int64.Type}, {""DI2_2_FlowMeter_OK"", Int64.Type}, {""DI2_3_RegulatorOperatingOrder"", Int64.Type}, {""DI3_2_CircuitBreaker_F2_F" & _
        "3_OK"", Int64.Type}, {""DI3_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI4_2_CircuitBreaker_F4_F5_OK"", Int64.Type}, {""DI4_3_PowerOnIndicatorLightGreen"", Int64.Type}, {""DI5_2_CabOverheat_OK"", Int64.Type}, {""DI5_3_InOperatIndicatorLightYellow"", Int64.Type}, {""DI6_2_CabVoltagePresence_OK"", Int64.Type}, {""DI6_3_PID_Controler_Heating"", Int64.Type}, {""D" & _
        "I7_2_FanOperatingOrder"", Int64.Type}, {""DI7_3"", Int64.Type}, {""DI8_2_Heater1OperatingOrder"", Int64.Type}, {""DI8_3"", Int64.Type}, {""DO1_0_LanEnabled"", Int64.Type}, {""DO1_2"", Int64.Type}, {""DO1_3"", Int64.Type}, {""DO2_2"", Int64.Type}, {""DO2_3"", Int64.Type}, {""Sensor1AbsoluteHumidity"", type text}, {""Sensor1DewPointTemperature"", type text}, {""Sensor" & _
        "1Enthalpy"", type text}, {""Sensor1MixingRatio"", type text}, {""Sensor1RelativeHumidity"", type text}, {""Sensor1Status"", Int64.Type}, {""Sensor1Temperature"", type text}, {""Sensor1WetBulbTemperature"", type text}, {""Sensor2AbsoluteHumidity"", type text}, {""Sensor2DewPointTemperature"", type text}, {""Sensor2Enthalpy"", type text}, {""Sensor2MixingRatio"", type" & _
        " text}, {""Sensor2RelativeHumidity"", type text}, {""Sensor2Status"", Int64.Type}, {""Sensor2Temperature"", type text}, {""Sensor2WetBulbTemperature"", type text}, {""Sensor3AbsoluteHumidity"", type text}, {""Sensor3DewPointTemperature"", type text}, {""Sensor3Enthalpy"", type text}, {""Sensor3MixingRatio"", type text}, {""Sensor3RelativeHumidity"", type text}, {""S" & _
        "ensor3Status"", Int64.Type}, {""Sensor3Temperature"", type text}, {""Sensor3WetBulbTemperature"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    Workbooks("FileConcatenation1.csv").Connections.Add2 _
        "Requête*-*Transformer l'exemple de fichier (4)", _
        "Connexion à la requête «*Transformer l'exemple de fichier (4)*» dans le classeur." _
        , _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transformer l'exemple de fichier (4)"";Extended Properties=""""" _
        , "SELECT * FROM [Transformer l'exemple de fichier (4)]", 2
    Workbooks("FileConcatenation1.csv").Connections.Add2 "Requête*-*Paramètre4", _
        "Connexion à la requête «*Paramètre4*» dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Paramètre4;Extended Properties=""""" _
        , "SELECT * FROM [Paramètre4]", 2
    Workbooks("FileConcatenation1.csv").Connections.Add2 _
        "Requête*-*Exemple de fichier (4)", _
        "Connexion à la requête «*Exemple de fichier (4)*» dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Exemple de fichier (4)"";Extended Properties=""""" _
        , "SELECT * FROM [Exemple de fichier (4)]", 2
    Workbooks("FileConcatenation1.csv").Connections.Add2 _
        "Requête*-*Transformer le fichier (4)", _
        "Connexion à la requête «*Transformer le fichier (4)*» dans le classeur.", _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Transformer le fichier (4)"";Extended Properties=""""" _
        , "SELECT * FROM [Transformer le fichier (4)]", 2
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""SynchroFile (4)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SynchroFile (4)]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SynchroFile__4"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.SaveAs Filename:="C:\3\FileConcatenation1.csv", FileFormat:= _
        xlCSVUTF8, CreateBackup:=False
End Sub