Import csv file from web VBA

[dpmaki]

I have a file out on the web: Create Tests for Organizational Training and Certification Programs

I'd like to import the contents of that file into excel through VBA. Is there a way to do this?
Note - the URL is obviously a dummy URL.

The contents of the file will change from time to time in terms of how many rows there are. Ideally I'd like to capture all the data and import into a worksheet titled "test".

Thanks!

 

[John_w]

Try something like this:

Sub Import_CSV_File_From_URL()
    
    Dim URL As String
    Dim destCell As Range
    
    URL = "http://www.test.com/test.csv"
    
    Set destCell = Worksheets("test").Range("A1")
    
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & URL, Destination:=destCell)
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
    
    destCell.Parent.QueryTables(1).Delete

End Sub

 

[jaryszek]

Hi,

and to put this query into listobject there is any magic trick ?

Best,
Jacek