using web queries

[M-Kells]

I am only a basic user of excel but i am trying to make my job a little
easier. i use excel to create a database of addresses to use as a mail merge.
this information is copied from a website in which it gives the name of the
company as a hyperlink and the post code, i currently manually copy and paste
the rest of the address details.

i have tried using a macro to run a webquery to copy the data from the
webpage (which is the entire page) into a new sheet and copy the cells i need
from that but i cant get the web query to use the hyperlink in the original
sheet, i have to type the web address. this defeats the object as there are
50 addresses i need info for.

it would be a huge help if i could get an experts advice to whether this can
actually be done.

Cheers

 

[Don Guillett]

Probably doable. Details of how you are getting there along with url

 

[M-Kells]

Hi Don,

Thanks for replying.

To start i have to create a spread sheet using information from the Law
Society website:
http://www.lawsociety.org.uk/choosingandusing/findasolicitor/action=lawfirmsearch.law

this is a typical page that i have to copy info from. it provides me with 50
solicitors in or around a certain post code. the link i have provided is for
just a random post code in Lodon.

I currently paste this info in to a spreadsheet and organise the data so it
is in columns. i have written a macro to do that for me. but what it leaves
me with is the name of the solicitor (hyperlink), the fisrt line of the
address and the post code.

what i am trying to do is write a macro to access the hyperlink to fill out
all of the address info for each solicitor (as you can imagine it is very
time consuming to access each hyper link). i tried just a basic copy and
paste but it only remembers the last thing on the clipboard so that didnt
work, then i tried a web query but i cant get it to use the web address from
the hyperlink i have to manually type it, but the web query for this
particular web page has to take the whole page and not a section. I tried the
web query to see what it was like and i ran it on a spare sheet and then
copied the cells that the info from the web page pulled through.

i would be greatly appreciative if you or anyone could give me some help.

M-Kells

 

[Don Guillett]

I'm still not sure what you want. I tried the url and was prompted to add
more info. Doesn't seem to lend itself to an external query. Perhaps you
would want to send your workbook to my private address below along with a
DETAILED, step by step, explanation of what you do and what you want it to
look like.