Web Query Macro automation to return phone number

dairvine

New Member
Joined
Jan 23, 2015
Messages
2
Hello all,

This is my fist time posting, thank you for your help. I am using excel 2007.

I have come across an issue at work with a web query. I have a basic understanding of web queries. Which means I have pasted a URL into excel to get external data from certain tables on the desired website. However I have been asked to extract phone numbers and company websites from a search engine with just the companies name. My data is listed in column A (company names).

An example of one of a piece of data I will be dealing with - AICHI FORGE USA, INC.

In my test macro I have made it so each space in the companies name is replaced with "+" which i believe will allow me to run the query succesfully since the correct search URL would look something like this.

https://www.google.com/search?q=AICHI+FORGE+USA+INC.

However when I run this macro it does not produce the desired results. instead it dispays this
Screen reader users, click here to turn off Google Instant.
×
Come here often? Make Google your homepage.
Sure
No thanks
Search
Images
Maps
Play
YouTube
News
Gmail
Drive
More
Calendar
Translate
Mobile
Books
Wallet
Shopping
Blogger
Finance
Photos
Videos
Docs
Even more »
Account Options
Sign in
Search settings
Advanced search
Language tools
Web History

Google
Google Instant is unavailable. Press Enter to search. Learn more
Google Instant is off due to connection speed. Press Enter to search.
Press Enter to search.
×


A faster way to browse the web
Install Google Chrome
Google
Make Google my homepage
Privacy Terms Settings Search settings Advanced search History Search Help Send feedback Advertising Business About


<tbody>
</tbody>
My code so far is as follows.

Code:
Sub test()
Columns("A").Replace what:=" ", replacement:="+", SearchOrder:=xlByColumns
Dim w As Worksheet: Set w = ThisWorkbook.Sheets("Sheet1")
Dim url As String
Dim symbols As String
symbols = symbols & w.Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:="URL;https://www.google.com/#q=" & strSearch, _
Destination:=Range("B1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

Naturally once I get this part figured out I will create it so it runs through the the entire set of data not just A1. Clearly I am a little lost and have been racking my brain to get this figured out, any help would be greatly appreciated.

Thank you,

Nic
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I am still getting a jumble of information, not really getting the info i need from the page. If i run the query normally by entering the url and seleting the table for all the information presented by the page it returns the info i need in the data but if I run it through this macro it returns the results below.

Happy New Year to all our users!
This page has been Textised!
The original page address was https://www.google.co.uk/?gfe_rd=cr&ei=OajCVIneJtPBUMHZgIAL&gws_rd=ssl
For sharing use Textised! : Google
Here are some options:
- Back to original page (in this window | in a new window)
- Convert this page to a PDF
- Print this page
- Textise Home Page (full version | text only version)
- Textise Options Page (choose font size, font colour, etc.)
Google Instant is unavailable. Press Enter to search. Learn more
Google Instant is off due to connection speed. Press Enter to search.
Press Enter to search.
Screen-reader users, click here to turn off Google Instant.
+You
Gmail
Images
Sign in
Hidden fields
×
UK
Privacy Terms Settings Search settings Advanced search History Search Help Send feedback Use Google.com Advertising Business About
Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Learn more
Got it
+You
Search
YouTube
Maps
Play
News
Gmail
Drive
Calendar
More
Translate
Books
Shopping
Blogger
Finance
Photos
Docs
Even more from Google
This text-only page was created by Textise ([url]www.textise.net).[/URL]
Find us on Facebook and Twitter or visit the Official Blog.

<tbody>
</tbody><colgroup><col></colgroup>
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top