VBA - Power Query

**dudux2** · 26/11/2024, 11h57

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

**tototiti2008** · 26/11/2024, 12h03

Bonjour,

L'intérêt d'une requête PowerQuery c'est qu'elle est enregistrée dans le fichier
On a besoin de macro que si on a besoin de recréer la requête, mais si elle est déjà là et fonctionnelle, pourquoi passer par une macro ?
Il suffit d'actualiser la requête, et si on veut comprendre ce qui est fait regarder chaque étape d'import et transformation des données
Pour analyser une étape spécifique, le code M est inscrit en haut et est modifiable (ou si on veut plus d'options en cliquant sur la roue dentée à côté de l'étape)

**78chris** · 27/11/2024, 10h04

Bonjour

D'autant qu'une requête peut se paramétrer. Donc pas d'intérêt de coder dans la majorité des cas

VBA - Power Query

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager