Import mehrere CSV-Dateien aus dem Internet in Excel

EDIT: Den code unten behebt das Problem, das Sie berichtet, aber aus der Erinnerung sehr schnell. Erstellt habe ich eine andere Antwort, die ich denke, ist viel besser und robuster

Sieht es aus wie deine Abfrage ist nicht anerkannt vom server. Sie können hinzufügen, einige Fehler prüft weiterhin, ob ein solcher Fehler aufgetreten ist.

Sub Get_Yahoo_finance()

    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Ticker As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim a, b, c, d, e, f
    Dim StrURL As String
    Dim errorMsg As String

    Set Sh = Worksheets("Input")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)
    For Each Cell In Rng
        Ticker = Cell.Value
        StartDate = Cell.Offset(0, 1).Value
        EndDate = Cell.Offset(0, 2).Value
        a = Format(Month(StartDate) - 1, "00") '   Month minus 1
        b = Day(StartDate)
        c = Year(StartDate)
        d = Format(Month(EndDate) - 1, "00")
        e = Day(EndDate)
        f = Year(EndDate)
        StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
        StrURL = StrURL & "s=" & Ticker & "&a=" & a & "&b=" & b
        StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
        StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"
        If WorksheetExists(Ticker, ActiveWorkbook) Then
            Application.DisplayAlerts = False
            Sheets(Ticker).Select
            ActiveWindow.SelectedSheets.Delete
            ActiveWorkbook.Worksheets.Add.Name = Ticker
        Else
            ActiveWorkbook.Worksheets.Add.Name = Ticker
        End If
        With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
           .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            On Error Resume Next
            .Refresh BackgroundQuery:=False
            errorMsg = IIf(Err.Number = 0, "", Err.Description)
            On Error GoTo 0
        End With
        If errorMsg = "" Then
            Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
                TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
                :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
                Array(7, 1))
            Range("A2").Select
            Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
            Columns("A:F").EntireColumn.AutoFit
        Else
            Range("A1") = errorMsg
        End If
    Next Cell

End Sub

Function WorksheetExists(SheetName As String, Optional WhichBook As Workbook) As Boolean '
    'from Chip Pearson
    Dim WB As Workbook
    Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
    On Error Resume Next
    WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

Möchten Sie vielleicht zu löschen, das Blatt, anstatt eine Fehlermeldung, oder vielleicht schicken Sie eine MsgBox statt...

Beigefügt ist eine "einfachere" Lösung mit den original-code geändert, um erneut abrufen die ticker-Daten bis zu 3-mal (wartet ein paar Sekunden zwischen den versuchen), bevor Sie schließlich eingestehen von Versagen durch die messagebox. My 2 cents 🙂

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMillisecond As Long)

Sub Get_Yahoo_finance_history()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Ticker As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim a, b, c, d, e, f
    Dim StrURL As String
    Dim RetryCount As Integer

'turn calculation off
    'Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Set Sh = Worksheets("Input")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row)

    For Each Cell In Rng
        Ticker = Cell.Value
        StartDate = Cell.Offset(0, 1).Value
        EndDate = Cell.Offset(0, 2).Value
        a = Format(Month(StartDate) - 1, "00") '   Month minus 1
        b = Day(StartDate)
        c = Year(StartDate)
        d = Format(Month(EndDate) - 1, "00")
        e = Day(EndDate)
        f = Year(EndDate)
        StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
        StrURL = StrURL & "s=" & Ticker & "&a=" & a & "&b=" & b
        StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
        StrURL = StrURL & "&f=" & f & "&g=d&ignore=.csv"
        If WorksheetExists(Ticker, ActiveWorkbook) Then
            Sheets(Ticker).Select
            ActiveWindow.SelectedSheets.Delete
            ActiveWorkbook.Worksheets.Add.Name = Ticker
        Else
            ActiveWorkbook.Worksheets.Add.Name = Ticker
        End If
        RetryCount = 0 Retry:
        If RetryCount > 3 Then
            Range("A1") = errorMsg
            MsgBox "After 3 attempts: Could not retrieve data for " + Ticker
            End
        End If
        RetryCount = RetryCount + 1

        With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            On Error Resume Next
            .Refresh BackgroundQuery:=False
            errorMsg = IIf(Err.Number = 0, "", Err.Description)
            On Error GoTo 0
        End With
        If errorMsg = "" Then
           Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
               TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
               Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
               :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
               Array(7, 1))
           Columns("A").EntireColumn.NumberFormat = "mm/dd/yyyy"
           Columns("B:E").EntireColumn.NumberFormat = "$###,##0.00"
           Columns("F").EntireColumn.NumberFormat = "###,##0"
           Columns("B:E").EntireColumn.NumberFormat = "$###,##0.00"
           Columns("A:F").EntireColumn.AutoFit
        Else
           Sleep (500)
           Sheets(Ticker).Cells.ClearContents
           GoTo Retry
        End If
    Next Cell
     'turn calculation back on
    'Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
     End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function