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 |
