import multiple CSV files into sheet

[rey745]

Hello All


I am trying to import multiple sheets from a link, i currently do this by downloading all the file (28 files) and importing them to my sheet. I would like to have excel download right from the site and import into the sheet. I am currently able to download the 1 file into the sheet for the 1 day i input, is there a way to have an input box were i can input the date were the date is in the link and repeat increasing the date by 1 day seven times and there are 4 other files that are the same link just the region ID changes. Then have all the data go into a sheet
any help would be appreciated
https://****.test.com/test123/reports/reports?route_date=2019-09-12&region_id=12&work_type=&format=csv

Sub IMPORT_ALL()

Dim str As String
 Dim myarray() As Variant
'Delete existing data 'Name of sheet the data will be downloaded into. Change as required.
Sheets("Sheet1").Select
    Range("A2:Aw2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A2:Aw50001").Select
    Selection.ClearContents
    Range("Ah1").Activate
    Selection.ClearContents
    ActiveWindow.LargeScroll ToRight:=1
    Range("Ah1").Select
    ActiveCell.FormulaR1C1 = "Version"
    Range("A1").Select



Dim ConnString As String
ConnString = "TEXT;https://****.test.com/test123/reports/reports?route_date=2019-09-12&region_id=12&work_type=&format=csv"

    With ActiveSheet.QueryTables.Add(Connection:=ConnString, Destination:=Range("A1"))
        .Name = _
        " "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 775
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileDecimalSeparator = ","
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
      Sheets("sheet2").Select

    If Range("A2").End(xlDown).Row <> Range("Ag2").End(xlDown).Row Then
        If Range("Ah2") = "" Then
            Range("Ah2", Cells(Range("A2").End(xlDown).Row, 34)) = "Dispatch"
        ElseIf Range("Ah2").End(xlDown).Value = "Dispatch" Then
            Range(Cells(Range("Ah2").End(xlDown).Row + 1, 34), Cells(Range("A2").End(xlDown).Row, 33)) = "Intraday"
        Else
            Range(Cells(Range("Ah2").End(xlDown).Row + 1, 34), Cells(Range("A2").End(xlDown).Row, 33)) = "Completed"
        End If
    End If

    
    Range("A2:A50001").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Ai2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("Ai2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=True, Comma:=True, Space:=True, other:=True, OtherChar:= _
        "-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Range("A2").Select
    ActiveSheet.Next.Select
    Range("A2").Select
    Selection.End(xlToRight).Select
    Range("x3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("y3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    Range("A3").Select
    ActiveWorkbook.RefreshAll
        Sheets("RNR").Select
        ActiveWorkbook.RefreshAll
        Sheets("sheet3").Select
    Range("A1").Select
End Sub