I am back at this problem again - have not been able to resolve it yet.
The google adwords approach is probably more comprehensive but I have not been able to setup the account and all that. It seems to be a longer route to solve my problem. I put in all the requests for the accounts etc. but have not got any response. I may have to walk through the instructions all over again and still wait another week to get a response from google. If ultimately that is what I need to do I will do that.
However, I have found something close enough on stackoverflow.com that just needs a small modification. So here is your task :
1) Look at this code and walk me through steps to get this working as is. I am not an expert in Excel or VB but given some instructions can cut and paste some code and make it run if someone can walk me through. Last time I did programming was about 20 years ago.
2) Help me modify the google search portion where instead of searching the whole internet, I am searching in a particular website, which is named in say Column D (D1, D2, D3 etc.)
Let me know what the fee is and lets move on this.
============= Excerpt from stackoverflow.com===============
Public Sub ExcelGoogleSearch()
Dim searchWords As String
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
results_var = search_http.responsetext
Set search_http = Nothing
' Find the number of results and post to sheet
pos_1 = InStr(1, results_var, "resultStats>", vbTextCompare)
If pos_1 = 0 Then
NumberofResults = 0
pos_2 = InStr(3 + pos_1, results_var, ">", vbTextCompare)
pos_3 = InStr(pos_2, results_var, "<nobr>", vbTextCompare)
NumberofResults = Mid(results_var, 1 + pos_2, (-1 + pos_3 - pos_2))
Range("B" & RowCount) = NumberofResults
RowCount = RowCount + 1