CODEWITHSUNDEEP
excelvba
Michael W
Michael W

Reputation: 67

Excel VBA searching for the range of dates and downloading the respective files

I would like to write a Excel VBA script to help me search through the dates, and based on that download the range of files.

(I was trying to introduce a "for" loop but don't have an idea how to proceed)

My starting date is dtReportDateStart and respectively end day is dtReportDateEnd

This is how I started:

Sub Get_XY()
    Dim dtReportDateStart As String
    Dim dtReportDateEnd As String
    Dim strPricesLocation As String
    Dim strPricesDestination As String
    
    dtReportDateStart = Format(ThisWorkbook.Worksheets("Dashboard").Range("C2"), "YYYYMMDD")
    dtReportDateEnd = Format(ThisWorkbook.Worksheets("Dashboard").Range("C3"), "YYYYMMDD")
    
    strPricesLocation = "http:/..." & dtReportDateStart & ".tsv.txt"
End Sub

Upvotes: 0

Views: 109

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

Try to use Microsoft.XMLHTTP object:

Option Explicit

Sub Get_XY()
    Dim dt As Long
    
    With ThisWorkbook.Worksheets("Dashboard")
        For dt = .Range("C2") To .Range("C3")
            LoadFile "http:/..." & Format(dt, "YYYYMMDD") & ".tsv.txt", "C:\temp"
        Next
    End With
End Sub

Sub LoadFile(CURL As String, FPath As String)
    Dim WinHttpReq As Object, FName As String
    
    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    
    FName = InStrRev(CURL, "/")
    If FName > 0 Then
        FName = Mid(CURL, FName + 1)
    Else
        FName = "file.dat"
    End If

    On Error GoTo out
    WinHttpReq.Open "GET", CURL, False
    On Error GoTo 0
    WinHttpReq.send
    
    If WinHttpReq.Status = 200 Then
        With CreateObject("ADODB.Stream")
            .Open
            .Type = 1
            .Write WinHttpReq.responseBody
            .SaveToFile FPath & "\" & FName, 2
            .Close
        End With
    End If
    Exit Sub
out:
    Debug.Print CURL & " can't be opened"
End Sub

Upvotes: 2

Related Questions