Extract data from website in an Excel Spreadsheet

[sh_obaid]

Hi all,

I am new to this forum and would like someone to please help me out. I have a website given below;

http://biz.yahoo.com/ic/524_cl_all.html

My Excel Spreadsheet has columns named "Hospital Name | Overview | Address | Phone | Fax | Contact Person 1 | Contact Person 1 Title | Contact Person 2 | Contact Person 2 Title | Contact Person 3 | Contact Person 3 Title"

There is the list of hospitals starting from "Abbott Northwestern Hospital". I have two things in my mind to extract the information. If you click on the first hospital name "Abbott Northwestern Hospital". Below is an example text which I would like to transfer it automatically in columns given above;

This text should go in column named "Overview":
Abbott Northwestern Hospital helps bring twins into the Twin Cities -- along with triplets, quadruplets............

This text should go in column named "Address": 800 E. 28th St.Minneapolis, MN 55407

This text should go in column named "Phone": 612-863-4000

This text should go in column named "Fax": 612-863-5667


This text should go in column named "Contact Person 1": Jeffrey D. (Jeff) Peterson

This text should go in column named "Contact Person 1 Title": President

This text should go in column named "Contact Person 2": Daryl Schroeder

This text should go in column named "Contact Person 2 Title": VP Operations

This text should go in column named "Contact Person 3": Sandy Schmitt

This text should go in column named "Contact Person 3 Title": VP Strategic Development, Allina Hospitals and Clinics

Please help, if there can be any script, macro or whatever could be helpful to automate the process.

Thanks and I look forward for any help.

Regards!

[JohnM3]

Hi sh

I _CANNOT_ code all the tasks that you require. But I have attached a
workbook that will bring back data from each hospital.

You will need additional code to move the data to where you want. That
coding is beyond me.

Please be aware that the way I am retreiving the data _MAY NOT BE_ the
best way to do it. But it is the only way I know.

There are approx 900 hospitals and I have HARD-CODED the routine
to bring back 100. My Control WS explains what you have to change
to bring back data for all 900.

I have all ready tested bringing back all 900+ hospitals but it takes
> 20 minutes. (Another reason to review any other responses of coding
solutions for your request)

The attached WB is an Excel 2007 macro WB. It will automatically open
to a WS called Control. Just hit the button to run the routine.

(there are instructions how to get to the routine to modify it)

The 3 WS's
=============
Hospital - lists all the LINKS --> each hospital
Target_V - data is copied DOWNWARD for each hospital
Target_H - data is copied ACCROSS the ws for each hospital

HTH

regards
John

[Palmetto]

sh_obaid,

Had you taken the time to read the forum rules you would have learned that cross posting is a serious violation of the rules.

Cross posted:

Ozgrid

VBA Express

Mr Excel