Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

VBA Abfrage aus Datei - aus CSV


Betrifft: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 24.09.2017 14:03:01

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

  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: ChrisL
Geschrieben am: 26.09.2017 13:02:10

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 26.09.2017 16:48:08

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: ChrisL
Geschrieben am: 26.09.2017 18:49:49

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 26.09.2017 20:52:09

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: ChrisL
Geschrieben am: 28.09.2017 18:40:28

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 30.09.2017 10:42:48

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Matthias
Geschrieben am: 26.09.2017 13:33:23

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





  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 26.09.2017 16:24:52

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Matthias
Geschrieben am: 27.09.2017 10:54:43

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


  

Betrifft: AW: VBA Abfrage aus Datei - aus CSV von: Bernd Kiehl
Geschrieben am: 27.09.2017 11:40:09

Hallo Matthias,
egal, denn du hast versucht mir zu helfen und das alleine zählt!!!
Gruß aus Bremen
Bernd


Beiträge aus den Excel-Beispielen zum Thema "VBA Abfrage aus Datei - aus CSV"