Import data from website (1 Viewer)

[krabople]

Hi, wondering if anyone can help. I want to import data from a webpage using VBA and then store the HTML contents in a table (well, actually just part of the contents in fact). Is this possible?

 

[tehNellie]

off the top of my head, you don't need to use VBA, Excel can interrogate a website periodically and store that data in a tabular format which could then be imported into a database.

This may not be exactly what you're looking for, but might at least give you some ideas:

http://office.microsoft.com/en-gb/excel-help/get-external-data-from-a-web-page-HA010218472.aspx

 

[Royce]

This will get the html and save it to a text file

Sub CopyHTTPFile(strURL As String, strLocalPath As String, strLocalFileName As String)
    
    ' Create an array to hold the response data.
    Dim arrDownloadedBytes() As Byte
    Dim WinHttpReq As WinHttpRequest
       
    
    Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
        
    ' create the HTTP Request
    WinHttpReq.Open "GET", strURL, False
    
    ' Send the request
    WinHttpReq.Send
    
    
    ' copy the response body to a local file
    Open strLocalPath & strLocalFileName For Binary As #1
    arrDownloadedBytes() = WinHttpReq.ResponseBody
    Put #1, 1, arrDownloadedBytes()
    Close

End Sub

You'll need to set a reference to Microsoft WinHTTPServices, version 5.1

Usually pulling the data out of the HTML is the biggest task. Often made more complicated because the html will change from one request to another.

If there is a web api that will return XML the task would be much simpler.

 

[krabople]

This will get the html and save it to a text file

Sub CopyHTTPFile(strURL As String, strLocalPath As String, strLocalFileName As String)
    
    ' Create an array to hold the response data.
    Dim arrDownloadedBytes() As Byte
    Dim WinHttpReq As WinHttpRequest
       
    
    Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
        
    ' create the HTTP Request
    WinHttpReq.Open "GET", strURL, False
    
    ' Send the request
    WinHttpReq.Send
    
    
    ' copy the response body to a local file
    Open strLocalPath & strLocalFileName For Binary As #1
    arrDownloadedBytes() = WinHttpReq.ResponseBody
    Put #1, 1, arrDownloadedBytes()
    Close

End Sub

You'll need to set a reference to Microsoft WinHTTPServices, version 5.1

Usually pulling the data out of the HTML is the biggest task. Often made more complicated because the html will change from one request to another.

If there is a web api that will return XML the task would be much simpler.

Hi, thanks very much for your reply. I don't actually appear to have a reference available for WinHTTPServices, but having removed the 'As WinHttpRequest' the code appears to execute perfectly and creates a text file with the HTML. How would I go about inserting the HTML into a text box on a form, instead of into a text file? I've tried adding 'Me.Text1 = arrDownloadedBytes()' to the end of the sub but the text box just ends up being a load of gibberish instead of the actual HTML (as you may have guessed by now I'm not an expert when it comes to this type of stuff!)

 

[krabople]

And by the way, I did have a reference for Microsoft WinHTTPServices, version 5.1, I was just being blind. Could still do with some help for the above though.

 

[jdraw]

krabople,

Do you have a specific url for testing?
What exactly do you need help with?

 

[krabople]

krabople,

Do you have a specific url for testing?
What exactly do you need help with?

It's a bit complicated to explain in detail but it's to do with our company's website. There's a lot of data stored in various pages but it's all coded in HTML. My job is to go through the entire website and extract the relevant information and store it in a database table.

It's OK now though. Using the suggestion above I managed to import data into a text file and then import it back into an Access database. From there I can loop through all of the pages on the website and find the relevant strings and store them in a database. I know this is a bit of an excessive way of doing things but it's all working fine so problem sorted!

 

[jdraw]

Glad you have it under control.