Excel Web Data - Chicagoland Sportbikes
Chicagoland Sportbikes
 
Geek Squad to the rescue! fucken computer crashing on you again? another porn site infected your computer? tierd of your wife seeing your anal fisting videos and want to lock her out of the external HD? get the answers you need from our local computer experts.

 
LinkBack Thread Tools Display Modes
post #1 of 11 (permalink) Old 09-18-2011, 02:42 PM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
Excel Web Data

I don't know if this is something that is a simple command or not - and I can't seem to find it anywhere.

I have a column of words. I want to search using google each of those words and return the number of search results in the column next to it. I tried using the data web query but it was not recognizing the # of results as a data section.

Any ideas? I want the the web search and the data return to be automatic?


Before anyone says it - I did google this I am just having trouble making it work

Quote:
Public Sub ExcelGoogleSearch()

Dim searchWords As String

With Sheets("Sheet1")
RowCount = 1
Do While .Range("A" & RowCount) <> ""
searchWords = .Range("A" & RowCount).Value

' Get keywords and validate by adding + for spaces between
searchWords = Replace$(searchWords, " ", "+")

' Obtain the source code for the Google-searchterm webpage
search_url = "http://www.google.com/search?hl=en&q=" & searchWords & "&meta="""
Set search_http = CreateObject("MSXML2.XMLHTTP")
search_http.Open "GET", search_url, False
search_http.send
results_var = search_http.responsetext
Set search_http = Nothing

' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "b> of", vbTextCompare)
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<", vbTextCompare)
NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))

Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1
Loop
End With

End Sub

Jordan

Last edited by Loki047; 09-18-2011 at 02:57 PM.
Loki047 is offline  
Sponsored Links
Advertisement
 
post #2 of 11 (permalink) Old 09-18-2011, 08:00 PM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
How about some money for anyone figures this out and comments the code so I can edit it later as necessary.

Jordan
Loki047 is offline  
post #3 of 11 (permalink) Old 09-18-2011, 08:06 PM
Instigator
 
bigbaddiesel's Avatar
 
Join Date: Oct 2005
Location: Chicago, Illinois
Posts: 283
Location: Chicago, Illinois
Sportbike: 2003 Yamaha R6
Years Riding: 8
How you found us: Google!
  
I could do it in perl, ruby, python or bash, and output the results to CSV -- I dont get involved much in microsoft products, so no idea on doing it in excel.

2003 Yamaha R6 Raven Limited Edition

Cages:
1999 Ford F250 (Gas Sucker)
2001 VW Golf TDI (Gas Saver)
bigbaddiesel is offline  
Sponsored Links
Advertisement
 
post #4 of 11 (permalink) Old 09-18-2011, 08:22 PM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
Quote:
Originally Posted by bigbaddiesel View Post
I could do it in perl, ruby, python or bash, and output the results to CSV -- I dont get involved much in microsoft products, so no idea on doing it in excel.
Macros for excel are written in visual basic.

Jordan
Loki047 is offline  
post #5 of 11 (permalink) Old 09-19-2011, 08:24 AM
Registered User
 
Join Date: Jun 2005
Location: Arlington Heights
Posts: 298
Location: Arlington Heights
Sportbike: ZX6R RIP............ 02 VFR RED
Years Riding: Second category rider.
How you found us: Friend
           
Gosha is offline  
post #6 of 11 (permalink) Old 09-19-2011, 10:23 AM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
Quote:
Originally Posted by Gosha View Post
Works for me! Awesome

What is your favorite beer, wine, or favorite restaurant?

Jordan
Loki047 is offline  
post #7 of 11 (permalink) Old 09-19-2011, 10:30 AM
Rex Banner
 
Flying_Hellfish's Avatar
 
Join Date: Aug 2002
Location: Aurora
Posts: 2,561
Location: Aurora
Sportbike: 99 SV650, z50r
Years Riding: A few years
How you found us: A tranny told me
           
Use Google Apps? LOL

Lord, beer me strength

Gamertag: FlyingHellf1sh
Flying_Hellfish is offline  
post #8 of 11 (permalink) Old 09-19-2011, 10:42 AM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
Quote:
Originally Posted by Flying_Hellfish View Post
Use Google Apps? LOL
Care to explain?

Jordan
Loki047 is offline  
post #9 of 11 (permalink) Old 09-19-2011, 10:44 AM
Registered User
 
Join Date: Jun 2005
Location: Arlington Heights
Posts: 298
Location: Arlington Heights
Sportbike: ZX6R RIP............ 02 VFR RED
Years Riding: Second category rider.
How you found us: Friend
           
Quote:
Originally Posted by Loki047 View Post
Works for me! Awesome

What is your favorite beer, wine, or favorite restaurant?
Just glad I could help.

George
Gosha is offline  
post #10 of 11 (permalink) Old 09-19-2011, 10:56 AM
Rex Banner
 
Flying_Hellfish's Avatar
 
Join Date: Aug 2002
Location: Aurora
Posts: 2,561
Location: Aurora
Sportbike: 99 SV650, z50r
Years Riding: A few years
How you found us: A tranny told me
           
Quote:
Originally Posted by Loki047 View Post
Care to explain?
I can't 100% explain......We had a presentation last week with google and apparently if you use their apps package (read replacement for office..... corporate version of Google Docs) you can integrate real time web query data into their spreadsheets, docs, etc. I haven't had a chance to mess with it yet though.

Lord, beer me strength

Gamertag: FlyingHellf1sh
Flying_Hellfish is offline  
post #11 of 11 (permalink) Old 09-19-2011, 11:15 AM Thread Starter
he who is absent foreskin
 
Loki047's Avatar
 
Join Date: Jul 2008
Location: Northshore - Deerfield
Posts: 5,704
Location: Northshore - Deerfield
Sportbike: Z1000
Years Riding: 3 years
How you found us: Search Engine
           
Send a message via AIM to Loki047
Quote:
Originally Posted by Flying_Hellfish View Post
I can't 100% explain......We had a presentation last week with google and apparently if you use their apps package (read replacement for office..... corporate version of Google Docs) you can integrate real time web query data into their spreadsheets, docs, etc. I haven't had a chance to mess with it yet though.
Oh ok cool. Thats another place for me to explore.

The code that Gosha found works, but I have too many queries and looks like google stops responding after 4000

Jordan
Loki047 is offline  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the Chicagoland Sportbikes forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome