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:

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,608
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,409
Messages
5,595,973
Members
414,035
Latest member
billbumkins

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