Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1580to1584
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA Abfrage aus Datei - aus CSV

VBA Abfrage aus Datei - aus CSV
24.09.2017 14:03:01
Bernd
Hallo zusammen,
Ich hoffe mir kann hier jemand helfen!
Hatte am 18.9 bereits eine ähnliche Frage gepostet aber bis heute keine Antwort bekommen und zwar hier www.clever-excel-forum.de unter Querytable einfügen.
Kurz zu meinem Problem bzw. mein aufgezeichnetes Makro in der Beispielmappe.
Mein Vorhaben:
1. Lege neue Tabelle an mit dem Namen aus Tabelle Kürzel Zelle B2 (z.B. ADS.DE)
2. Nehme aus Tabelle Kürzel Zelle G2 (also selbe Zeile) den Link und mache neue Abfrage aus CSV. Füge Abfrage in Tabelle "ADS.DE" Zelle A1 ein.
3. Wenn fertig mache dasselbe mit der nächsten Zeile in Tabelle Kürzel.
Meine aufgezeichnetes Makro funktioniert bis Punkt 2, aber ich schaffe es nicht mit einer Schleife die gleiche Abfrage mit der nächste Zeile 3 usw. (bis letzte gefüllte Zeile) in Tabelle Kürzel durchzuführen. Meine VBA-Kenntnisse reichen noch nicht.
Daher bitte ich euch um Hilfe.
Gruß Bernd
Office Version 2016 Pro 32bit
Beispielmappe https://www.herber.de/bbs/user/116475.xlsm

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 13:02:10
ChrisL
Hi Bernd
Bei der Abfrage kann ich dir nicht helfen, aber hier mal eine mögliche Struktur:
Sub HistorischeKurseOnvista()
Call AbfragenLoeschen
Call Verbindungen_Loeschen
Call DeleteWorkSheets
Dim lngZeile As Long, wksNeu As Worksheet
With Worksheets("Kürzel")
For lngZeile = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
Sheets.Add After:=Sheets(Sheets.Count)
Set wksNeu = ActiveSheet
wksNeu.Name = .Cells(lngZeile, 2)
MsgBox "Hier die Abfrage auf Pfad " & .Cells(lngZeile, 7) & " einfügen."
Next lngZeile
End With
End Sub
cu
Chris
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 16:48:08
Bernd
Hallo Chris,
Danke für deine Hilfe. Ich bin dir sehr dankbar!!!
Die Zeile MsgBox verstehe ich nicht?
Habe gerade ein Makro aufgezeichnet, welches nur die Abfrage beinhaltet.
Sheets("Kürzel").Select     ' Hier wird der Link für die Abfrage entnommen
Range("G2").Select          ' ...
Selection.Copy              ' ...
Sheets("ADS.DE").Select     ' Bis hier
'Nun kommt die eigentliche Abfrage
ActiveWorkbook.Queries.Add Name:= _
"export csv?notationId=39517324&dateStart=26 09 2016&interval=Y1&assetName=ADS" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(Web.Contents(""https://www. _
onvista.de/onvista/boxes/historicalquote/export.csv?notationId=39517324&dateStart=26.09.2016&interval=Y1&assetName=ADS.DE&exchange=Xetra""),[Delimiter="";"", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #" & _
"""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""Datum"", _
type date}, {""Eroeffnung"", type number}, {""Hoch"", type number}, {""Tief"", type number}, {""Schluss"", type number}, {""Volumen"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten"" = Table.RemoveColumns(#""Geänderter Typ"",{""Eroeffnung"", ""Hoch"", ""Tief"", ""Volumen""})," & Chr(13) & "" & Chr(10) & "    #" & _
"""Sortierte Zeilen"" = Table.Sort(#""Entfernte Spalten"",{{""Datum"", Order.Descending} _
})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Sortierte Zeilen"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""export csv? _
notationId=39517324&dateStart=26 09 2016&interval=" _
, "Y1&assetName=ADS"""), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [export csv?notationId=39517324&dateStart=26 09 2016&interval=Y1& _
assetName=ADS]" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"export_csv_notationId_39517324_dateStart_26_09_2016_interval_Y1_assetName_ADS"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Kannst du mir sagen, wie ich das in deinen Code einfügen muss.
Lieben Gruß aus Bremen
Bernd
Anzeige
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 18:49:49
ChrisL
hi Bernd
Die MsgBox ist ein Platzhalter für die Abfrage.
.Cells(lngZeile, 7)
Enthält den variablen Link.
BackgroundQuery (= Hintergrundabfrage)
Steht aktuell manchmal auf True. Ich würde mal alle auf False setzen, dann sollten die Abfragen der Reihe nach laufen.
Die Kürzel müssen natürlich eindeutig sein. Und wenn du nicht vorher löschst, dann ist ein Konflikt vorprogrammiert. Könnte also richtig mühsam werden, die löschbaren von den anderen Abfragen zu unterscheiden.
cu
Chris
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 20:52:09
Bernd
Hallo Chris,
habe es hin bekommen und die Abfrage an die richtige Stelle eingefügt.
Problem beim zweiten Durchlauf des Makros stoppt das Makro

