1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| Sub getExtract()
Dim sSql As String
Dim debut, fin As Date
debut = ThisWorkbook.Sheets("Modèle Client").Range("D3")
fin = ThisWorkbook.Sheets("Modèle Client").Range("F3")
debut = DateSerial(Year(debut), Month(debut), Day(debut))
fin = DateSerial(Year(fin), Month(fin), Day(fin))
On Error Resume Next
ActiveWorkbook.Queries("MaTable_1").Delete
sSql = "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""MaTable""]}[Content], " & Chr(13) & "" & Chr(10) & ""
sSql = sSql & "#""Changed Type"" = Table.TransformColumnTypes(Source,{{""DateExcécution"", type datetime}, {""Sens"", type text}, {""OrdreNbr"", Int64.Type}, "
sSql = sSql & "{""Produit"", type text}, {""Mise"", type number}, {""Ouverture"", type number}, {""Clôture"", type number}, {""G_P"", type text}, {""Paiement"", " & _
"type number}})," & Chr(13) & "" & Chr(10) & " #""Filtered Rows"" = Table.SelectRows(#""Changed Type"", "
sSql = sSql & "each [DateExcécution] > #datetime(" & Year(debut) & ", " & Month(debut) & ", " & Day(debut) & ", 15, 20, 1) " & _
"And [DateExcécution] < #datetime(" & Year(fin) & ", " & Month(fin) & ", " & Day(fin) & ", 15, 20, 1))" & Chr(13) & "" & Chr(10) & _
"in" & Chr(13) & "" & Chr(10) & " #""Filtered Rows"""
ActiveWorkbook.Queries.Add Name:="MaTable_1", Formula:=sSql |
Partager