So, ich bin mal nicht so. Und nächstes mal: API Key niemals weitergeben!
Das Makro über eine Schaltfläche ausführen:
Option Explicit
Sub Test()
Dim query As WorkbookQuery
Dim strQuery As String
strQuery = "let" & vbNewLine & _
"Quelle = Csv.Document(Web.Contents(""https://www.quandl.com/api/v3/datasets/BTER/BTCUSD.csv?api_key=Q9nWrKsWj7_yZBX9x6LH""),[Delimiter="","", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None])," & 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 'neues Tabellenblatt anlegen
.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
Grüße
|