Sub Créer_requête()
'
' Macro5 Macro
'
'
fin = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
Nom = InputBox("entrer le nom de la requête")
ActiveWorkbook.Queries.Add Name:=Nom, Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://www.sfl.ch/fr/superleague/calendrier/saison/archiveseason/202122/sorting/date/""))," & Chr(13) & "" & Chr(10) & " Data2 = Source{2}[Data]," & Chr(13) & "" & Chr(10) & " #""Type modifié"" = Table.TransformColumnTypes(Data2,{{""Column1"", type text}, {""Column2"", type time}, {""Column3"", type text}, {""Column4"", type time}, {""Column5"", type text}})," & Chr(13) & "" & Chr(10) & " #""Derni" & _
"ers caractères insérés"" = Table.AddColumn(#""Type modifié"", ""Derniers caractères"", each Text.End([Column1], 8), type text)," & Chr(13) & "" & Chr(10) & " #""Colonnes permutées"" = Table.ReorderColumns(#""Derniers caractères insérés"",{""Column1"", ""Derniers caractères"", ""Column2"", ""Column3"", ""Column4"", ""Column5""})," & Chr(13) & "" & Chr(10) & " #""Colonnes supprimées"" = Table.RemoveColumns(#""Colonne" & _
"s permutées"",{""Column1""})," & Chr(13) & "" & Chr(10) & " #""Type modifié1"" = Table.TransformColumnTypes(#""Colonnes supprimées"",{{""Derniers caractères"", type date}})," & Chr(13) & "" & Chr(10) & " #""Colonnes supprimées1"" = Table.RemoveColumns(#""Type modifié1"",{""Column2""})," & Chr(13) & "" & Chr(10) & " #""Colonnes renommées"" = Table.RenameColumns(#""Colonnes supprimées1"",{{""Column3"", ""Equipe dom""}, {""Column5"", ""Equipe" & _
" ext""}, {""Derniers caractères"", ""Date""}})," & Chr(13) & "" & Chr(10) & " #""Colonnes permutées1"" = Table.ReorderColumns(#""Colonnes renommées"",{""Date"", ""Equipe dom"", ""Equipe ext"", ""Column4""})," & Chr(13) & "" & Chr(10) & " #""Premiers caractères insérés"" = Table.AddColumn(#""Colonnes permutées1"", ""Premiers caractères"", each Text.Start(Text.From([Column4], ""fr-CH""), 2), type text)," & Chr(13) & "" & Chr(10) & " #""Colon" & _
"nes renommées1"" = Table.RenameColumns(#""Premiers caractères insérés"",{{""Premiers caractères"", ""But dom""}})," & Chr(13) & "" & Chr(10) & " #""Derniers caractères insérés1"" = Table.AddColumn(#""Colonnes renommées1"", ""Derniers caractères"", each Text.End(Text.From([Column4], ""fr-CH""), 2), type text)," & Chr(13) & "" & Chr(10) & " #""Colonnes renommées2"" = Table.RenameColumns(#""Derniers caractères insérés" & _
"1"",{{""Derniers caractères"", ""But ext""}})," & Chr(13) & "" & Chr(10) & " #""Type modifié2"" = Table.TransformColumnTypes(#""Colonnes renommées2"",{{""But dom"", Int64.Type}, {""But ext"", Int64.Type}})," & Chr(13) & "" & Chr(10) & " #""Colonnes supprimées2"" = Table.RemoveColumns(#""Type modifié2"",{""Column4""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Colonnes supprimées2"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & Nom & ";Extended Properties=""""" _
, Destination:=Range("$A$" & fin)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [" & Nom & "]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = Nom
.Refresh BackgroundQuery:=False
End With
End Sub