Fetch data from website with conditions

the_rainmaker

New Member
Joined
Mar 22, 2011
Messages
2
Hi,

I am trying to write a macro that can fetch data from a website.
To be specific, I am trying to write a macro that would automatically
pull phone numbers from www.canada411.ca given the postal code and name AND retrive ONLY IF THE ADDRESS MATCHES.
This is to be used with a spreadsheet that has address, postal codes, and names sorted by columns

I am very new with this so can anyone show me how I can start with this? Maybe a sample script that has been used or a link to different thread would be very helpful.

Thanks in advance!:)
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,338
Try a web query. Using an example search for:

Name: John Smith
Location: Toronto, H2L 3K5

the resultant URL is:

http://www.canada411.ca/search/si/1/John+Smith/Toronto,+H2L+3K5

which means you can construct (using VBA) the search URL directly from the data in your Excel sheet without having to populate the search boxes on the web page. You can use this URL in a web query created by VBA.

Change the 'Results per page' to 50 and the URL changes to:

http://www.canada411.ca/search/si/1/John+Smith/Toronto,+H2L+3K5?pglen=50

which means it's more likely that the address you want to match is on that page. Even better, if you click 'Printer Friendly', the URL changes to:

http://www.canada411.ca/search/print/si/1/John+Smith/Toronto,+H2L+3K5?pglen=50

and there is less junk for the web query to import.

I would start by creating a web query with the macro recorder running to generate the VBA as a starting point for further development and go from there.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,627
Members
430,372
Latest member
contentment

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top