Importing web data to a VBA Array
 

Hi Claymore,

You can do things like this with the XMLHTTP object (search on Google /
Google Groups for more info). Basically, you would open the URL with the
GET method and invoke the Send method, and the resulting HTML source for
that page can be retrieved using the ResponseText property.

Once you have the HTML source for the page, you'll have to get creative with
parsing it out and storing values to an array. Looking at the yahoo!
finance page you referenced, it may be difficult, as not many of the
elements are named or have unique strings in them.

After looking up references for the XMLHTTP object, let us know if you have
any more questions or need more guidance.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Claymore wrote:
Hello,

The below code imports a small table from yahoo finance to range A1
of the current sheet.

ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/hp?s=DELL",
Destination:=Range("A1")

What I want to do is import the same table to an array in VBA memory
and then access the array to spit out a specific part of the table.
So instead of pasting the table to a sheet and then cutting and
pasting the number I need, I can just paste the number I need. The
problem is I cant figure out how to import the data to anything other
than a physical range.

Any Help would be greatly Appreciated.

Thanks Much