Web Query Macro automation to return phone number

[dairvine]

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
[TABLE="width: 384"]
<tbody>[TR]
[TD="colspan: 2"]Screen reader users, click here to turn off Google Instant.
[/TD]
[/TR]
[TR]
[TD]×
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Come here often? Make Google your homepage.
[/TD]
[/TR]
[TR]
[TD]Sure
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No thanks
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Search
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Images
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Maps
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Play
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YouTube
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]News
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gmail
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drive
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]More
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Calendar
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Translate
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mobile
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Books
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wallet
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shopping
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blogger
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finance
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Photos
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Videos
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Docs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Even more »
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Account Options
[/TD]
[/TR]
[TR]
[TD]Sign in
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Search settings
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Advanced search
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Language tools
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Web History
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Google
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Google Instant is unavailable. Press Enter to search. Learn more
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Google Instant is off due to connection speed. Press Enter to search.
[/TD]
[/TR]
[TR]
[TD]Press Enter to search.
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]×


[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A faster way to browse the web
[/TD]
[/TR]
[TR]
[TD]Install Google Chrome
[/TD]
[/TR]
[TR]
[TD]Google
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Make Google my homepage
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Privacy Terms Settings Search settings Advanced search History Search Help Send feedback Advertising Business About

[/TD]
[/TR]
</tbody>[/TABLE]
My code so far is as follows.

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

 

[Roderick_E]

Webqueries work best with data formatted as tables. Look at this text only search engine instead of Google and see what results you get: Textise - the Text-Only Internet Tool

 

[dairvine]

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.

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