Download data from URL and write to worksheet.

[Tom_T]

Hi,
I'm looking for a way to download data from a server and then display it in a worksheet. So far I've created two worksheets Home & Sites

On the Home worksheet I have two cells A1 & A2 which are used for the users username and password.
What I'm trying to do is add a download button so when they click it there username and password are submitted to the server and the details are then downloaded.

The servers URL is in this format: http://example.com?user=USERNAME&password=PASSWORD

The downloaded results are a string of data with multiple comma separated values. Each new entry is on a new line: eg:

"NW21-A76","Upstate","798952124"
"NP54-P87","Local","798927272"
"SK06-001","N/A","543666788"

I need to take this data and split it on the new lines, then on the commas, writing the values to the Sites worksheet.
The first value in B10, the second C10 and the third in D10. The next row of data in B11,C11,D11 etc.

So far I've got:

Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
    Dim oXMLHTTP As Object
    Dim sResponse  As String
    Dim sURL As String
    Dim var As String
 
    user = ThisWorkbook.Sheets("Home").Range("A1")
    pwd = ThisWorkbook.Sheets("Home").Range("A2")
    sURL = "http://example.com?user=" & user & "&password=" & pwd


    'Extract data from website to Excel using VBA
    Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHTTP.Open "GET", sURL, False
    oXMLHTTP.send
    sResponse = oXMLHTTP.responseText


    'Get webpage data into Excel
    ThisWorkbook.Sheets("Sites").Cells(1, 1) = sResponse
    MsgBox "XMLHTML Fetch Completed"
    
    sResponse = Replace(sResponse, vbCrLf, vbCr)
    sResponse = Replace(sResponse, vbLf, vbCr)
    sResponse = Replace(sResponse, """", "")
    
    rowData = Split(sResponse, vbCr)
 
    For Counter = 0 To UBound(rowData)
        MsgBox rowData(Counter)
    Next


End Sub

The final loop is currently outputting each row of data to a MsgBox and that appears fine. I need to split that on the commas and then write the three values in Sites B10, C10 and D10.
The next row of results should be written into Site B11, C11 and D11 and so on until all results are written.

Can anyone advise how to do this ?

Thanks

 

[Tom_T]

I've got this working and it's downloading the data fine, but the writing out to the rows and cells is slow.

Any better way to do this ?

Private Sub To_Excel()
    Dim oXMLHTTP As Object
    Dim sResponse  As String
    Dim sURL As String
    destRow = 10
    
    user = ThisWorkbook.Sheets("Home").Range("A1")
    pwd = ThisWorkbook.Sheets("Home").Range("A2")
    
    
    sURL = "http://example.com?user=" & user & "&password=" & pwd

    'Extract data from website to Excel using VBA
    Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    oXMLHTTP.Open "GET", sURL, False
    oXMLHTTP.send
    sResponse = oXMLHTTP.responseText

    sResponse = Replace(sResponse, vbCrLf, vbCr)
    sResponse = Replace(sResponse, vbLf, vbCr)
    sResponse = Replace(sResponse, """", "")
    rowData = Split(sResponse, vbCr)
 
    If (InStr(sResponse, "ERROR")) Then
        MsgBox "Error: " & vbCrLf & vbCrLf & sResponse
        End
    End If
 
For Counter = 0 To UBound(rowData)
    Row = rowData(Counter)
    
    Column = Split(Row, ",")
    valA = Column(0)
    valB = Column(1)
    valC = Column(2)
    
    ThisWorkbook.Sheets("Sites").Cells(destRow, 2) = valA
    ThisWorkbook.Sheets("Sites").Cells(destRow, 3) = valB
    ThisWorkbook.Sheets("Sites").Cells(destRow, 4) = valB
    destRow = destRow + 1
    
Next

End Sub

Thanks