Den Code hatte ich über die Makroaufzeichnung generiert:
clanlink = Worksheets(
"ListeDeutscheClans"
).Cells(10 + i, 3)
Application.CutCopyMode =
False
ActiveWorkbook.Queries.Add Name:=
"Table 3"
, Formula:= _
"let"
& Chr(13) &
""
& Chr(10) &
" Quelle = Web.Page(Web.Contents("
""
& clanlink &
""
")),"
& Chr(13) &
""
& Chr(10) &
" Data3 = Quelle{3}[Data],"
& Chr(13) &
""
& Chr(10) &
" #"
"Geänderter Typ"
" = Table.TransformColumnTypes(Data3,{{"
""
", type text}, {"
"Spielername"
", type text}, {"
"Veränderung"
", type text}, {"
"Datum"
", type text}}),"
& Chr(13) &
""
& Chr(10) &
" #"
"Entfernte Spalten"
" = Table.RemoveColumns(#"
"Geänderter Typ"
",{"
""
", "
"Datum"
"}),"
& Chr(13) &
""
& Chr(10) &
" #"
& _
""
"Gefilterte Zeilen"
" = Table.SelectRows(#"
"Entfernte Spalten"
", each ([Veränderung] = "
"Ausgetreten"
")),"
& Chr(13) &
""
& Chr(10) &
" #"
"Hinzugefügte bedingte Spalte"
" = Table.AddColumn(#"
"Gefilterte Zeilen"
", "
"Benutzerdefiniert"
", each if [Veränderung] = "
"Ausgetreten"
" then 1 else null)"
& Chr(13) &
""
& Chr(10) &
"in"
& Chr(13) &
""
& Chr(10) &
" #"
"Hinzugefügte bedingte Spalte"
""
With
ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="
"Table 3"
";Extended Properties="
""
""
_
, Destination:=Range(
"$B$10"
)).QueryTable
.CommandType = xlCmdSql
.CommandText = Array(
"SELECT * FROM [Table 3]"
)
.RowNumbers =
False
.FillAdjacentFormulas =
False
.PreserveFormatting =
True
.RefreshOnFileOpen =
False
.BackgroundQuery =
True
.RefreshStyle = xlInsertDeleteCells
.SavePassword =
False
.SaveData =
True
.AdjustColumnWidth =
True
.RefreshPeriod = 0
.PreserveColumnInfo =
True
.ListObject.DisplayName =
"Table_3"
.Refresh BackgroundQuery:=
False
End
With