Web query - using cell contents as part of URL

C

claytorm

Hello,

There are two parts to my question:
1. I want to substitute cell contents for part of an url in a we
query.
e.g how could I substitute the "02474" i
http://www.hemscott.com/equities/company/key/key02474.htm for a valu
specified in a cell in my sheet.

2. Using the table at the url above, I want to pull only three piece
of data to be placed in sequential columns:A."Abacus Group" B."fin x
(6.80p)" C."10-Dec-03".


Any help appreciated greatly.

Bertie
 
J

Jake Marx

Hi Bertie,

You can do this entirely programmatically, but I think it would be more
effort than it's worth. I would suggest trying a web query (Data | Import
External Data --> New Web Query). You could then use code in the
worksheet's Change event that checks to see if your "ID" cell changes - when
it does, simply update the query definition and refresh the query table.

In this specific case, I did the following:

1) select cell A6 and select Data | Import External Data --> New Web Query
(I put it in A6 to leave room above it for the "ID" cell and data you
want
to extract)

2) in the Address box, enter the full URL:

http://www.hemscott.com/equities/company/key/key02474.htm

3) select the following 3 tables: the links (Home, Sitemap, etc),
company name, and data table

4) click Import and import the data to cell A6

5) right-click cell A6 and select Data Range Properties - under
"If the number of rows in the data....", select the option
"Overwrite existing cells with new data, clear unused cells",
then click OK

6) format cell A1 as text, then enter 02474 in it

7) in cells A4, B4, and C4, enter the following formulas:

=A7
=A15
=B15

These cells will pull the requested values from the data range.

8) right-click the sheet tab and select View Code - paste the
following code into the code module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) _
Is Nothing Then
With QueryTables(1)
.Connection = "URL;http://www.hemscott.com/" _
& "equities/company/key/key" & _
Range("A1").Value & ".htm"
.Refresh
End With
End If
End Sub


That should do it. Let me know if you have any questions or need more
information.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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