ActiveWorkbook.Queries.Add Name:= _
Wie kann man die Erste Abfrage bevor die nächste Anweisung

Next lngZeile
kommt löschen und zweitens wie kann ich die MsgBox umgehen.
Kannst du mir weiterhelfen?
Danke im Voraus!
Lieben Gruß
Bernd
Anzeige
AW: VBA Abfrage aus Datei - aus CSV
28.09.2017 18:40:28
ChrisL
Hi Bernd
Ich lasse die Frage offen.
Aber trotzdem als kleine Anregung. Ich denke du müsstest existierende Abfragen/Queries updaten und nicht neu erstellen. Da das Überprüfen der Query etwas komplizierter wird, könntest du vielleicht den umgekehrten Weg gehen und die bereits erstellten Abfragen in der Liste kennzeichnen.
z.B.
Sub HistorischeKurseOnvista()
Call Verbindungen_Loeschen
Call DeleteWorkSheets
Dim lngZeile As Long, wksNeu As Worksheet
With Worksheets("Kürzel")
For lngZeile = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
If .Cells(lngZeile, 3) = "schon vorhanden" Then
MsgBox "jetzt update"
Else
.Cells(lngZeile, 3) = "schon vorhanden"
Sheets.Add After:=Sheets(Sheets.Count)
Set wksNeu = ActiveSheet
wksNeu.Name = .Cells(lngZeile, 2)
MsgBox "Hier die Abfrage auf Pfad " & .Cells(lngZeile, 7) & " einfügen."
End If
Next lngZeile
End With
End Sub
cu
Chris
Anzeige
AW: VBA Abfrage aus Datei - aus CSV
30.09.2017 10:42:48
Bernd
Hallo Chris,
habe deinen Vorschlag

Abfragen/Queries updaten

mal gegoogelt und mehrere Youtube Videos gefunden unter anderem über das Ändern von Parametern der Abfrage. Werde mich da mal reinfummeln und schauen wie weit ich komme. Wenn ich nicht weiter komme, werde ich mich melden.
Lieben Gruß
Bernd
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 13:33:23
Matthias
Moin!
Habe mal deinen Code zusammengefasst und ein SChleife durch alle Kürzel in Spalte B angelegt. Konnte es aber nicht testen, da mein altes System kein PowerQery kennt. Habe deshalb mal versucht den Code in der Abfrage dazu anzupassen (Kürzel der Seite, Wert aus Spalte G usw.). Bei der Abfrage müsste du ggf. noch die Strings anpassen, weiß nicht ob die Fehlerfrei für jeden Durchlauf hinhauen. HIer dier Code.
VG

