Programmatic Web Queries

[ChloeRadshaw]

Hi All ,

I need to connect to a URL and retrieve data which I then want to process in VBA.

As far as I understand it web queries is the way to do this and I have written the code to do this - However this relies on writing the webquery results to a range

Is here any way og getting the web query to return data into memory and not have to use a worksheet as an intermediate step?

Any answers much appreciated

[Kenneth Hobs]

Depends on what information you need.

An option using web query method is to import the data to a temporary or hidden sheet and select the text and cut or copy the selection. Delete the sheet or hide it.

[jfournier]

You can use the internet explorer control to navigate to a URL, after which you can access the html representation of the page retrieved by the URL.

Sub TryIE()
Dim objApp As Object

'Create instance and set to visible
On Error GoTo ErrHandler
Set objApp = CreateObject("InternetExplorer.Application")
With objApp
.Visible = False
End With

'Add some text to the document
objApp.Navigate URL:="http://google.com"
Range("A1").Value = objApp.Document.body.innerHTML
objApp.Quit
ErrHandler:
Set objApp = Nothing
End Sub