CSV from Website CreateObject("Microsoft.XMLHTTP")

[johnbird1988]

Hello,

I have the below bit of code that will connect to a website download the .csv file into a location. At the moment I have set this on a loop of about ten files and save them in a location.

I then copy all the data from the ten files download into one master worksheet.

What I would like help with if possible is to not save the downloaded files but to copy and append them into a master worksheet either in one go or ten appended files.

Any help would be greatly appreciated, I have been searching the net for hours.

Dim WinHttpReq As Object
Dim myURL As String, sFilename As String
Dim c As Range

Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

For Each c In Sheet1.Range("A1:A10")

sFilename = Environ("SystemDrive") & Environ("HomePath") & Application.PathSeparator & "Desktop" & Application.PathSeparator & c.value & ".csv"

myURL = "http://www.website.co.uk/index/test/" & c.value &".csv"

WinHttpReq.Open "GET", myURL, False
WinHttpReq.Send

myURL = WinHttpReq.ResponseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.ResponseBody
    oStream.SaveToFile sFilename, 2
    oStream.Close
    
End If

Next c

Thank you

John

 

[John_w]

Try this, using a different technique. The URLs are in cells A2:A11 on the first sheet and the CSV file contents are imported into the second sheet.

Public Sub Import_CSV_Files2()
    
    Dim URLcell As Range
    Dim destCell As Range, CSVstartRow As Long
    
    For Each URLcell In Worksheets(1).Range("A2:A11")
    
        With Worksheets(2)
        
            CSVstartRow = 1
            Set destCell = .Cells(Rows.Count, 1).End(xlUp)
            If destCell.Row > 1 Then
                Set destCell = destCell.Offset(1, 0)
                CSVstartRow = 2
            End If
    
            With .QueryTables.Add(Connection:="TEXT;" & URLcell.Value, Destination:=destCell)
                .TextFileStartRow = CSVstartRow
                .TextFileParseType = xlDelimited
                .TextFileCommaDelimiter = True
                .Refresh BackgroundQuery:=False
            End With
    
            .QueryTables(1).Delete
        End With
    Next

End Sub