Sub HistorischeKurseOnvista()
Dim blattname As String
Dim zeile As Long
Dim ende As Long
Dim kürzel As String
Call AbfragenLoeschen
Call Verbindungen_Loeschen
Call DeleteWorkSheets
Application.ScreenUpdating = False
ende = Sheets("Kürzel").Cells(Sheets("Kürzel").Rows.Count, 2).End(xlUp).Row
For zeile = 2 To ende
blattname = Sheets("Kürzel").Cells(zeile, 2)
kürzel = Left(blattname, Len(blattname) - 3)
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = blattname
ActiveWorkbook.Queries.Add Name:= _
"export csv?notationId=39517324&dateStart=22 09 2012&interval=Y5&assetName=" & kürzel &  _
"" _
, Formula:= _
"let" & Chr(13) & "" & Chr(10) & "    Quelle = Csv.Document(Web.Contents(" & Sheets("Kü _
rzel").Cells(zeile, 7) & "),[Delimiter="";"", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #" & _
"""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""Datum"", _
type date}, {""Eroeffnung"", type number}, {""Hoch"", type number}, {""Tief"", type number}, {""Schluss"", type number}, {""Volumen"", type number}})," & Chr(13) & "" & Chr(10) & "    #""Sortierte Zeilen"" = Table.Sort(#""Geänderter Typ"",{{""Datum"", Order.Descending}})," & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten"" = Table" & _
".oveColumns(#""Sortierte Zeilen"",{""Eroeffnung"", ""Hoch"", ""Tief"", ""Volumen""})" & _
Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Entfernte Spalten"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""export csv? _
notationId=39517324&dateStart=22 09 2012&interval=" _
, "Y5&assetName=" & kürzel & """"), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT * FROM [export csv?notationId=39517324&dateStart=22 09 2012&interval=Y5& _
assetName=" & kürzel & "]" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = _
"export_csv_notationId_39517324_dateStart_22_09_2012_interval_Y5_assetName_" & kürzel
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Columns("B:B").NumberFormat = "#,##0.000"
Next zeile
Application.ScreenUpdating = True
End Sub
Sub Verbindungen_Loeschen()
Dim wb As Workbook, objConnection As Variant, varAuswahl As Long
Set wb = ActiveWorkbook
For Each objConnection In wb.Connections
Application.DisplayAlerts = False
objConnection.Delete
Application.DisplayAlerts = True
Next
End Sub
Sub AbfragenLoeschen()
Dim objQr
For Each objQr In ThisWorkbook.Queries
objQr.Delete
Next
End Sub
Sub DeleteWorkSheets()
Dim wks As Worksheet
Application.DisplayAlerts = False
For Each wks In ThisWorkbook.Worksheets
If wks.Name  "Depot" And wks.Name  "Kürzel" And wks.Name  "Chart-Vorschau" And wks. _
Name  "Yahoo API Tags" And wks.Name  "Hilfe" Then
wks.Delete
End If
Next
Application.DisplayAlerts = True
End Sub

Anzeige
AW: VBA Abfrage aus Datei - aus CSV
26.09.2017 16:24:52
Bernd
Hallo Matthias,
Danke für deine Hilfe, ich bin dir sehr dankbar dafür!!!
Beim ersten Durchlauf deines Makros stoppte es bei der Zeile ".Refresh BackgroundQuery:=False". Mit dem Hinweis "Anwendungs-- oder objektorientierter Fehler"
Beim zweiten Durchlauf stoppte das Makro bei der Zeile "ActiveWorkbook.Queries.Add" mit dem Hinweis, dass die Abfrage bereits vorhanden ist.
Was auch nicht mehr ausgeführt werden kann, sind die Makros "Abfrage löschen und Verbindungen löschen", da ich mittlerweile mehrere andere Abfragen in der Arbeitsmappe habe, die dann ebenfalls gelöscht werden.
Kannst du die Fehler beseitigen?
Lieben Gruß aus Bremen
Bernd
Anzeige
AW: VBA Abfrage aus Datei - aus CSV
27.09.2017 10:54:43
Matthias
MOin!
Da kann ich leider nicht wirklich helfen. Wie geschrieben kennt mein Excel 2003 noch keine Queries. Hatte deshalb nur versucht die im Code vorkommenden Abschnitte (bspw. die Kürzel) variabel zu gestalten.
Tut mir leid.
VG
AW: VBA Abfrage aus Datei - aus CSV
27.09.2017 11:40:09
Bernd
Hallo Matthias,
egal, denn du hast versucht mir zu helfen und das alleine zählt!!!
Gruß aus Bremen
Bernd

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige