Option
Explicit
Sub
Test()
Dim
query
As
WorkbookQuery
Dim
strQuery
As
String
strQuery =
"let"
& vbNewLine & _
"Kopfzeile = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),"
& vbNewLine & _
"Daten_mit_Datentyp = Table.TransformColumnTypes(Kopfzeile,{{"
"Date"
", type date}, {"
"High"
", Int64.Type}, {"
"Low"
", Int64.Type}, {"
"Last"
", Int64.Type}, {"
"Average"
", Int64.Type}, {"
"Sell"
", Int64.Type}, {"
"Buy"
", Int64.Type}, {"
"Volume (BTC)"
", Int64.Type}, {"
"Volume (USD)"
", Int64.Type}})"
& vbNewLine & _
"in"
& vbNewLine & _
"Daten_mit_Datentyp"
Set
query = ThisWorkbook.Queries.Add(
"MeineAbfrage_"
& Format$(Now(),
"yyyy-mm-dd\Thhnnss"
), strQuery,
"Die Beschreibung..."
)
With
ThisWorkbook.Worksheets.Add
.Name = query.Name
With
.ListObjects.Add( _
SourceType:=0, _
Source:=
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="
& query.Name &
";Extended Properties="
""
""
, _
Destination:=Range(
"$A$1"
) _
).QueryTable
.CommandType = xlCmdSql
.CommandText = Array(
"SELECT * FROM ["
& query.Name &
"]"
)
.RowNumbers =
False
.FillAdjacentFormulas =
False
.PreserveFormatting =
True
.RefreshOnFileOpen =
False
.BackgroundQuery =
True
.RefreshStyle = xlInsertDeleteCells
.SavePassword =
False
.SaveData =
True
.AdjustColumnWidth =
True
.RefreshPeriod = 0
.PreserveColumnInfo =
True
.Refresh BackgroundQuery:=
False
End
With
End
With
End
Sub