VBA code to get .csv file from a URL and repeat for all rows

[clark153]

I am trying to import a large number of URL addresses where a .csv file exists. These URL addresses are stored in a worksheet, one per row, along with the name of the worksheet I want to import the data to.

I know how to create a hyperlink for each row but would like a VBA/Macro solution that automates; creating a new workbook from file name in the row, downloading the .csv into that new workbook and then repeat until the rows are all complete.

Here are some examples of the URL and work sheet names.

Thanks in advance

[TABLE="width: 654"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]URL[/TD]
[TD]Work Sheet Name[/TD]
[/TR]
[TR]
[TD]http://bidonfusion.com/downloads/lot/226283/manifest-1999AM14716.csv[/TD]
[TD]manifest-1999AM14716[/TD]
[/TR]
[TR]
[TD]http://bidonfusion.com/downloads/lot/226284/manifest-1999AM14718.csv[/TD]
[TD]manifest-1999AM14718[/TD]
[/TR]
[TR]
[TD]http://bidonfusion.com/downloads/lot/226285/manifest-1999AM14720.csv[/TD]
[TD]manifest-1999AM14720[/TD]
[/TR]
</tbody>[/TABLE]

 

[John_w]

Try this code in the workbook containing the URLs.

Public Sub Download_CSV_Data()
    
    Dim URLs As Range, URL As Range
        
    With ActiveSheet
        Set URLs = .Range("A2", .Cells(Rows.Count, "A").End(xlUp))
    End With
    
    For Each URL In URLs
        Workbooks.Add xlWBATWorksheet
        ActiveSheet.Name = URL.Offset(0, 1)
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & URL.Value, Destination:=Range("A1"))
            .Name = "csv"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        
        ActiveSheet.QueryTables(1).Delete
        
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & URL.Offset(0, 1) & ".xls"
        ActiveWorkbook.Close False
        
    Next
    
End Sub

If you want to download the data to .csv files instead of workbooks (.xls files) then you could simply call URLDownloadToFile in the loop.